Hide worksheets before close

  • Thread starter Thread starter JoeleneW
  • Start date Start date
J

JoeleneW

I have a workbook with multiple sheets that I would like to Hide before
close. I am using a macro attached to a "Quit" button, but it does not
work.
I also have a Sub in ThisWorkbook, but I can't seem to call it

Sub Button14_Click_Quit()

Application.DisplayAlerts = False

Worksheets("A Detail").Visible = False
Worksheets("A Metrics").Visible = False
Worksheets("B DV Detail").Visible = False
Worksheets("B DV Metrics").Visible = False
Worksheets("B Detail").Visible = False
Worksheets("B Metrics").Visible = False
Worksheets("C Metrics").Visible = False
Worksheets("C Detail").Visible = False
Worksheets("D Detail").Visible = False
Worksheets("D Metrics").Visible = False

Workbooks("KeyCustomerMetrics.XLS").Close


End Sub


The code in ThisWorkbook:

Public Sub Workbook_BeforeClose(Cancel As Boolean)

Worksheets("A Detail").Visible = False
Worksheets("A Metrics").Visible = False
Worksheets("B DV Detail").Visible = False
Worksheets("B DV Metrics").Visible = False
Worksheets("B Detail").Visible = False
Worksheets("B Metrics").Visible = False
Worksheets("C Metrics").Visible = False
Worksheets("C Detail").Visible = False
Worksheets("D Detail").Visible = False
Worksheets("D Metrics").Visible = False
End Sub

If I click on File, Close, it works--not sure what i am doing wrong....
any help would be much appreciated

J
 
You can't just make up event procedure names. You must use the
event names provided by VBA. Assuming your button is named
Button14, change

Sub Button14_Click_Quit()
to
Sub Button14_Click()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Guess I should explain better.
I created the button using the Forms toolbar, then right-clicked to
assign macro. I created the macro and re-named it, and that is where
the name came from.
The workbook closed when i clicked the button, but the sheets did not
hide.
I re-named the procedure and re-assigned the button macro, but still,
the sheets remained visible.

Got it--as i worked on it, i realized that i didn't save before
close....now it works

thanks for the help,--your website is great, by the way

J
 
Back
Top