Error 1004: …Hidden Property…Range Class - Suspect Comments but Cannot Solve

MayJustQuit

New Member
Joined
Jun 19, 2015
Messages
2
I have a workbook where it is normal for rows and columns to be hidden.

I have been using these show/hide features since the beginning and I have only now encountered a strange phenomenon when I created a Subroutine that resets all the "Default" hidden and shown columns (there are some hidden by default).
This sub is called when the workbook is opened and also enters in some Error Handlers; it is there to make sure that anything hidden by the previous user is unhidden upon starting (or vice versa).

Suddenly, anytime I open the workbook and use the following scripts in ANY sub I get the 1004 error:
Code:
Sheet2.Cells.EntireRow.Hidden = False
Sheet3.Cells.EntireRow.Hidden = False
Sheet2.Cells.EntireColumn.Hidden = False
Sheet3.Cells.EntireColumn.Hidden = False
I strongly suspect [necessary] cell comments are the issue.

I use a subroutine to resize and reposition all comments. If I Call this subroutine from a button on a worksheet, suddenly ALL of the functionality works properly again. If I Call this subroutine during the workbook_open event, the error persists. If I call it during any other sub that uses the Sheet2.Cells.EntireRow.Hidden = False code, the problem persists. And here's the thing, ONLY after I call this Sub from a worksheet button is the problem fixed, but I ALSO need to call it a second time from within the subs. I need these scripts to work when the workbook opens WITHOUT user interaction.

The following sub is called on a WorkBook_Open Event:
Code:
Public Sub defaultSortAndFilter()
Dim i As Long
Dim rowArrayPG
    rowArrayPG = Array(15, 19, 21, 25, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51)
Dim rowArrayMD
    rowArrayMD = Array(15, 18, 20, 24, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49)
Call ResetComments
Sheet2.Cells.EntireRow.Hidden = False
Sheet3.Cells.EntireRow.Hidden = False
Sheet2.Cells.EntireColumn.Hidden = False
Sheet3.Cells.EntireColumn.Hidden = False
Dim lastColPG As Long
    lastColPG = Sheet2.Cells(5, Columns.Count).End(xlToLeft).Column
    revertLastColPG = lastColPG
Dim lastColMD As Long
    lastColMD = Sheet3.Cells(5, Columns.Count).End(xlToLeft).Column
    revertLastColMD = lastColMD
    
Dim sortRangePG As Range
    Set sortRangePG = Range(Cells(5, 3), Cells(55, lastColPG))
Dim sortRangeMD As Range
    Set sortRangeMD = Range(Cells(5, 3), Cells(53, lastColMD))
Application.ScreenUpdating = False
On Error GoTo ExplodeSheets
For i = 0 To UBound(rowArrayPG)
    Sheet2.Rows(rowArrayPG(i)).EntireRow.Hidden
Next
For i = 0 To UBound(rowArrayMD)
    Sheet3.Rows(rowArrayMD(i)).EntireRow.Hidden
Next
Sheet2.Range("sortRangePG").Sort Key1:=Range("C5"), Order1:=xlAscending, Orientation:=xlSortRows, Key2:=Range("C6"), Order2:=xlAscending, Key3:=Range("C9"), Order3:=xlAscending
Sheet3.Range("sortRangeMD").Sort Key1:=Range("C5"), Order1:=xlAscending, Orientation:=xlSortRows, Key2:=Range("C6"), Order2:=xlAscending, Key3:=Range("C9"), Order3:=xlAscending
Application.ScreenUpdating = True
Exit Sub
ExplodeSheets:
Sheet2.Cells.EntireRow.Hidden = False
Sheet3.Cells.EntireRow.Hidden = False
Sheet2.Cells.EntireColumn.Hidden = False
Sheet3.Cells.EntireColumn.Hidden = False
Application.ScreenUpdating = True
End Sub

And this is the ResetComments code:
Code:
Sub ResetComments()
Dim cmt As Comment
For Each cmt In Sheet2.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = cmt.Parent.Offset(0, 1).Left + 5
cmt.Shape.Height = 0
cmt.Shape.Width = 0
Next
For Each cmt In Sheet3.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = cmt.Parent.Offset(0, 1).Left + 5
cmt.Shape.Height = 0
cmt.Shape.Width = 0
Next
 
End Sub

I have spent a lot of time googling, this appears to be an oddball. If you need more code I can supply it but I cannot send the entire workbook since it contains some confidential info.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
Sounds like user has applied "Protect Sheet" or "Protect Workbook".
You can use Sheet2.Unprotect(password) and Sheet3.Unprotect(password) to unprotect them
 

MayJustQuit

New Member
Joined
Jun 19, 2015
Messages
2
I do have protected Sheets but they are set to "userinterfaceonly:=True". Also, as I said, it works when I play with the comment locations. I did not unlock the sheets at any point for my "workaround" to function.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top