Closing a workbook

  • Thread starter Thread starter N1KO
  • Start date Start date
N

N1KO

Hi,

I need (if possible) for a workbook to close if the "Disable Macro" button
is clicked when the workbook first opens.

Is this possible?

Thanks in advance

NIKO
 
I know of no way to do that. Closing the workbook automatically would take
a VBA macro and that macro would not run if macros are disabled.
But there are other ways of insuring that the workbook is opened in the way
you want. Post back and detail what you are doing and what you want. HTH
Otto
 
Basically, you can setup a macro that runs whenever the file is closed.
That macro would set your data to a condition that would render the workbook
useless. Then you would have another macro that fires whenever the workbook
is opened. That macro would reset your data to what you want. HTH Otto
 
Basically what happened was, a login box appeared and the "Macro Security"
options didn't appear.

Now the options do (Enable/Disable Macros).

If you disable the macros you can see everything on the sheet, what i need
is for them to see nothing. Could i do something on the close application
macro to maybe hide everything and protect everything and then if they login
properly and select the enable macro button to show it all again?

Would this be possible?
 
Put these 2 macros in the Workbook module. I assumed that you have a splash
sheet named "Splash". When the file is closed by the user, the first macro
will fire and will make the Splash sheet visible and all the others
VeryHidden. If the file is subsequently opened with macros disabled, all
the user will see is the Splash sheet. If the workbook is opened with
macros enabled, all the sheets will be made visible and the Splash sheet
will be made VeryHidden. HTH Otto
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Sheets("Splash").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then _
ws.Visible = xlSheetVeryHidden
Next ws
ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then _
ws.Visible = xlSheetVisible
Next ws
Sheets("Splash").Visible = xlSheetVeryHidden
End Sub
 
Thanks Otto

Otto Moehrbach said:
Put these 2 macros in the Workbook module. I assumed that you have a splash
sheet named "Splash". When the file is closed by the user, the first macro
will fire and will make the Splash sheet visible and all the others
VeryHidden. If the file is subsequently opened with macros disabled, all
the user will see is the Splash sheet. If the workbook is opened with
macros enabled, all the sheets will be made visible and the Splash sheet
will be made VeryHidden. HTH Otto
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Sheets("Splash").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then _
ws.Visible = xlSheetVeryHidden
Next ws
ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Splash" Then _
ws.Visible = xlSheetVisible
Next ws
Sheets("Splash").Visible = xlSheetVeryHidden
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

closing another file by code causes failure to run workbook activate event 2
Excel Protection 2
Unwanted workbooks on startup 2
MACRO TO COUNT ALL OPEN WORKBOOKS and print 1
Copy & Paste from One Workbook to a Macroed Template Workbook 1
Close current workbook 1
COM addin won't connect when workbook opened via another workbookfrom another instance 3
How to Unlock Excel Workbook protected by MACRO Password 6

Back
Top