Very hidden sheets don't stay hidden

[ChrisC]

Hi -I've been having problems keeping very hidden sheets hidden. I go to the VBA and apply the "very hidden" designation to the worksheet. When I save, the sheet becomes visible again. Am I missing a step?

Thanks much

 

[Mike H..]

Hi -I've been having problems keeping very hidden sheets hidden. I go to the VBA and apply the "very hidden" designation to the worksheet. When I save, the sheet becomes visible again. Am I missing a step?

Thanks much

Hi,

Do you have any VBA code in the workbook that could be unhiding the worksheet(s)?

 

[ChrisC]

Not to my knowledge. How would I know that? Its a spreadsheet I inherited by the way.

 

[ChrisC]

This is the only code I found

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Mike H..]

This is the only code I found

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Chris,

If this happens as you save then it could be caused by some event code. Alt +F11 to open VB editor, double click 'ThisWorkbook'. What do you see in the white space on the right? If there's some code that begins with the line below then paste it in to reply here. Remember to use code tags if you do.

Can you attach the workbook that does this?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

 

[ChrisC]

Here is the code I see

Option Explicit

Private Sub Workbook_Open()
Call prCust_WbOpen

Application.Iteration = True

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If abortchangeevent = True Then
Exit Sub
End If

Call Solve_Carry_Costs


Call prCust_WbBeforeSave(SaveAsUI, Cancel)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If abortchangeevent = True Then
Exit Sub
End If

Call Solve_Carry_Costs


Call prCust_WbBeforeClose(Cancel)


End Sub

 

[Asheesh]

hi Chris...

Worksheet unhide property is not mentioned anywhere in the above code...It will be very helpful, if you could share the file...

 

[NARAYANK991]

Hi Chris ,

Can you please clarify one point ?

Do the hidden sheets become visible when you just save the workbook , or when you close ( with a save ) and reopen ?

If it is the former , we need to look at the code within these procedures :

Call Solve_Carry_Costs

Call prCust_WbBeforeSave(SaveAsUI, Cancel)

If it is the latter , we need to look at the code within these procedures :

Call Solve_Carry_Costs

Call prCust_WbBeforeClose(Cancel)

Call prCust_WbOpen

However , the easiest way to decide whether it is the code which is doing it , or Excel itself , is to rename the event procedures by just adding the suffix 1 , as in :

Private Sub Workbook_BeforeSave1(ByVal SaveAsUI As Boolean, Cancel As Boolean)

This ensures that no code will be executed when you do a manual Save ; if the problem does not occur , it is very clear that it is the code within the event procedure which is causing it.

Narayan

 

[ChrisC]

It show's the hidden workbooks after the save.

 

[ChrisC]

That worked! Thanks.