Putting the "Enable Macros" button into a userform type display

[Stildawn]

Hi All

In reference to my other topci here: http://www.mrexcel.com/forum/excel-questions/701123-userform-error.html

In this workbook I have successfully made it act like a userform calculator, the user does not ever see the actual sheets in the workbook, their only interface is the Userform calculator I created.

This is exactly how I want it, they do not need to see the sheets, nor do I want them to.

This leads me only to my next problem... Some of the users obviously wont have Macros automatically enabled, what I am wondering is, is there a way to put the normal "Enable Macros" button which appears along the top of a macro containing workbook when opened, I want to put this button into a userform like display without showing the workbook at all.

So here the process I envision:

1. User double clicks the file
2. It opens and automatically goes invisable (like it does already)
3. If the user does not have macro enabled, a userform like box appears and says "Please enable Marcros to user" with a button below for "Enable" and "Exit"
4. If exit is pressed excel closes
5. If enabled is pressed Macros are enabled and then they will automatically see my calculator userform like normal.

As you see, at no point do I want them to see excel or a workbook, just userforms so its not like they are using excel at all.

I have searched around the net and found a few ideas to tackle this, mainly one that has a "Prompt" workbook that shows when macros are disabled, but that is not ideal for me. However perhaps I could just edit this "prompt" code thing for my situation?

Any help would be greatly appreciated.





Here is the prompt sheet code I talked about:

[COLOR=#0000ff]Option Explicit[/COLOR] 

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Workbook_Open() 
     
    [COLOR=blue]With[/COLOR] Application 
         [COLOR=darkgreen]'disable the ESC key[/COLOR]
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = [COLOR=blue]False[/COLOR] 
         
        [COLOR=blue]Call[/COLOR] UnhideSheets 
         
        .ScreenUpdating = [COLOR=blue]True[/COLOR] 
         [COLOR=darkgreen]'re-enable ESC key[/COLOR]
        .EnableCancelKey = xlInterrupt 
    [COLOR=blue]End With[/COLOR] 
     
[COLOR=blue]End Sub[/COLOR] 
[COLOR=darkgreen]'[/COLOR]
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UnhideSheets() 
     [COLOR=darkgreen]'[/COLOR]
    [COLOR=blue]Dim[/COLOR] Sheet [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
     [COLOR=darkgreen]'[/COLOR]
    [COLOR=blue]For Each[/COLOR] Sheet [COLOR=blue]In[/COLOR] Sheets 
        [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] Sheet.Name = "Prompt" [COLOR=blue]Then[/COLOR] 
            Sheet.Visible = xlSheetVisible 
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
    [COLOR=blue]Next[/COLOR] 
     [COLOR=darkgreen]'[/COLOR]
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     [COLOR=darkgreen]'[/COLOR]
    Application.Goto Worksheets(1).[A1], [COLOR=blue]True[/COLOR] [COLOR=darkgreen]'< Optional[/COLOR]
     [COLOR=darkgreen]'[/COLOR]
    [COLOR=blue]Set[/COLOR] Sheet = [COLOR=blue]Nothing[/COLOR] 
    ActiveWorkbook.Saved = [COLOR=blue]True[/COLOR] 
     
[COLOR=blue]End Sub[/COLOR] 

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR]) 
    [COLOR=blue]With[/COLOR] Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = [COLOR=blue]False[/COLOR] 
         
        [COLOR=blue]Call[/COLOR] HideSheets 
         
        .ScreenUpdating = [COLOR=blue]True[/COLOR] 
        .EnableCancelKey = xlInterrupt 
    [COLOR=blue]End With[/COLOR] 
[COLOR=blue]End Sub[/COLOR] 

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] HideSheets() 
     [COLOR=darkgreen]'[/COLOR]
    [COLOR=blue]Dim[/COLOR] Sheet [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] [COLOR=darkgreen]'< Includes worksheets and chartsheets[/COLOR]
     [COLOR=darkgreen]'[/COLOR]
    [COLOR=blue]With[/COLOR] Sheets("Prompt") 
         [COLOR=darkgreen]'[/COLOR]
         [COLOR=darkgreen]'the hiding of the sheets constitutes a change that generates[/COLOR]
         [COLOR=darkgreen]'an automatic "Save?" prompt, so IF the book has already[/COLOR]
         [COLOR=darkgreen]'been saved prior to this point, the next line and the lines[/COLOR]
         [COLOR=darkgreen]'relating to .[A100] below bypass the "Save?" dialog...[/COLOR]
        [COLOR=blue]If[/COLOR] ThisWorkbook.Saved = [COLOR=blue]True[/COLOR] [COLOR=blue]Then[/COLOR] .[A100] = "Saved" 
         [COLOR=darkgreen]'[/COLOR]
        .Visible = xlSheetVisible 
         [COLOR=darkgreen]'[/COLOR]
        [COLOR=blue]For Each[/COLOR] Sheet [COLOR=blue]In[/COLOR] Sheets 
            [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] Sheet.Name = "Prompt" [COLOR=blue]Then[/COLOR] 
                Sheet.Visible = xlSheetVeryHidden 
            [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
        [COLOR=blue]Next[/COLOR] 
         [COLOR=darkgreen]'[/COLOR]
        [COLOR=blue]If[/COLOR] .[A100] = "Saved" [COLOR=blue]Then[/COLOR] 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
         [COLOR=darkgreen]'[/COLOR]
        [COLOR=blue]Set[/COLOR] Sheet = [COLOR=blue]Nothing[/COLOR] 
    [COLOR=blue]End With[/COLOR] 
     [COLOR=darkgreen]'[/COLOR]
[COLOR=blue]End Sub[/COLOR]

How to use:	Open an Excel workbook Select Tools/Macro/Visual Basic Editor In the VBE window, select View/Project Explorer Select the ThisWorkbook module Copy and paste the code above into this Module Now select File/Close and Return To Microsoft Excel Dont forget to save your changes... 8. Name one of your sheets "Prompt"

<TBODY>
</TBODY>

 

[Dryver14]

Have you tried putting the file in a Macro enabled folder:

see here.

Enable All Macros In Excel 2010

Same process for 2007 as well

 

[Stildawn]

How do you create a macro enabled folder? Actually just googled it and I doubt it will work as it looks like another setting for my personal copy of excel.

This FRT Calculator could be potentially passed around the entire business (including going off for use in other countries etc lol), and to make it as idiot proof as possible I have made it so only the only interface the user see's is the userform I have created. But this enabling macro's thing is ruining this as when macros are disabled it will bring up excel (showing the sheets etc) with the usual "Enable Macros" prompt along the top of the excel window.

I really just need to put that prompt inside a little window before my userform displays (so that people still get the choice to not enable, I'm not trying to be dodgy just trying to present it all better?

Thanks for the idea though, I'll test a Macro folder to see if that would work.