Excel: how to hide comments... "in bulk"
Solved
Sherlock Hoimes
Posted messages
134
Registration date
Status
Membre
Last intervention
-
Sherlock Hoimes Posted messages 134 Registration date Status Membre Last intervention -
Sherlock Hoimes Posted messages 134 Registration date Status Membre Last intervention -
Hello everyone,
I have a little problem that I'm sure I'm not the only one facing.
On my Excel page, throughout the month, I make comments on the cells. At the end of the month, I sort through them and I would like to hide, just hide, most of the comments and keep only a few here and there.
So far, I've done it cell by cell but it's a monumental task... and I'm not Roman ;-))
I found a system online but it either removes all comments from the entire page or shows them all with big lines connecting the comments across the page. (Review, comments, show/hide all comments).
Moreover, I couldn't figure out how to undo the action, which would have allowed me to restore everything properly.
I'm on Windows 10 and not very skilled with computers (big issue with the vocabulary)...
If anyone knows how to hide comments "in bulk", I would greatly appreciate it and thank you in advance.
Configuration: Windows / Firefox 96.0
S.H.
From knowledge comes light, and vice versa.
I have a little problem that I'm sure I'm not the only one facing.
On my Excel page, throughout the month, I make comments on the cells. At the end of the month, I sort through them and I would like to hide, just hide, most of the comments and keep only a few here and there.
So far, I've done it cell by cell but it's a monumental task... and I'm not Roman ;-))
I found a system online but it either removes all comments from the entire page or shows them all with big lines connecting the comments across the page. (Review, comments, show/hide all comments).
Moreover, I couldn't figure out how to undo the action, which would have allowed me to restore everything properly.
I'm on Windows 10 and not very skilled with computers (big issue with the vocabulary)...
If anyone knows how to hide comments "in bulk", I would greatly appreciate it and thank you in advance.
Configuration: Windows / Firefox 96.0
S.H.
From knowledge comes light, and vice versa.
22 réponses
- 1
- 2
Suivant
Re,
VBA stands for Visual Basic for Applications, in simple terms, it is a programming language that allows the use of Visual Basic code to execute the many features of the Excel Application.
In the earlier versions of Excel, this language was written in the local language, nowadays it's in English, which requires some knowledge.
For example, this piece of code entered in the properties of an ActiveX button allows displaying the comments of the selected cells or hiding it if the comment is currently shown.
Option Explicit
Private Sub CommandButton1_Click()
Dim Cel As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each Cel In Selection
If Cel.Comment.Visible Then ' ------------ if the comment is visible
Cel.Comment.Visible = False ' -------- the comment is hidden
Else
Cel.Comment.Visible = True ' --------- if not, show the comment
End If
Next
On Error GoTo 0
End Sub
A+
Mike-31
I am responsible for what I say, not for what you understand...
VBA stands for Visual Basic for Applications, in simple terms, it is a programming language that allows the use of Visual Basic code to execute the many features of the Excel Application.
In the earlier versions of Excel, this language was written in the local language, nowadays it's in English, which requires some knowledge.
For example, this piece of code entered in the properties of an ActiveX button allows displaying the comments of the selected cells or hiding it if the comment is currently shown.
Option Explicit
Private Sub CommandButton1_Click()
Dim Cel As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each Cel In Selection
If Cel.Comment.Visible Then ' ------------ if the comment is visible
Cel.Comment.Visible = False ' -------- the comment is hidden
Else
Cel.Comment.Visible = True ' --------- if not, show the comment
End If
Next
On Error GoTo 0
End Sub
A+
Mike-31
I am responsible for what I say, not for what you understand...
Hello,
You know, Sherlock is a bit more persistent than that. Don't get discouraged so quickly.
Another suggestion without a button.
Right-click on the name of the relevant tab, choose 'View Code', and copy-paste this:
That's it...
Select a range and right-click on it.
You'll be asked: "Show/hide comments?"
- yes: toggle the comment visibility
- no: usual right-click
In the code, I've restricted it to columns "A:C", set the range you are interested in to get the right-click directly on the rest.
Then do 'Save As...' and choose the type *.xlsm to keep the macro.
On the first subsequent opening, you'll need to accept the macros (yellow banner at the top of the sheet)
An example so you can see what it looks like: https://www.cjoint.com/c/LAElSdL48L4
eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
Besides the thank you (yes, it happens!!!), remember to mark it as resolved. Thanks
You know, Sherlock is a bit more persistent than that. Don't get discouraged so quickly.
Another suggestion without a button.
Right-click on the name of the relevant tab, choose 'View Code', and copy-paste this:
Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim pl As Range, c As Range Set pl = Range("A:C") ' restrict to columns A:C Set pl = Intersect(pl, Target) If Not pl Is Nothing Then If MsgBox("Show/hide comments?", vbYesNo, "Comment Visualization") = vbYes Then Cancel = True Application.ScreenUpdating = False For Each c In Target If Not c.Comment Is Nothing Then c.Comment.Visible = Not c.Comment.Visible Next c End If End If End Sub That's it...
Select a range and right-click on it.
You'll be asked: "Show/hide comments?"
- yes: toggle the comment visibility
- no: usual right-click
In the code, I've restricted it to columns "A:C", set the range you are interested in to get the right-click directly on the rest.
Then do 'Save As...' and choose the type *.xlsm to keep the macro.
On the first subsequent opening, you'll need to accept the macros (yellow banner at the top of the sheet)
An example so you can see what it looks like: https://www.cjoint.com/c/LAElSdL48L4
eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
Besides the thank you (yes, it happens!!!), remember to mark it as resolved. Thanks
Good evening,
what you are asking for can only be done with VBA code if you have some knowledge of it. What interests me in your request is the majority of comments and only keeping a few here and there
we can write a code to display comments from a selection of contiguous or non-contiguous cells, which is similar to what you are asking, but it will be necessary to select the relevant cells or define a logical selection rule
--
See you soon
Mike-31
I am responsible for what I say, not for what you understand...
what you are asking for can only be done with VBA code if you have some knowledge of it. What interests me in your request is the majority of comments and only keeping a few here and there
we can write a code to display comments from a selection of contiguous or non-contiguous cells, which is similar to what you are asking, but it will be necessary to select the relevant cells or define a logical selection rule
--
See you soon
Mike-31
I am responsible for what I say, not for what you understand...
Good evening Mike-31
And thank you for your quick response.
Fortunately, you are only responsible for what you say because uhhhh what is a VBA code? What kind of code should be displayed? All this seems quite complicated for my poor head.
What’s strange is that the program's designers didn’t anticipate this kind of manipulation over a range and not just in a single cell. Maybe they'll get around to it!
Thanks again for trying to save me a lot of time. I think I will continue... the old-fashioned way, cell by cell.
Good night.
--
S.H.
From knowledge comes light and vice versa.
And thank you for your quick response.
Fortunately, you are only responsible for what you say because uhhhh what is a VBA code? What kind of code should be displayed? All this seems quite complicated for my poor head.
What’s strange is that the program's designers didn’t anticipate this kind of manipulation over a range and not just in a single cell. Maybe they'll get around to it!
Thanks again for trying to save me a lot of time. I think I will continue... the old-fashioned way, cell by cell.
Good night.
--
S.H.
From knowledge comes light and vice versa.
Hello Mike-31,
Thanks again for your help, but I'm going to stick to the old way while waiting for the function to be available directly because right now, I'm completely in a mess with programming. Sorry!
Thank you very much. Take good care of yourself.
By the way, I love your avatar.
--
S.H.
From knowledge comes light and vice versa.
Thanks again for your help, but I'm going to stick to the old way while waiting for the function to be available directly because right now, I'm completely in a mess with programming. Sorry!
Thank you very much. Take good care of yourself.
By the way, I love your avatar.
--
S.H.
From knowledge comes light and vice versa.
Hello courageous Eriiic,
I'm usually quite stubborn, but right now, I'm really stuck.
I've read and reread your message and I'm no further along. If I have to come off as a b... which is not untrue when it comes to computers, here are a few sticking points.
1 - An "tab" is indeed the thing at the top indicating "File, Home, Insert..." or the line below "Clipboard, Font, Alignment...". In either case, following your instructions, it only offers to "customize the ribbon...". No "View the code" option. Which tab are you talking about?
2 - I've opened your example, which I assume is ready for use.
One strange thing: when I opened your attachment, it’s my name that appears at the top of the document with the name you gave it!!!
a - Select a range and right-click on it. OK
b - You are asked: "Show/hide comments?" OK
c - By selecting "hide" on one of your ranges, only the top cell hides or shows its comment, not the entire selected series.
d - - yes: toggle the display of comments From there, I'm lost.
e - Toggle the display??
f - regular right-click??
g - to directly retrieve the right-click on the rest. ??
I warned you, sorry, I'm not skilled in the field of computers and I totally understand if you get tired of this. Thank you again for your efforts.
-
S.H.
From knowledge comes light and vice versa.
I'm usually quite stubborn, but right now, I'm really stuck.
I've read and reread your message and I'm no further along. If I have to come off as a b... which is not untrue when it comes to computers, here are a few sticking points.
1 - An "tab" is indeed the thing at the top indicating "File, Home, Insert..." or the line below "Clipboard, Font, Alignment...". In either case, following your instructions, it only offers to "customize the ribbon...". No "View the code" option. Which tab are you talking about?
2 - I've opened your example, which I assume is ready for use.
One strange thing: when I opened your attachment, it’s my name that appears at the top of the document with the name you gave it!!!
a - Select a range and right-click on it. OK
b - You are asked: "Show/hide comments?" OK
c - By selecting "hide" on one of your ranges, only the top cell hides or shows its comment, not the entire selected series.
d - - yes: toggle the display of comments From there, I'm lost.
e - Toggle the display??
f - regular right-click??
g - to directly retrieve the right-click on the rest. ??
I warned you, sorry, I'm not skilled in the field of computers and I totally understand if you get tired of this. Thank you again for your efforts.
-
S.H.
From knowledge comes light and vice versa.
The tab is the name of your sheet at the bottom.
At the top is the menu, or the ribbon with its tools as it should now be called.
You can right-click on it, 'Customize the Ribbon...' and check 'Developer' on the right-hand list to have an additional menu dedicated to VBA in the ribbon.
The advantage of right-clicking on the tab is that it takes you to the right place in VBE for the events of your sheet, allowing you to paste the code directly. It's simpler when you're starting out.
One weird thing: when opening your attachment, my name appears at the top of the doc with the name you gave it!!!
Well, that's because your name is Eric too :-)
More seriously, I don't see what you mean. Maybe a 365 thing (?) that I don't have.
c- By selecting "hide" on one of your ranges, only the top cell hides or shows its comment, not the entire selected series.
See functionality below
e- Switch the display ?? of comments...
In your selection, displayed comments will hide, hidden ones will show.
You select 3 comments you no longer want, right-click on the selection, poof, gone.
And at Lycée de Versailles, right-click again and they show up again.
f- usual right-click ??
the usual context menu appears (Cut, Copy, etc.).
You need to be able to choose between comment management and the context menu; otherwise, you wouldn't be able to add or delete comments through the right-click.
g- to directly retrieve the right-click on the rest. ??
With Set pl = Range("A:C"), I limit the option to choose between comment management or the usual context menu to columns A:C. No need to be bothered by a dialog box to validate at every right-click in an area where you don't need it.
I randomly put A:C; it’s up to you to define where you want the macro to act.
It's up to you to specify exactly what you want if you want the syntax to put.
I made this operational choice to make it easier to implement and have fewer questions, but failed ;-)
When you get used to the B.B.BA of VBA, you can turn towards Mike's proposal with a button if you prefer.
Eric
At the top is the menu, or the ribbon with its tools as it should now be called.
You can right-click on it, 'Customize the Ribbon...' and check 'Developer' on the right-hand list to have an additional menu dedicated to VBA in the ribbon.
The advantage of right-clicking on the tab is that it takes you to the right place in VBE for the events of your sheet, allowing you to paste the code directly. It's simpler when you're starting out.
One weird thing: when opening your attachment, my name appears at the top of the doc with the name you gave it!!!
Well, that's because your name is Eric too :-)
More seriously, I don't see what you mean. Maybe a 365 thing (?) that I don't have.
c- By selecting "hide" on one of your ranges, only the top cell hides or shows its comment, not the entire selected series.
See functionality below
e- Switch the display ?? of comments...
In your selection, displayed comments will hide, hidden ones will show.
You select 3 comments you no longer want, right-click on the selection, poof, gone.
And at Lycée de Versailles, right-click again and they show up again.
f- usual right-click ??
the usual context menu appears (Cut, Copy, etc.).
You need to be able to choose between comment management and the context menu; otherwise, you wouldn't be able to add or delete comments through the right-click.
g- to directly retrieve the right-click on the rest. ??
With Set pl = Range("A:C"), I limit the option to choose between comment management or the usual context menu to columns A:C. No need to be bothered by a dialog box to validate at every right-click in an area where you don't need it.
I randomly put A:C; it’s up to you to define where you want the macro to act.
It's up to you to specify exactly what you want if you want the syntax to put.
I made this operational choice to make it easier to implement and have fewer questions, but failed ;-)
When you get used to the B.B.BA of VBA, you can turn towards Mike's proposal with a button if you prefer.
Eric
Hello Eriiic,
I let it settle and I'm picking up this paragraph that was left behind: At the top is the menu, or the ribbon with its tools as it should now be called.
You can right-click on it, 'Customize the Ribbon...' and check 'Developer' in the right-hand list to have an additional menu dedicated to VBA in the ribbon.
The advantage of right-clicking on the tab is that it takes you to the right place in VBE for your sheet's events, allowing you to paste the code directly. Easier when you are starting out.
VBA and VBE: is it a typo or is one (VBE) the container for the other?
"Easier when you are starting out" yes but especially for accessing and adjusting the settings.
For the rest, "Eureka!" I went over the course on a blank test document and it works correctly. I understood the why and almost the how. It's not perfect yet but I will be able to use and reuse your course! It was a bit tedious but I am worthy of my username. Thank you for pushing through and providing good advice.
I let it settle and I'm picking up this paragraph that was left behind: At the top is the menu, or the ribbon with its tools as it should now be called.
You can right-click on it, 'Customize the Ribbon...' and check 'Developer' in the right-hand list to have an additional menu dedicated to VBA in the ribbon.
The advantage of right-clicking on the tab is that it takes you to the right place in VBE for your sheet's events, allowing you to paste the code directly. Easier when you are starting out.
VBA and VBE: is it a typo or is one (VBE) the container for the other?
"Easier when you are starting out" yes but especially for accessing and adjusting the settings.
For the rest, "Eureka!" I went over the course on a blank test document and it works correctly. I understood the why and almost the how. It's not perfect yet but I will be able to use and reuse your course! It was a bit tedious but I am worthy of my username. Thank you for pushing through and providing good advice.
Hello Sherlock,
VBA is the language: Visual Basic for Applications
VBE is the editor: Visual Basic Editor, which you can also open with Alt+F11
Well done, the first steps are not instinctive.
Then it takes a little time to start understanding the lines of code and to be less afraid of this 'Chinese' :-)
eric
VBA is the language: Visual Basic for Applications
VBE is the editor: Visual Basic Editor, which you can also open with Alt+F11
Well done, the first steps are not instinctive.
Then it takes a little time to start understanding the lines of code and to be less afraid of this 'Chinese' :-)
eric
Good evening Eriiic,
Indeed, it's not instinctive at all, especially when I "broke the computer" at work during my early days in IT. Since then, it’s true that I’m a bit afraid of messing everything up because I wouldn’t be able to fix it. A handicap these days.
But well, a good stick, a good guide, and off we go across the street.
Thanks again and have a nice evening.
Indeed, it's not instinctive at all, especially when I "broke the computer" at work during my early days in IT. Since then, it’s true that I’m a bit afraid of messing everything up because I wouldn’t be able to fix it. A handicap these days.
But well, a good stick, a good guide, and off we go across the street.
Thanks again and have a nice evening.
Good evening Eriiic,
The tab is OK. Just a matter of terms! I took a look at “visualize…” as indicated at 12:45. Re-OK but it’s all Greek to me so I accept.
As for the ribbon, we’ll see later when I’ve grasped everything and I’ll go back to Mike’s messages.
However, it’s still stalling after selecting 3 cells because only the 1st one makes a splash. Same for the Versailles high school. Only 1 cell (the 1st) out of the bunch changes, just like with the context menu (Another expression I’ve just understood the meaning of). I’ll die less stupid.
Is it possible that “You select 3 comments that you no longer want” refers to something other than the cells with the red mark? Because I haven’t been able to select multiple comment bubbles at the same time.
g- although your language is current ????, I don’t understand what to do (or not) nor how. Dialog box = context menu?
I’ve understood that you’ve only made a sample but I can’t do what I want with it (see 3rd paragraph). When I’m up to speed, I’ll apply the system to the whole page so there won’t be any more thinking needed. I want: I click - I don’t want anymore: I click again.
The light is beginning to come in. Thank you for your support.
--
S.H.
From knowledge comes light and vice versa.
The tab is OK. Just a matter of terms! I took a look at “visualize…” as indicated at 12:45. Re-OK but it’s all Greek to me so I accept.
As for the ribbon, we’ll see later when I’ve grasped everything and I’ll go back to Mike’s messages.
However, it’s still stalling after selecting 3 cells because only the 1st one makes a splash. Same for the Versailles high school. Only 1 cell (the 1st) out of the bunch changes, just like with the context menu (Another expression I’ve just understood the meaning of). I’ll die less stupid.
Is it possible that “You select 3 comments that you no longer want” refers to something other than the cells with the red mark? Because I haven’t been able to select multiple comment bubbles at the same time.
g- although your language is current ????, I don’t understand what to do (or not) nor how. Dialog box = context menu?
I’ve understood that you’ve only made a sample but I can’t do what I want with it (see 3rd paragraph). When I’m up to speed, I’ll apply the system to the whole page so there won’t be any more thinking needed. I want: I click - I don’t want anymore: I click again.
The light is beginning to come in. Thank you for your support.
--
S.H.
From knowledge comes light and vice versa.
Does "You select 3 comments you no longer want" happen to refer to anything other than the cells with the red marker?
Yes, absolutely, those are the cells that need to be selected.
However, it still gets stuck after selecting 3 cells because only the 1st one makes a splash. Same for the Lycée de Versailles. Only 1 cell (the 1st one) in the group changes like with the context menu.
That's less normal.
For all selected cells, the displayed comments should hide, and the hidden ones should display.
Stay on the file I provided to simplify.
Did you accept the macros?
If you right-click on A2, you should get a dialog box asking you "Show/hide comments?" with 2 Yes/No buttons to respond.
Respond No, nothing changes.
Select A2:A3, right-click, Yes, the 2 comments should no longer display.
We'll see about your point g (uhhh...) later.
Yes, absolutely, those are the cells that need to be selected.
However, it still gets stuck after selecting 3 cells because only the 1st one makes a splash. Same for the Lycée de Versailles. Only 1 cell (the 1st one) in the group changes like with the context menu.
That's less normal.
For all selected cells, the displayed comments should hide, and the hidden ones should display.
Stay on the file I provided to simplify.
Did you accept the macros?
If you right-click on A2, you should get a dialog box asking you "Show/hide comments?" with 2 Yes/No buttons to respond.
Respond No, nothing changes.
Select A2:A3, right-click, Yes, the 2 comments should no longer display.
We'll see about your point g (uhhh...) later.
Re,
Hi Eriiic,
Without a minimum knowledge of VBA it won't be easy; I'm posting a file with several options to test and see.
https://www.cjoint.com/c/LAEsVeSi2tF
--
See you +
Mike-31
I am responsible for what I say, not for what you understand...
Hi Eriiic,
Without a minimum knowledge of VBA it won't be easy; I'm posting a file with several options to test and see.
https://www.cjoint.com/c/LAEsVeSi2tF
--
See you +
Mike-31
I am responsible for what I say, not for what you understand...
Mike,
I just saw that you followed our wanderings throughout the day and I thank you for that.
It seems that I found the electric button. Let's hope I don't get shocked or pull the cat's tail too hard!
For now, I will walk with Eriiic's system and when I gain confidence, I will try your VBA thing.
Have a good evening and thanks again for your research.
I just saw that you followed our wanderings throughout the day and I thank you for that.
It seems that I found the electric button. Let's hope I don't get shocked or pull the cat's tail too hard!
For now, I will walk with Eriiic's system and when I gain confidence, I will try your VBA thing.
Have a good evening and thanks again for your research.
I only "work" on your doc to make sure it works.
I think that to accept macros, you need to click on the yellow banner that appears when opening the doc. If yes: OK from the beginning.
When I hover over the cells, the comments appear or disappear accordingly.
I don't understand this part, but... IT W.O.R.K.S!!!! The yes/no dialog box opens every time and you just need to click yes for there to be a change. On no, indeed we go back to the old model.
I tried copying and pasting a cell and... IT W.O.R.K.S again! So on my real doc, I will do a copy-paste on all the relevant areas and off I go. Previously, I will need to "format" the page through the bottom tab. Did I understand everything correctly?
You really deserve it for your patience. THANK YOU!
--
S.H.
From knowledge comes light and vice versa.
I think that to accept macros, you need to click on the yellow banner that appears when opening the doc. If yes: OK from the beginning.
When I hover over the cells, the comments appear or disappear accordingly.
I don't understand this part, but... IT W.O.R.K.S!!!! The yes/no dialog box opens every time and you just need to click yes for there to be a change. On no, indeed we go back to the old model.
I tried copying and pasting a cell and... IT W.O.R.K.S again! So on my real doc, I will do a copy-paste on all the relevant areas and off I go. Previously, I will need to "format" the page through the bottom tab. Did I understand everything correctly?
You really deserve it for your patience. THANK YOU!
--
S.H.
From knowledge comes light and vice versa.
In your actual doc, you need to add the code in the VBA module of your sheet as explained in post 5
g- although your language is current ????, I don't understand what needs to be done (or not) or how. Dialog box = context menu?
Now that it works, in my doc, right-click on D8.
You see that nothing is requested, no validation box to access the context menu.
You are outside of the A:C range, the macro stops as soon as it detects that.
If your choice comments are for a specific area, you might as well limit it to that.
You won't be bothered by unnecessary validation if in other columns you have copying-pasting or formatting to do by right-clicking.
This is what's done in
It's up to you to put in the reality. It can be columns, a single one, or a union of non-contiguous columns, one or more rectangular ranges, etc.
eric
--
By continuously trying, one eventually succeeds.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes yes, it happens!!!), remember to mark it as resolved. Thank you.
g- although your language is current ????, I don't understand what needs to be done (or not) or how. Dialog box = context menu?
Now that it works, in my doc, right-click on D8.
You see that nothing is requested, no validation box to access the context menu.
You are outside of the A:C range, the macro stops as soon as it detects that.
If your choice comments are for a specific area, you might as well limit it to that.
You won't be bothered by unnecessary validation if in other columns you have copying-pasting or formatting to do by right-clicking.
This is what's done in
Set pl = Range("A:C") ' restrict to columns A:C It's up to you to put in the reality. It can be columns, a single one, or a union of non-contiguous columns, one or more rectangular ranges, etc.
eric
--
By continuously trying, one eventually succeeds.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes yes, it happens!!!), remember to mark it as resolved. Thank you.
Good evening, Sirs,
I believe the light is well lit, thanks to you.
As with every discovery I make, I write a note for which I would be pleased if you could correct me if it is not very accurate.
https://www.cjoint.com/c/LAEwbAqFrOJ
You will also be able to check the quality of your teaching.
Thank you again and good night to you both.
--
S.H.
From knowledge comes light and vice versa.
I believe the light is well lit, thanks to you.
As with every discovery I make, I write a note for which I would be pleased if you could correct me if it is not very accurate.
https://www.cjoint.com/c/LAEwbAqFrOJ
You will also be able to check the quality of your teaching.
Thank you again and good night to you both.
--
S.H.
From knowledge comes light and vice versa.
It seems correct to me.
You forgot 'Save as...' with the type *.xlsm
Not sure you'll remember it in 6 months.
eric
PS: If you have it, you should look into OneNote for note-taking.
I don't think it's done. It's divided into notebooks, sections, pages.
This allows for good subdivision and grouping by theme.
By trying continuously, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to saying thank you (yes, it can be done!!!), remember to mark it as resolved. Thank you.
You forgot 'Save as...' with the type *.xlsm
Not sure you'll remember it in 6 months.
eric
PS: If you have it, you should look into OneNote for note-taking.
I don't think it's done. It's divided into notebooks, sections, pages.
This allows for good subdivision and grouping by theme.
By trying continuously, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to saying thank you (yes, it can be done!!!), remember to mark it as resolved. Thank you.
I will correct it immediately if you think the rest will be understandable, because in 6 months, indeed, I will probably have forgotten everything.
I really like your humor and your way of explaining things. Keep it up! And take good care of yourself during these somewhat turbulent times. Thank you for your patience.
--
S.H.
From knowledge comes light, and vice versa.
I really like your humor and your way of explaining things. Keep it up! And take good care of yourself during these somewhat turbulent times. Thank you for your patience.
--
S.H.
From knowledge comes light, and vice versa.
Hello S.H.
Now that your problem is solved thanks to Mike and Eric, I'd like to revisit your initial issue.
So I would suggest limiting the number of your comments, keeping them mostly hidden, and possibly adding columns reserved for comments, which will make it easier to hide or delete them when the time comes.
--
Retirement is great! Especially in the Caribbean...
Raymond (INSA, AFPA)
Now that your problem is solved thanks to Mike and Eric, I'd like to revisit your initial issue.
Little problem that I'm certainly not the only one experiencing.
On my Excel page, throughout the month, I leave comments on the cells. At the end of the month, I sort them and I want to hide, just hide, most of the comments and keep only a few.
- You are one of the very few people to encounter this problem, as we don’t usually work this way.
- When we insert comments, 9 times out of 10 they are hidden by default; and the more there are on a page, the more it becomes odd, unreadable, and contrary to the spirit of Excel to have them permanently displayed.
So I would suggest limiting the number of your comments, keeping them mostly hidden, and possibly adding columns reserved for comments, which will make it easier to hide or delete them when the time comes.
--
Retirement is great! Especially in the Caribbean...
Raymond (INSA, AFPA)
Good evening Raymond,
We've already been in contact, both of us. The Caribbean!!! :-)
I use this method of working to keep track of my son's accounts, as he seems to have holes in his pockets, so I add comments to more easily determine where he's spending his money. I only use it for about a month, and after that, I only keep visible the comments on specific purchases. He makes a lot of transactions in a month, and hiding the comments one by one is very tedious.
That's a little clarification.
Enjoy your stay in the sun. Thank you for your assistance.
--
S.H.
From knowledge comes light, and vice versa.
We've already been in contact, both of us. The Caribbean!!! :-)
I use this method of working to keep track of my son's accounts, as he seems to have holes in his pockets, so I add comments to more easily determine where he's spending his money. I only use it for about a month, and after that, I only keep visible the comments on specific purchases. He makes a lot of transactions in a month, and hiding the comments one by one is very tedious.
That's a little clarification.
Enjoy your stay in the sun. Thank you for your assistance.
--
S.H.
From knowledge comes light, and vice versa.
Yes, I understand better the why of these comments.
But for the how I still think it would be much better to add a column:
After Date, Amount, Subject, Beneficiary ... you should plan a column Comment, which you can manage as you wish (hide, display, expand, sort, filter); and you can even insert a comment in a cell of the Comment column!
--
Retirement is great! Especially in the West Indies...
Raymond (INSA, AFPA)
But for the how I still think it would be much better to add a column:
After Date, Amount, Subject, Beneficiary ... you should plan a column Comment, which you can manage as you wish (hide, display, expand, sort, filter); and you can even insert a comment in a cell of the Comment column!
--
Retirement is great! Especially in the West Indies...
Raymond (INSA, AFPA)
Hello Raymond,
Not bad! I admit I hadn't thought of that solution.
However, it has its limits in the sense that it adds 2 additional columns (1 for the - and 1 for the +) every month, and for each "old" month there are already 3 (-, +, variation where I can see when it goes overdrawn).
But why not! Something to think deeply about, my dear Watson!
Have a good week, all in caution.
--
S.H.
From knowledge comes light and vice versa.
Not bad! I admit I hadn't thought of that solution.
However, it has its limits in the sense that it adds 2 additional columns (1 for the - and 1 for the +) every month, and for each "old" month there are already 3 (-, +, variation where I can see when it goes overdrawn).
But why not! Something to think deeply about, my dear Watson!
Have a good week, all in caution.
--
S.H.
From knowledge comes light and vice versa.
Re,
if I'm following your reasoning correctly, I think you can simplify it a lot using conditional formatting. For example, in the Debit column, apply a conditional formatting rule to turn red if the amounts exceed an allowed value; in this case, I've set it to €100, and if the balance is negative.
You can also add conditional formatting in the Balance column if the remaining amount becomes critical, for example, yellow from €300, orange from €100, and the value turns red from zero.
Test up to row 20 to see.
https://www.cjoint.com/c/LAFk0xavAqF
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
if I'm following your reasoning correctly, I think you can simplify it a lot using conditional formatting. For example, in the Debit column, apply a conditional formatting rule to turn red if the amounts exceed an allowed value; in this case, I've set it to €100, and if the balance is negative.
You can also add conditional formatting in the Balance column if the remaining amount becomes critical, for example, yellow from €300, orange from €100, and the value turns red from zero.
Test up to row 20 to see.
https://www.cjoint.com/c/LAFk0xavAqF
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
Hello Mike,
Did you have a good night? It's nice of you to take over.
You'll find a sample of my calculation page in the document below to respond to your proposals.
https://cjoint.com/c/LAFlMrLNHJJ
This is the short version, so it's relatively easy to sort, but the complete one is a different story.
Have a good week. See you.
Did you have a good night? It's nice of you to take over.
You'll find a sample of my calculation page in the document below to respond to your proposals.
https://cjoint.com/c/LAFlMrLNHJJ
This is the short version, so it's relatively easy to sort, but the complete one is a different story.
Have a good week. See you.
Re,
Look at the file below which works with basic formulas and easily adaptable conditional formatting on a file
https://cjoint.com/c/LAFuJPEHWkF
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
Look at the file below which works with basic formulas and easily adaptable conditional formatting on a file
https://cjoint.com/c/LAFuJPEHWkF
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
Hello,
I have thoroughly reviewed your sample.
My tables already function with color coding based on the terms used. Indeed, very practical and easily accessible. Thank you anyway for your suggestion which may be useful to others.
Your proposal is very good except that... below the daily accounts, I also manage placements..., on the same sheet (1 sheet per holder and there are 3 of us) so I don't have much vertical space and over time this column will be verrrrry long.
To be honest, I might be able to move the placements to the side?!? Again, these are also large tables. This needs to be considered because the latest information would be at the top of the page: a definite advantage.
I think we have covered the topic now unless you (you all) have an additional idea. I want to thank the various contributors for their efforts and help.
Last question: how do we mark as resolved, I couldn't find the section.
Best of luck to everyone, and take good care of yourselves.
--
S.H.
From knowledge comes light and vice versa.
I have thoroughly reviewed your sample.
My tables already function with color coding based on the terms used. Indeed, very practical and easily accessible. Thank you anyway for your suggestion which may be useful to others.
Your proposal is very good except that... below the daily accounts, I also manage placements..., on the same sheet (1 sheet per holder and there are 3 of us) so I don't have much vertical space and over time this column will be verrrrry long.
To be honest, I might be able to move the placements to the side?!? Again, these are also large tables. This needs to be considered because the latest information would be at the top of the page: a definite advantage.
I think we have covered the topic now unless you (you all) have an additional idea. I want to thank the various contributors for their efforts and help.
Last question: how do we mark as resolved, I couldn't find the section.
Best of luck to everyone, and take good care of yourselves.
--
S.H.
From knowledge comes light and vice versa.
- 1
- 2
Suivant