Progromatically Change or Test Macro Security Level

  • Thread starter Thread starter bsullins
  • Start date Start date
B

bsullins

Greetings,

I am attempting to either

Change macro security level to low so my user does not get the 'enabl
macros' dialogue box

OR

Test to see if macro's are enabled...

If the macro's are disabled then show a warning asking the user t
enable them.

Here's what i've been able to come up with but its not working ver
well, anything will help..

/***************************************/
Sub Security()
If Application.AutomationSecurity
msoAutomationSecurityForceDisable Then
MsgBox "You must enable macro's to use this workbook"
End If
End Sub
/***************************************
 
Good point....:-P

I was thinking maybe include the code in the XLSTART folder but tha
wouldn't be specific to the workbook.

Not sure on that one, I guess the only thing to do is to educate th
user regarding this message box....

:confused:

Regards,

Ben Sullin
 
I only want to show a warning when macro security is high.
I really need that. Is there a way for this???
thanx
 
There is one funny way...

gardrop said:
I only want to show a warning when macro security is high.
I really need that. Is there a way for this???
thanx
Click to expand...
If it's only possible for your task you can try to use this way.

1. Create new workbook.
2. Create new VBA-module .
3. Copy VBA-code below and then paste it into module.
PHP: 
Sub runOnceToCreateAlertSheet()
	Dim wks As Worksheet
	Set wks = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
	wks.Name = "SecurityAlert"
	wks.Activate
	Cells.Interior.ColorIndex = 3 'Red'
	With Range("B10")
		.Value = "Security is High!!!"
		.Font.ColorIndex = 2
		.Font.Size = 72
	End With
End Sub
 
Sub beforeClose()
	Dim wks As Worksheet
	ThisWorkbook.Worksheets("SecurityAlert").Visible = xlSheetVisible
	For Each wks In ThisWorkbook.Worksheets
		If wks.Name <> "SecurityAlert" Then
			wks.Visible = xlSheetHidden
		End If
	Next wks
	ThisWorkbook.Save
End Sub
 
Sub afterOpen()
	Dim wks As Worksheet
	For Each wks In ThisWorkbook.Worksheets
		If wks.Name <> "SecurityAlert" Then
			wks.Visible = xlSheetVisible
		End If
	Next wks
	ThisWorkbook.Worksheets("SecurityAlert").Visible = xlSheetHidden
End Sub
4. Run macro "runOnceToCreateAlertSheet" - red alert sheet will be added into your workbook.
5. Arrange the following event procedures in your workbook:
PHP: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
	Call beforeClose
End Sub
 
Private Sub Workbook_Open()
	Call afterOpen
End Sub
6. Save and close your new workbook.
7. Change security level to High and open workbook - look what's happening.
8. Next time change security level to Medium and open workbook - look what's happening.
 
Last edited:
Back
Top