how to expand "selection" to a range instead of "active cell" to show comment/note...

[sadams1]

hello & I hang my head in shame that I can't figure this out. Have the follow code...
*********************************************

Sub cmtShow()

With Selection

.Comment.Visible = True
.Comment.Shape.Select True

End With
Application.CutCopyMode = False


End Sub

************************************************

This works great for the "active cell" but if a range is selected, an error about "block variable not set" comes up. What am I missing using "selection" as it should work for ranges (?) Any insight would be greatly appreciated! Thank you for your time!
Steve

 

[jolivanes]

What if you change "With Selection" to "For Each c In Selection"?

 

[sadams1]

hello & thank you for the reply! I'm a hack at best so I mess around with stuff until I have ask for help so when I replace "with selection" with "for each c in selection" Visual Basic gets all upset & snarls "COMPILE ERROR! INVALID REFERENCE!" so looks like there's addition syntax? Sorry for sounding stupid but my programming skills are suspect on a good day!

 

[jolivanes]

Sorry about that.
What are you trying to accomplish? Just make the Comment visible?
Maybe try so

Sub Make_Visible()
Dim c As Range
For Each c In Selection
c.Comment.Visible = True
Next c
End Sub

Select the Range in question and run the macro. See if that is what you want.
Next time when asking a question, explain what you want to achieve. Quite often code that does not work is of little or no use.
What is this meant to show you?

.Comment.Shape.Select True

 

[sadams1]

Sorry for being unclear about the end result...it's hooked up to a "hotkey" so when a cell, or range, is active, the keyboard shortcut makes the comment visible.

Something I noticed is that the code works with contiguous cells but if a cell is blank anywhere in the range, the "block variable not set" error occurs. The ranges will have blank cells so would it possible to tweak the code to handle empty cells? Thank you very much again for your time & insights!

 

[sadams1]

for comparison, this is a macro that resets the comments to a specific spot & usually an entire row or entire column can be selected...

Sub cmtResetRange()
Dim r As Range
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'


For Each r In Selection
If Not r.Comment Is Nothing Then
With r.Comment

r.Comment.Shape.Top = r.Comment.Parent.Top + 15
r.Comment.Shape.Left = r.Comment.Parent.Offset(0, 1).Left + 5





End With
End If
Next r
End Sub

 

[jolivanes]

Maybe take notice of attached. Makes life so much easier.