P
pgjoshi
Can I force the user to enable macros before opeining workboo
containing macros
containing macros
K
Ken Wright
LOL - No - don't you think the virus writers would love that 
But - what you can do though, is hide all the salient sheets in the workbook,
leaving a sheet that explains they will not see anything at all until they
enable macros before opening the workbook. Then, in the workbook_open event,
have some code display all the sheets but hide the warning sheet. If you hide
the sheets to start with using the xlveryhidden option, then they won't even
know they are there if they open it without macros and poke around.
Now, if they open without macros enabled, they get a warning and no data, but as
long as they are enabled, they just get the data.
But - what you can do though, is hide all the salient sheets in the workbook,
leaving a sheet that explains they will not see anything at all until they
enable macros before opening the workbook. Then, in the workbook_open event,
have some code display all the sheets but hide the warning sheet. If you hide
the sheets to start with using the xlveryhidden option, then they won't even
know they are there if they open it without macros and poke around.
Now, if they open without macros enabled, they get a warning and no data, but as
long as they are enabled, they just get the data.
M
Martyn
Ken, considering there are lots of macro programming beginners in this NG
could you please set a clear example how we can do that?
TIA
could you please set a clear example how we can do that?
TIA
K
Ken Wright
This assumes you have a workbook with say 5 sheets (Doesn't matter how many or
what they are called - Other than the Warning sheet which must be called warning
in this case - or simply change the macro to reflect the name you give it):-
1 - 'Warning'
2 - 'Sht 1'
3 - 'Sht 2'
4 - 'Sht 3'
5 - 'Sht 4'
Whilst in the VBE (ALT+F11), put the following code into the 'ThisWorkbook'
module:-
Private Sub Workbook_Open()
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Sheets(i).Visible = True
Next i
Sheets("Warning").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Sheets("Warning").Visible = True
If Sheets(i).Visible = True And Sheets(i).Name <> "Warning" Then
Sheets(i).Visible = xlVeryHidden
End If
Next i
Application.DisplayAlerts = False
ActiveWorkbook.Save
End Sub
As long as macros were enabled, then this will hide the warning sheet and
display all the other sheets
Finally you need to protect the VBA code so no-one can see it and work out
what's going on, so Hit ALT+F11 - take a look at the top left of your screen,
and hopefully you will see a window entitled VBA Project Explorer. In this
window there will be a list of filenames, just like the folder names in an
Explorer window. Find the name of your file in that window and right click on
your workbook name and then from the menu that pops up, select VBAProject
properties. This will then give you a grey dialog box with two tabs. Click on
the Protection Tab, put a tick in the 'Lock Project for viewing' option, put in
a password and confirm it below, then hit OK. Do File / Save Workbookname, File
Close and return to Excel, then close file and reopen.
You should now not be able to get to the VBAProject Code if you go into the VBE.
NOTE:-
This use the Before_close event to hide the sheets, so that it is ready to start
afresh next time, BUT, if they are closing because they just don't want to save
the changes they made, then the code is going to save anyway to ensure the
sheets are hidden by default on opening.
what they are called - Other than the Warning sheet which must be called warning
in this case - or simply change the macro to reflect the name you give it):-
1 - 'Warning'
2 - 'Sht 1'
3 - 'Sht 2'
4 - 'Sht 3'
5 - 'Sht 4'
Whilst in the VBE (ALT+F11), put the following code into the 'ThisWorkbook'
module:-
Private Sub Workbook_Open()
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Sheets(i).Visible = True
Next i
Sheets("Warning").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Sheets("Warning").Visible = True
If Sheets(i).Visible = True And Sheets(i).Name <> "Warning" Then
Sheets(i).Visible = xlVeryHidden
End If
Next i
Application.DisplayAlerts = False
ActiveWorkbook.Save
End Sub
As long as macros were enabled, then this will hide the warning sheet and
display all the other sheets
Finally you need to protect the VBA code so no-one can see it and work out
what's going on, so Hit ALT+F11 - take a look at the top left of your screen,
and hopefully you will see a window entitled VBA Project Explorer. In this
window there will be a list of filenames, just like the folder names in an
Explorer window. Find the name of your file in that window and right click on
your workbook name and then from the menu that pops up, select VBAProject
properties. This will then give you a grey dialog box with two tabs. Click on
the Protection Tab, put a tick in the 'Lock Project for viewing' option, put in
a password and confirm it below, then hit OK. Do File / Save Workbookname, File
Close and return to Excel, then close file and reopen.
You should now not be able to get to the VBAProject Code if you go into the VBE.
NOTE:-
This use the Before_close event to hide the sheets, so that it is ready to start
afresh next time, BUT, if they are closing because they just don't want to save
the changes they made, then the code is going to save anyway to ensure the
sheets are hidden by default on opening.