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

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

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

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

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

Forgot to ask, what is Falsch?

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

Howard

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