Enable Macros

[isma786]

I have an spreadsheet that has macros in it but i don't want people to disallow the macros enabling.

Thanks

 

[SkipVought]

Hi,

What you need to do is somewhat complex.

Your workbook needs to be saved using code that has a dummy sheet active and ALL OTHER SHEETS VERY HIDDEN. Very Hidden sheets can ONLY be unhidden via code or the VB Editor.

So if the user disables macros, they can't use the workbook. All they see is the dummy sheet that has nothing useful on it and they can't see that there are any other sheets.

If they enable macros, the Workbook_Open event macro unhides the very hidden sheets and makes the workbook macros available.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[isma786]

thanks for the response skip but what code do i need to do that?

I understand a dummy sheet but what code in the open event?

sorry to be a pain.

 

[SkipVought]

Insert a Sheet named Dummy
alt+F11 - Activates VB Editor
ctrl+R - view the Project Explorer
right click ThisWorkbook - select View Code
copy and paste this code into the WORKBOOK OBJECT Code Sheet -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Worksheets("Dummy").Visible = xlSheetVisible
   For Each ws In Worksheets
     With ws
       If .Name <> "Dummy" Then
         .Visible = xlSheetVeryHidden
       End If
     End With
   Next
End Sub

Private Sub Workbook_Open()
   For Each ws In Worksheets
     With ws
       If .Name <> "Dummy" Then
         .Visible = xlSheetVisible
       End If
     End With
   Next
   Worksheets("Dummy").Visible = xlSheetVeryHidden
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[isma786]

thanks skip will have a go at that now...