VBA code won't run, even with Macro Security set to Enable All Macors

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

VBA newbie here.... well, at least a few months into it... and have
learned many things from this group... Thank you.
Anyway....

I've developed an Excel 2007 (Windows 7) sheet with a VBA front end
for data entry.
In order for this to run automatically (other colleagues use it)...
I'm using the Userform.Show command in the WorkBook_Open sub.
Looks like this...
Private Sub Workbook_Open()
frmMachVisCalculator.Show
End Sub

I've also password protected the entire workbook, VBA code, hidden
sheets, etc to "secure" it as much as possible from erroneous edits.
When I open the sheet (on my laptop)... the macro runs automatically,
as expected.
For others (other PCs)... it runs fine, but for some the Userform
never comes up.... and never displays any error message or anything??

I've instructed them to check these settings in their Trust Center....
Macro Settings - check-mark the "Enable All Macros"
Macro Settings - check-mark the "Trust Access to the VBA project
object model" (whatever that is?)
Message Bar - select the "Show the Message Bar in all applications
when content has been blocked"

And still no luck.
For one colleague, I tried to debug the problem on his PC.
I opened up my VBA userfrom and tried to execute it manually.... and
got the following error message dialog...
"The macros in this project are disabled. Please refer to the online
help or documentation of the host application to determine how to
enable macros."

But they are enabled already in his Trust Center?

What could be going on here?
Is there some Windows 7 Security that is over-ridding the Excel
setting?
It's strange that it works for some, but not others?

Thanks for the help.
 
Hi
Have you set Security to Medium or low? This is required for anything
that is not a signed macro. After setting it, close and re-open Excel.
regards
Paul
 
Hi
Have you setSecurityto Medium or low? This is required for anything
that is not a signedmacro. After setting it, close and re-open Excel.
regards
Paul
Click to expand...

In Excel 2007 Trust Center, there is no Low or Medium... unless I'm in
the wrong settings area?
The options are...
Disable All Macros without notification.
Disable All Macros with notification.
Disable All Macros except digitally signed macros.
Enable All Macros.

We have set it to Enable All Macros. I assume that is equivalent to
Low.
And we have restarted Excel...
And still have the problem.
 
Sorry, I was thinking about the previous Excel version.
You might still have to close and re-open Excel after changing the
settings. I know little about the trust Settings, but might the
Trusted Locations come into play?
Not much help!
Paul
 
Brian presented the following explanation :
VBA newbie here.... well, at least a few months into it... and have
learned many things from this group... Thank you.
Anyway....

I've developed an Excel 2007 (Windows 7) sheet with a VBA front end
for data entry.
In order for this to run automatically (other colleagues use it)...
I'm using the Userform.Show command in the WorkBook_Open sub.
Looks like this...
Private Sub Workbook_Open()
frmMachVisCalculator.Show
End Sub

I've also password protected the entire workbook, VBA code, hidden
sheets, etc to "secure" it as much as possible from erroneous edits.
When I open the sheet (on my laptop)... the macro runs automatically,
as expected.
For others (other PCs)... it runs fine, but for some the Userform
never comes up.... and never displays any error message or anything??

I've instructed them to check these settings in their Trust Center....
Macro Settings - check-mark the "Enable All Macros"
Macro Settings - check-mark the "Trust Access to the VBA project
object model" (whatever that is?)
Message Bar - select the "Show the Message Bar in all applications
when content has been blocked"

And still no luck.
For one colleague, I tried to debug the problem on his PC.
I opened up my VBA userfrom and tried to execute it manually.... and
got the following error message dialog...
"The macros in this project are disabled. Please refer to the online
help or documentation of the host application to determine how to
enable macros."

But they are enabled already in his Trust Center?

What could be going on here?
Is there some Windows 7 Security that is over-ridding the Excel
setting?
It's strange that it works for some, but not others?

Thanks for the help.
Click to expand...

Are you getting the macro security bar popping up to allow you to
choose options?
 
Forgot to ask also...

Can you scrap using the Workbook_Open event and use Sub Auto_Open() in
a standard module instead?
 
Forgot to ask also...

Can you scrap using the Workbook_Open event and use Sub Auto_Open() in
a standard module instead?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Garry,
No... we are not getting the Macro Security bar pop up.
We only get the "The macros in this project are disabled...." message
dialog when I try to run it manually.

I can try the Auto_Open...
How would I implement that?
What do you mean... "in a standard module"?
What is the syntax and where do I put the code... under the workbook
or under the userform?

Thanks,
Brian.
 
Brian,

1. Right-click your project in the VBE and choose Insert>Module.

2. In the module:

Sub Auto_Open()
frmMachVisCalculator.Show
End Sub

3. Delete the Workbook_Open event under ThisWorkbook.
 
Hi Brian

In the VBE Window in the left hand pane, you will see ThisWorkbook.

On the Toolbar/Ribbon, goto Insert|Module.

Module1 will appear just under ThisWorkbook, doubleclick it and the right
pane will open up allowing you to enter Functions and Sub Routines.

Using something like the following should help open your workbook, modify to
suit.

Sub Export_PODs()

Dim myWB As Workbook

With Application
.ScreenUpdating = False
End With

On Error Resume Next
Set myWB = Workbooks("myWB.xls")
On Error GoTo 0
If myWB Is Nothing Then
Set myWB = Workbooks.Open("C:\YourFolder\myWB.xls")
End If

Windows("myWB.xls").Activate

With Application
.ScreenUpdating = True
End With

Garry will more than likely jump in, hopefully adding some very helpful code
to disable the Macro Security.

HTH
Mick
 
Hi Brian

In the VBE Window in the left hand pane, you will see ThisWorkbook.

On the Toolbar/Ribbon, goto Insert|Module.

Module1 will appear just under ThisWorkbook, doubleclick it and the right
pane will open up allowing you to enter Functions and Sub Routines.

Using something like the following should help open your workbook, modifyto
suit.

Sub Export_PODs()

    Dim myWB As Workbook

    With Application
        .ScreenUpdating = False
    End With

    On Error Resume Next
    Set myWB = Workbooks("myWB.xls")
    On Error GoTo 0
    If myWB Is Nothing Then
    Set myWB = Workbooks.Open("C:\YourFolder\myWB.xls")
    End If

    Windows("myWB.xls").Activate

    With Application
        .ScreenUpdating = True
    End With

Garry will more than likely jump in, hopefully adding some very helpful code
to disable the Macro Security.

HTH
Mick
Click to expand...

Thanks for all the help.
I tried Garry's Auto_Open code... and that's seems to work well.
I'll try the Mick's code, if needed.
Thanks,
Brian.
 
Back
Top