Force Macro Enable/Disable

[Maurush]

Hello guys,

I have a problem concerning macro's ... I made an excel workbook that will
show a sheet if macro's are disabled. Only that sheet will appear, in which it says
that people need to enable the macro's, close the file and then reopen.

When macro's are enabled people will not see that sheet again, and can start
answering questions and go through the rest of my sheets.

I found some files doing that under the name 'Splash' I don't know if anybody is familiar
with that term.

Anyway the problem is the following, I have multiple sheets that I want to show,
but I always want to start with the sheet called "Start Enquete" ... but the problem I
have is that other pages will show, after I have saved the file or quit the program.

The codes that I have are the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False

        Call HideSheets

        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With

    ThisWorkbook.Save
    ThisWorkbook.Saved = True

End Sub

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

    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False

        Call HideSheets

        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With

    ThisWorkbook.Save
    ThisWorkbook.Saved = True

End Sub

Private Sub Workbook_Open()
    
ActiveSheet.Unprotect ("")
    
    'Unhide all worksheets
    
            With Application
                .EnableCancelKey = xlDisabled
                .ScreenUpdating = False
        
                Call UnhideSheets
        
                .ScreenUpdating = True
                .EnableCancelKey = xlInterrupt
            End With
         
ActiveSheet.Protect ("")
        
End Sub

Private Sub HideSheets()

Dim Sheet As Object

    With Sheets("Macros")
        .Visible = xlSheetVisible
            For Each Sheet In Sheets
                If Not Sheet.Name = "Macros" Then
                    Sheet.Visible = xlSheetVeryHidden
                End If
            Next
        Set Sheet = Nothing
    End With

End Sub

Private Sub UnhideSheets()

    Sheets("Start Enquéte").Visible = xlSheetVisible
    Sheets("Macros").Visible = xlSheetVeryHidden
    Sheets("Clustermanagers blad 1").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 2").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 3").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 4").Visible = xlSheetVisible
    Sheets("End").Visible = xlSheetVisible
       
End Sub

What do I need to change at the code so that always when open the file,
when macro's are enabled, the file starts with Sheets("Start Enquéte")???

Thanks for the help!!

 

[GlennUK]

Isn't it just

Sheets("Start Enquéte").Select

before the End Sub of UnhideSheets?

 

[Maurush]

Isn't it just

Sheets("Start Enquéte").Select

before the End Sub of UnhideSheets?

I have tried this, but it doesn't work. Then when macros are disabled, it will show the Start Enquéte sheet.

It needs to show the Macros sheet.

Any other ideas?

 

[Maurush]

So what I want is shown on the following link: http://www.datapigtechnologies.com/flashfiles/forcemacros.html

The problem is that I have more sheets to show then only one after macro's are enabled.

After Save or Close, the workbook always needs to start with "Start Enquete" or if macro's
are disabled, with the sheet "Macros"

Now this doesn't work, when macros are disabled i get a notification about macro's are disabled but
the wrong sheet is showing, the last sheet "End" is showing.

Please some help on this!!

 

[GlennUK]

I have tried this, but it doesn't work. Then when macros are disabled, it will show the Start Enquéte sheet.

It needs to show the Macros sheet.

Any other ideas?

... I'd say that you didn't follow my instructions, at a guess. I said put that code before the End Sub of UnhideSheets ... is that what you did? I can't say, as you haven't posted your amended code.

 

[Maurush]

... I'd say that you didn't follow my instructions, at a guess. I said put that code before the End Sub of UnhideSheets ... is that what you did? I can't say, as you haven't posted your amended code.

Sorry Mate, it seems to be working today, definately don't know what I did wrong yesterday.
Yesterday after save it gave me some problems, pretty weird. Anyway that problem is solved.

Can I get your help with something else concerning this issue.

I have changed the code so that people if they click on cancel they get a notification,
that the can quit the program but the workbook doesn't get saved. I want the people to finally
press a commandbutton to save.

Now I have already managed to do so with the BeforeCancel sub, the only thing for me
to do is to change the code for the BeforeSave sub.

I know that if I want to disable the Save and Save as function of excel I need to put in the following code:

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

End Sub

But this way I cannot save the workbook at all. What I want to know is: how can I
say the Save is dependable of clicking a Commanbutton? This is the code of my commandbutton:

Private Sub CommandButton11_Click()

'Niet meewerken aan een diepte interview

        If ComboBox1.Value > 1 And ComboBox2.Value = "Nee" Then
                      
             If MsgBox("Weet u zeker dat u het document wilt opslaan en afsluiten?", vbYesNo + vbQuestion, "Bestand opslaan en afsluiten") = vbYes Then
                ActiveWorkbook.Save
                ActiveWorkbook.Close
             End If
                                           
'Meewerken aan een diepte interview en mail contact
   
        ElseIf ComboBox3.Value = "E-mail" And ComboBox2.Value = "Ja" And TextBox1.Value > "" And TextBox2.Value > "" Then
        
            If MsgBox("Weet u zeker dat u het document wilt opslaan en afsluiten?", vbYesNo + vbQuestion, "Bestand opslaan en afsluiten") = vbYes Then
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            End If
                                   
'Meewerken aan een diepte interview en telefonisch contact
   
        ElseIf ComboBox3.Value = "Telefonisch" And ComboBox2.Value = "Ja" And TextBox1.Value > "" And TextBox2.Value > "" And TextBox3.Value > "" Then
        
            If MsgBox("Weet u zeker dat u het document wilt opslaan en afsluiten?", vbYesNo + vbQuestion, "Bestand opslaan en afsluiten") = vbYes Then
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            End If
               
        Else
        
        MsgBox "Nog niet alle velden zijn ingevuld!", vbCritical, "Ontbrekende waarden"
        
        End If

End Sub

What do I need to do so that the BeforeSave sub looks at what happens in my commandbutton,
so that I eventually can save through this button and not Excel save functions themselves.

 

[Maurush]

Sorry Mate, it seems to be working today, definately don't know what I did wrong yesterday.
Yesterday after save it gave me some problems, pretty weird. Anyway that problem is solved.

Can I get your help with something else concerning this issue.

Nevermind my question above, I solved this.

But still got problems with the enable disable part after save. Everything works the first time.
But when I save the part and macro's are disable again and I re-open the file, I do not see
the "Macros" page, the page with information about enabling macro's.
Instead I see the last page I saw with the commandbutton on it with the save action.

Edit: How can i upload files here?

http://www.megaupload.com/?d=TOFC9UG6

Try the file I uploaded, the first time it will work, but after you save then disabled macro's
and open the file again you see what I mean.

Please help me with this, this is the last thing I need for my excel file, time is running out
for my enquete research.

 

[GlennUK]

Security policy at work here is blocking that upload site, so I can't see your file. Maybe someone else on here can see it, and work out what's going wrong.

 

[Maurush]

Security policy at work here is blocking that upload site, so I can't see your file. Maybe someone else on here can see it, and work out what's going wrong.

Hopefully you can help me later today. Still couldn't find out what I need to change.
Also on other Message Boards I couldn't find answers, tried many different things,
hide and unhide subs with loops in them, but also with visibility of the different sheets
directly on worksheet_open() and worksheet_beforeclose().

The part you helped me is working, putting worksheet("").select at the end of the unhide sub.

But it is like the workbook is not updating on close. If I save the file and close it,
disable macros, and open the file again .. it's like the workbook is still stuck in the last page.

Even if i tell in workbook_open() to show my "Macros" page:

            sheets("Macros").Visible = xlSheetVisible
            sheets("Macros").Select
            Call UnhideSheets

If macros are disabled, then the only thing it can do is show the macros sheet right?

 

[GlennUK]

If macros are disabled your workbook_open won't do anything. So no, wrong.