simple range selection returning "run time error 91" randomly...

[sadams1]

hello & many thanks for taking time to see if I'm crazy or not...attached are 2 screenshots...one of the code, the other of the error...& all this is is a way to select a range & resize the comment box. It works 100% when only 1 cell is selected. It works randomly wiith more than one cell selected. It will occasionally work with an entire column selected. It must have to do with the cell formatting so I've messed with that & unable to get anything to work consistently. Thank you again for any insights.

 

[rollis13]

That happens when you have no comment in the selected range. Add this line of code to your macro and add this UDF, like this:

Option Explicit
Sub cmtHideMakeSmall()
    '
    ' MacroHIDE Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
    Dim r     As Range
    If Not bHasComment(Selection) Then Exit Sub   '<- added
    ActiveCell.Comment.Visible = False
    For Each r In Selection
        If Not r.Comment Is Nothing Then
            With r.Comment.Shape
                .Width = 502
                .Height = 150
            End With
        End If
    Next r
End Sub

Function bHasComment(cell As Range) As Boolean
    On Error Resume Next
    bHasComment = cell.Comment.Parent.Address = cell.Address
End Function

 

[sadams1]

greatly appreciate the help...still messing with it to see if I'm not doing something right to get it to work.

 

[rollis13]

Is Run-time error #91 resolved? It should've been. To work, the macro and the UDF go in the same module; if separated, the UDF goes into a standard module.

 

[sadams1]

What I'm finding is:
1. Run time error resolved
2. code works for one cell
3. the comment boxes aren't adjusting size if the range more than 1 cell

What I did was to copy the code from the browser right into a new module so not fat fingers....I think this is what was meant by having the macro & UDF in the same module?

 

[rollis13]

Patch the macro as follows. The UDF doesn't need any modification and yes, it goes where you intended.

Option Explicit
Sub cmtHideMakeSmall()
    '
    ' MacroHIDE Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
    Dim r     As Range
    For Each r In Selection
        If Not bHasComment(r) Then GoTo skip      '<- moved and changed
        r.Comment.Visible = False                 '<- moved and changed
        If Not r.Comment Is Nothing Then
            With r.Comment.Shape
                .Width = 502
                .Height = 150
            End With
        End If
skip:                                             '<- added
    Next r
End Sub

 

[sadams1]

That's it! Amazing as it makes something horrendously tedious into one macro. Can't thank you enough!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.