if any columns hidden then ask if want to unhide before saving

[opinionated86]

Hi
I've got a spreadsheet that several people use, some of them regularly hide columns to make it easier to look at while they work, the problem is the forget to unhide the columns once they're done, I've figured out how to stop this with a 'Workbook_BeforeSave' private sub that unhides all columns but now these same colleagues are finding more bizarre ways to get round this like squashing the column width down to zero, what i want is to make it so that if they have hidden columns they get asked before saving if they want to unhide them, hopefully this extra prompt will get them to remember to unhide on their final save when they're done, the code below is what I've got so far, i don't want the prompt to come up if there aren't any hidden columns as then it's just a pain to everyone and more likely to get ignored.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Columns.Hidden = True Then
If MsgBox("Do you want to unhide any hidden cells?", vbYesNo) = vbYes Then
Columns.Hidden = False
End If
End If
End Sub

I've tried a few other things like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Master").Range("A:AU").EntireColumn.Hidden Then
If MsgBox("Do you want to unhide any hidden cells?", vbYesNo) = vbYes Then
Columns.Hidden = False
End If
End If
End Sub

But this only works on the first column specified. Any help would be very much appreciated.

 

[BarryL]

why give them the option? why not just call a sub to unhide everything and autofit the columns.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cells.EntireColumn.Hidden = False
 Cells.Columns.AutoFit

End Sub

or if you need the msgbox maybe

lc = Cells(1, Columns.Count).End(xlToLeft).Column
for I = 1 to lc step 1
if cells(1, lc).hidden = true then MsgBox("Do you want to unhide any hidden cells?", vbYesNo)

 

[opinionated86]

Thanks for the reply but I don't really understand the solution, I'm self taught so there are big holes in what I know, I'm guessing the second code needs to be part of a loop? Not really used the Next function at all which is what VBA is saying I'm missing when I drop your code into my sub. The reason I want the question rather than to just automate it is that I know they'll just find another way to get around it and I've had a hard enough time persuading them to let me add macros and automate stuff as it is.

 

[opinionated86]

Thanks with a few tweeks i've got this to work