Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
So if I select a number of cells in a row, maybe even two rows or a few cells in a few rows, I will have a "selected range". While selected I run the Sub MySelectedRange() and if vbyes from the message box then I select any number of cells in the "selected range" (and accidentally select a cell outside that range) click OK on the InputBox
Now do the stuff I have commented out. AND it would EXCLUDE that accidental selection I made. I can get the comments where I want them to an extent, but cannot exclude the accidental selection or make the comments go back to Hide, except manually. I want to preserve the ability to add text to the comments and then when I go to the next cell to add text to it the previous goes to hide. Unless I am wrong, you cannot add text any of the comments/cells in the middle of the code running, so I am stumped on how to access the cells waiting for a comment. Maybe will have to take the cells one at a time with some different code...? Thanks, Howard Option Explicit Sub MySelectedRange() Dim myCheck myCheck = MsgBox("Do you want add comments?", vbYesNo) If myCheck = vbNo Then Exit Sub Else Set rRange = Application.InputBox("With the ctrl key held down, use your mouse" _ & vbCr & "to click on the cells you want to add a Comment.", _ "Comment This Hour", , , , , , 8) ' Put the code below here to act on each of the cells selected for a comment ' and somehow revert the comments back to Hide (for mouse-over viewing) when it goes to next cell. ' The code below as is works great but leaves the comment Visible. End If End Sub Sub CommentAddOrEdit() 'method suggested by Jon Peltier 2006-03-04 'adds new plain text comment or adds text 'at end of existing comment text Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:="" End If 'type to add comment text to selected shape cmt.Visible = True cmt.Shape.Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 00:02:16 -0800 (PST) schrieb L. Howard: So if I select a number of cells in a row, maybe even two rows or a few cells in a few rows, I will have a "selected range". While selected I run the Sub MySelectedRange() and if vbyes from the message box then I select any number of cells in the "selected range" (and accidentally select a cell outside that range) click OK on the InputBox Now do the stuff I have commented out. AND it would EXCLUDE that accidental selection I made. don't select a range before the InputBox. First run the macro and then select a range with the InputBox: Sub MySelectedRange() Dim myCheck As Integer, myCell As Integer Dim rngC As Range, rRange As Range Dim myCom As String myCheck = MsgBox("Do you want add comments?", vbYesNo) If myCheck = vbYes Then _ Set rRange = Application.InputBox("With the ctrl key held down, " _ & "use your mouse" & vbCr & _ "to click on the cells you want to add a Comment.", _ "Comment This Hour", Type:=8) For Each rngC In rRange If rngC.Comment Is Nothing Then myCell = MsgBox("Do you want to add a comment to " _ & rngC.Address(0, 0), vbYesNo) If myCell = vbYes Then myCom = Application.InputBox("Enter your comment text", _ "My comment", Type:=2) Else myCom = vbNullString End If If myCom = vbNullString Then GoTo SKIP rngC.AddComment myCom End If SKIP: Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 09:50:39 +0100 schrieb Claus Busch: don't select a range before the InputBox. First run the macro and then select a range with the InputBox: or try: Sub MySelectedRange() Dim myCheck As Integer, myCell As Integer Dim rngC As Range, rRange As Range Dim myCom As String myCheck = MsgBox("Do you want add comments?", vbYesNo) If myCheck = vbYes Then _ Set rRange = Application.InputBox("With the ctrl key held down, " _ & "use your mouse" & vbCr & _ "to click on the cells you want to add a Comment.", _ "Comment This Hour", Type:=8) For Each rngC In rRange If rngC.Comment Is Nothing Then myCom = Application.InputBox("Enter your comment text for " _ & rngC.Address(0, 0), "My comment", Type:=2) If myCom = "" Or myCom = "False" Then GoTo SKIP rngC.AddComment myCom End If SKIP: Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi again,
sorry, the IF statement is not ok Try: Sub MySelectedRange() Dim myCheck As Integer, myCell As Integer Dim rngC As Range, rRange As Range Dim myCom As String myCheck = MsgBox("Do you want add comments?", vbYesNo) If myCheck = vbYes Then Set rRange = Application.InputBox("With the ctrl key held down, " _ & "use your mouse" & vbCr & _ "to click on the cells you want to add a Comment.", _ "Comment This Hour", Type:=8) For Each rngC In rRange If rngC.Comment Is Nothing Then myCom = Application.InputBox("Enter your comment text for " _ & rngC.Address(0, 0), "My comment", Type:=2) If myCom = "" Or myCom = "Falsch" Then GoTo SKIP rngC.AddComment myCom End If SKIP: Next End If End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Tuesday, January 28, 2014 1:40:07 AM UTC-8, Claus Busch wrote:
Hi again, sorry, the IF statement is not ok Try: Sub MySelectedRange() myCheck = MsgBox("Do you want add comments?", vbYesNo) If myCheck = vbYes Then Set rRange = Application.InputBox("With the ctrl key held down, " _ & "use your mouse" & vbCr & _ "to click on the cells you want to add a Comment.", _ "Comment This Hour", Type:=8) For Each rngC In rRange If rngC.Comment Is Nothing Then myCom = Application.InputBox("Enter your comment text for " _ & rngC.Address(0, 0), "My comment", Type:=2) If myCom = "" Or myCom = "Falsch" Then GoTo SKIP rngC.AddComment myCom End If SKIP: Next End If End Sub Regards Claus B. Hi Claus, Now that is a work of art! I figured out how to add a standard label or heading if one wants to. rngC.AddComment """Booger: """ & myCom I will also see if I can declare a variable and give it a value from a cell reference on the sheet (Drop Down) to let the user choose from a variety of headings. That doesn't look like much of a task. Thanks Claus for another classic (to me) piece of code. Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Forgot to ask, what is Falsch?
If myCom = "" Or myCom = "Falsch" Then GoTo SKIP Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 05:43:32 -0800 (PST) schrieb L. Howard: If myCom = "" Or myCom = "Falsch" Then GoTo SKIP sorry, in your english excel version you have to set it to: If myCom = "" Or myCom = "False" Then GoTo SKIP Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 05:24:04 -0800 (PST) schrieb L. Howard: I will also see if I can declare a variable and give it a value from a cell reference on the sheet (Drop Down) to let the user choose from a variety of headings. That doesn't look like much of a task. while running the code a data validation is not working. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 15:16:15 +0100 schrieb Claus Busch: while running the code a data validation is not working. please have a look: https://skydrive.live.com/#cid=9378A...121822A3%21326 for the workbook "Comments" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Tuesday, January 28, 2014 6:47:51 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Tue, 28 Jan 2014 15:16:15 +0100 schrieb Claus Busch: while running the code a data validation is not working. please have a look: https://skydrive.live.com/#cid=9378A...121822A3%21326 for the workbook "Comments" Regards Claus B. -- Pretty darned slick! I suspect this is a fairly basic UserForm setup, with a List Box, Text Box, and a Command Button on it. I have used them very little, in fact I don't even know how to change the Test 1, Test 2 etc. to something more meaningful. Got some study time ahead of me, I'm pretty sure it is all well documented. Thanks a lot. Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Tue, 28 Jan 2014 10:37:13 -0800 (PST) schrieb L. Howard: I have used them very little, in fact I don't even know how to change the Test 1, Test 2 etc. to something more meaningful. the rowSource for the ListBox is L1:L5 in sheet1. You can change the values in that range and can change the RowSource into the properties of the ListBox. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Tuesday, January 28, 2014 10:43:06 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Tue, 28 Jan 2014 10:37:13 -0800 (PST) schrieb L. Howard: I have used them very little, in fact I don't even know how to change the Test 1, Test 2 etc. to something more meaningful. the rowSource for the ListBox is L1:L5 in sheet1. You can change the values in that range and can change the RowSource into the properties of the ListBox. Regards Claus B. To access the property window of the userform or any of the controls you put on the userform don't you just Right Click that item. I also see f4 should access the property windows. None of that happens for me. I am using a HP Pavilion g series lap top and to use the f-numbers there is a key (w/ Microsoft Logo) that acts like the shift key does, to access the 'upper case' f functions. Upper case f4 on cell address make works for relative or absolute but does not access the properties in the vb editor. Any suggestions? Howard |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
a typo;
Upper case f4 on cell address works for relative or absolute but does not access the properties in the vb editor. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Tuesday, January 28, 2014 1:03:26 PM UTC-8, L. Howard wrote:
a typo; Upper case f4 on cell address works for relative or absolute but does not access the properties in the vb editor. Problem solved. The property window was so vastly displace downward it was invisible except for a tiny tiny bit of the top. Was able to just barely snag the top and pull it up into full view. Howard |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Claus,
Time permitting could you look at this workbook. All seems to work well with the exception of a runtime error on canceling one of the pop message boxes. https://www.dropbox.com/s/vgdajraglg...%20MForum.xlsm Select a range on any desk row and click the Charlie button. Range is colored and name Charlie is centered on selection and pop up box "Do you want to add comments?" Yes/No. Click Yes. "Comment this hour" pop up shows. Click Cancel or the red X and this produces a run time error. Debug takes you to the line "Set rRange..." I tried a line just above that to try to "GoTo SKIP" and "If myCheck = vbCancel Then Exit Sub" but neither work, still errors. Howard |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Wed, 29 Jan 2014 11:28:26 -0800 (PST) schrieb L. Howard: Range is colored and name Charlie is centered on selection and pop up box "Do you want to add comments?" Yes/No. Click Yes. "Comment this hour" pop up shows. Click Cancel or the red X and this produces a run time error. Debug takes you to the line "Set rRange..." if someone don't want to enter a comment they should press No instead of Yes. Now you have to workaround with a string instead of a range to avoid the error message. You can use the mouse to select the range but the InputBox returns a string. If the string = "" or False you can exit sub. If the string is ok you can set a range with this string. Look he https://skydrive.live.com/#cid=9378A...121822A3%21326 for "DeskBookings" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
if someone don't want to enter a comment they should press No instead of Yes. Now you have to workaround with a string instead of a range to avoid the error message. You can use the mouse to select the range but the InputBox returns a string. If the string = "" or False you can exit sub. If the string is ok you can set a range with this string. Look he https://skydrive.live.com/#cid=9378A...121822A3%21326 for "DeskBookings" Regards Claus B. -- Okay, with that working very well, I will study it some more and try for a full grasp of the concept. Looks pretty straight forward but is a bit more sneaky than I thought. Thanks, Howard |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Wed, 29 Jan 2014 12:51:59 -0800 (PST) schrieb L. Howard: Okay, with that working very well, I will study it some more and try for a full grasp of the concept. it is not working with more than one cell selected. Tomorrow I look for a better solution. I never thought that anyone press Yes if he don't want to enter a comment and then cancel the inputbox :-( Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Wednesday, January 29, 2014 1:08:21 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 29 Jan 2014 12:51:59 -0800 (PST) schrieb L. Howard: Okay, with that working very well, I will study it some more and try for a full grasp of the concept. it is not working with more than one cell selected. Tomorrow I look for a better solution. I never thought that anyone press Yes if he don't want to enter a comment and then cancel the inputbox :-( Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 It would have never occurred to me if I hadn't done it while testing. Did not do it to see or check anything in particular, I just made a mistake in what I was intending to do so just hit that cancel. Up jumped the devil so to speak. Thanks. Howard |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Wed, 29 Jan 2014 13:18:35 -0800 (PST) schrieb L. Howard: It would have never occurred to me if I hadn't done it while testing. Did not do it to see or check anything in particular, I just made a mistake in what I was intending to do so just hit that cancel. Up jumped the devil so to speak. if you do it with a range you always get an error if you cancel the inputbox. The only way is working with a string but this string must be written. It is not possible to do it by selecting with the mouse. Have another look in SkyDrive. I changed the code and the prompt for the inputbox. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Wednesday, January 29, 2014 1:52:47 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 29 Jan 2014 13:18:35 -0800 (PST) schrieb L. Howard: It would have never occurred to me if I hadn't done it while testing. Did not do it to see or check anything in particular, I just made a mistake in what I was intending to do so just hit that cancel. Up jumped the devil so to speak. if you do it with a range you always get an error if you cancel the inputbox. The only way is working with a string but this string must be written. It is not possible to do it by selecting with the mouse. Have another look in SkyDrive. I changed the code and the prompt for the inputbox. Regards Claus B. -- Looking good, either way is fine for me, perhaps the mouse is quicker and easier for me, and I know now to avoid the error. I suppose some folks prefer the keyboard as they are immensely adept with the keys. Thanks. Howard |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Wed, 29 Jan 2014 19:44:51 -0800 (PST) schrieb L. Howard: Looking good, either way is fine for me, perhaps the mouse is quicker and easier for me, and I know now to avoid the error. please look again in SkyDrive. It is now solved with another UserForm with a RefEdit. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Thursday, January 30, 2014 12:41:26 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 29 Jan 2014 19:44:51 -0800 (PST) schrieb L. Howard: Looking good, either way is fine for me, perhaps the mouse is quicker and easier for me, and I know now to avoid the error. please look again in SkyDrive. It is now solved with another UserForm with a RefEdit. Regards Claus B. -- Got it, that seems the most user friendly of all. Thanks again. Howard |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Thursday, January 30, 2014 12:41:26 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 29 Jan 2014 19:44:51 -0800 (PST) schrieb L. Howard: please look again in SkyDrive. It is now solved with another UserForm with a RefEdit. Regards Claus B. -- Hi Claus, my computer locked up and Excel crashed while toying with the last version of Deskbooking you posted on SkyDrive. I lost the version I had downloaded. The one with another Userform and a RefEdit. I looked back into SkyDrive and the link to that workbook is gone. Could you repost it please. Thank, Howard |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Thu, 30 Jan 2014 12:05:17 -0800 (PST) schrieb L. Howard: Could you repost it please. done! I also changed the upper part of the code in module2 (Charlie) because the centeracrossselection not always worked correctly Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Thursday, January 30, 2014 12:17:26 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Thu, 30 Jan 2014 12:05:17 -0800 (PST) schrieb L. Howard: Could you repost it please. done! I also changed the upper part of the code in module2 (Charlie) because the centeracrossselection not always worked correctly Regards Claus B. -- I noticed the centering thing, sorta inconsistent and annoying. Thanks. Howard |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Thu, 30 Jan 2014 13:38:40 -0800 (PST) schrieb L. Howard: I noticed the centering thing, sorta inconsistent and annoying. if the behaviour is not better now try it with merged cells and unmerge them with "Reform" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
On Thursday, January 30, 2014 1:50:46 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Thu, 30 Jan 2014 13:38:40 -0800 (PST) schrieb L. Howard: I noticed the centering thing, sorta inconsistent and annoying. if the behaviour is not better now try it with merged cells and unmerge them with "Reform" Regards Claus B. -- Okay, it does seem to still crop up for no reason I can detect. So you're saying instead of center across selection do merged cells instead and then unmerge them with the reform code. I will give that a go. Never thought I'd use merged cells in conjunction with code, but will do so on your advice.<bg Howard |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Thu, 30 Jan 2014 17:58:23 -0800 (PST) schrieb L. Howard: So you're saying instead of center across selection do merged cells instead and then unmerge them with the reform code. I avoid merged cells in tables because they are bringing problems with sorting and filtering. But in this case it is more a graphic than a table and I would try it. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
Hi Howard,
Am Thu, 30 Jan 2014 17:58:23 -0800 (PST) schrieb L. Howard: I will give that a go. please look again in SkyDrive. The problem now is solved with merged cells. With "Delete" the cell will be unmerged. After merging the selection the problem is, that you have only 1 cell so you can't choose more cells in the selection for adding comments :-( I also merged Y1 with Z1 and AA1 that the cell is wide enough to show the data validation entries without changing the column width and display flickering. I deleted the Worksheet_SelectionChange event macro. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to add comments to selected cells within a selected range
please look again in SkyDrive. The problem now is solved with merged cells. With "Delete" the cell will be unmerged. After merging the selection the problem is, that you have only 1 cell so you can't choose more cells in the selection for adding comments :-( I also merged Y1 with Z1 and AA1 that the cell is wide enough to show the data validation entries without changing the column width and display flickering. I deleted the Worksheet_SelectionChange event macro. Regards Claus B. -- That actually works quite well. One comment is probably enough. I made some modifications to the cell/hours selection process, if only one or two cells selected then just the first initial of the selector is centered in the selection. If three or more, then the whole name. Putting some finishing touches on the userforms to make a bit more presentable, then should be done. Thanks for all the great guidance. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Worksheet Functions | |||
Macro to take selected cells times a selected cell | Excel Programming | |||
Code to color selected ws cells | Excel Programming |