Macro security help

[Cloudy]

Hi all,

i want people whose macro security that is set to middle, cannot open the
file. It would be appreciated if the application will auto close the
window/application.

Is there any code for this? If so please advice! Thanks!

 

[Jan Karel Pieterse]

Hi Cloudy,

i want people whose macro security that is set to middle, cannot open the
file. It would be appreciated if the application will auto close the
window/application.

Is there any code for this?

No, because disabling macros -of course- disables the code.
What I'd do is: Have a before_save event that hides all sheets (but one, with a
warning message to enable macros), save and then shows the sheets again.
Have a workbook_Open event that shows the sheets.
Then if someone disables macros, he ends up with that one warning sheet.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Cloudy]

Thanks for your response and suggestion. But i am new in VBA. If i want to
use your suggestion into my situation, where do i place the codes? Below is
what i have.


Option Explicit

Const WelcomePage = "Prompt"

Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
End Sub

Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideAllSheets
ThisWorkbook.Saved = True
End Sub

Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub

Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
If KeyCode = 44 Then
Clipboard.Clear
End If
End Sub

 

[Jan Karel Pieterse]

Hi Cloudy,

See my comments inline.

Option Explicit

Const WelcomePage = "Prompt"

I'd put the above in a normal module (the same as mentioned below)

Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
End Sub

The above belongs in the ThisWorkbook module

Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

The above belongs in a normal module

Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideAllSheets
ThisWorkbook.Saved = True
End Sub

The above belongs in the ThisWorkbook module

Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub

The above belongs in a normal module

Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
If KeyCode = 44 Then
Clipboard.Clear
End If
End Sub

Not sure what the above relates to. Seems to belong to a userform.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com