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

opinionated86

New Member
Joined
Feb 2, 2016
Messages
12
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.
Code:
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:
Code:
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

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
why give them the option? why not just call a sub to unhide everything and autofit the columns.

Code:
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

Code:
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

New Member
Joined
Feb 2, 2016
Messages
12
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.
 

Forum statistics

Threads
1,082,607
Messages
5,366,591
Members
400,904
Latest member
srija

Some videos you may like

This Week's Hot Topics

Top