PDA

View Full Version : VBA - Hiding rows in worksheets with comments



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

Teeroy
11-28-2014, 07:43 PM
It sounds like you have comment and indicator displayed. If you change to indicator only displayed there shouldn't be a problem. You could probably even wrap your code in:


Application.DisplayCommentIndicator = xlCommentIndicatorOnly
... Your code ...
Application.DisplayCommentIndicator = xlCommentAndIndicator

EirikDaude
12-01-2014, 12:47 AM
Thanks for your suggestion. Unfortunately, I still get an error when I run the macro:
http://i.imgur.com/4QxJswxs.png?1 (http://imgur.com/4QxJswx)
Going back to the worksheet and attempting to hide the rows "by hand" after setting DisplayCommentIndicator to xlCommentIndicator only, I still get the error message:
http://i.imgur.com/9bdS11Hs.png?1 (http://imgur.com/9bdS11H)

snb
12-01-2014, 03:16 AM
Why don't you post a sample workbook representing yours without sensitive content ?