Code to add comments to selected cells within a selected range

[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 theSub 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 waitingfor 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 fora 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

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[L. Howard]

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

 

[L. Howard]

Forgot to ask, what is Falsch?

If myCom = "" Or myCom = "Falsch" Then GoTo SKIP

Howard

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Claus Busch]

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=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Comments"


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Tue, 28 Jan 2014 15:16:15 +0100 schrieb Claus Busch:






please have a look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

 

[Claus Busch]

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.

 

[L. Howard]

Hi Howard,



Am Tue, 28 Jan 2014 10:37:13 -0800 (PST) schrieb L. Howard:






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

 

[L. Howard]

a typo;

Upper case f4 on cell address works for relative or absolute but does not access the properties in the vb editor.

 

[L. Howard]

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

 

[L. Howard]

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/vgdajragl...ting My Reference Copy A Drop Box MForum.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

 

[Claus Busch]

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "DeskBookings"


Regards
Claus B.

 

[L. Howard]

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 here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

 

[Claus Busch]

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.

 

[L. Howard]

Hi Howard,



Am Wed, 29 Jan 2014 12:51:59 -0800 (PST) schrieb L. Howard:






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

 

[Claus Busch]

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.