EirikDaude
11-27-2014, 05:52 AM
I have a workbook with several sheets in each. In every one of those sheets I only want to show rows 33:106. I tried just highlighting every sheet and hiding the relevant rows, but I got an error message "Cannot shift objects off sheet". After a bit of googling, as well as some trying and failing, I discovered that this is because there are comments in the worksheets, and when I e.g. hide rows 1:32 they may get repositioned so that they overlap with the other area I want to hide, or in the example below, they are repositioned to somewhere "outside" the worksheet.
Browsing the locals and object browser, it seemed that it was the topleftcell and bottomrightcell which gave the position of the comment, but trying to reposition the textbox using those addresses failed.
At the moment I have two solutions in mind:
Find a way to reposition the comment box before hiding the rows
Find a way to discover the address of the cell the textbox is linked to, so I can copy, remove, and then add a new comment with similar fields to the cell after the rest of the macro has run its course.
However, I have no idea how to do this, and my google-fu has failed miserably. Therefore, I decided to turn to your insight - I really hope you can help!
Sub hiderows()
Dim i As Long, r As Range, c As Range, sh As Shape, ws As Worksheet
For Each ws in Worksheets
With ws
For Each sh In ws.Shapes
If sh.Type = msoComment Then
Set sh.TopLeftCell = sh.TopLeftCell.Offset(-32, 0)
Set sh.BottomRightCell = sh.BottomRightCell.Offset(-32, 0)
EndIf
Next
ws.Rows("1:32").EntireRow.Hidden = True
End With
Next
End Sub
Browsing the locals and object browser, it seemed that it was the topleftcell and bottomrightcell which gave the position of the comment, but trying to reposition the textbox using those addresses failed.
At the moment I have two solutions in mind:
Find a way to reposition the comment box before hiding the rows
Find a way to discover the address of the cell the textbox is linked to, so I can copy, remove, and then add a new comment with similar fields to the cell after the rest of the macro has run its course.
However, I have no idea how to do this, and my google-fu has failed miserably. Therefore, I decided to turn to your insight - I really hope you can help!
Sub hiderows()
Dim i As Long, r As Range, c As Range, sh As Shape, ws As Worksheet
For Each ws in Worksheets
With ws
For Each sh In ws.Shapes
If sh.Type = msoComment Then
Set sh.TopLeftCell = sh.TopLeftCell.Offset(-32, 0)
Set sh.BottomRightCell = sh.BottomRightCell.Offset(-32, 0)
EndIf
Next
ws.Rows("1:32").EntireRow.Hidden = True
End With
Next
End Sub