Solved: Remove Excel Userform Using VBA

[Cora419]

I'm using Excel 2003. I have a userform that loads when the file is opened and allows the user to select from among 10 pictures to insert into the Excel document. The Excel doc is a nomination form that is automatically emailed when a command button is clicked.

The problem is that the file is too large with the pics displayed on the userform. I want to be able to either remove the userform before the email code is run or just remove all 10 pics from the userform. I'm not sure how to do either. Any help would be greatly appreciated.
Thanks!

[Aussiebear]

What code do you have already?

[mikerickson]

In a normal module, to be run when the userform is not loaded:With ThisWorkbook.VBProject
.VBComponents.Remove .VBComponents("UserForm1")
End With

[Cora419]

The code I was trying was very similar to what Mikerickson shows, just without the With/End With. When I try the code mentioned here, I get the follwoing error:

Runtime Error 1004
Programmatic access to Visual Basic is not trusted

Is there a setting I need to change to make this work? I would try to remove the pictures from the userform, but I'm sure I'd get the same error. I'd much rather just remove the whole userform.

Any ideas? Thanks!!!!!

[Bob Phillips]

The code works fine, so perhaps syou are running it in the wrong place. Where are you using that code?

[mikerickson]

How high is your security set?

[Bob Phillips]

Also, do you have the Trust Access to Visual Basic Project in Tools>Macro>Security...>Trusted Publishers?

[Cora419]

Perfect! Thanks, xld. That did the trick. I did not have "Trust access to Visual Basic Project" checked. Once I did that, the userform is deleted. One question -- does this change apply to the document? I want to make sure other users will not run into the same issue.

And thanks, mikerickson! Your code is exactly what I needed to make this work. Appreciate the help!

[Bob Phillips]

It is an application setting, so you will need to make sure that theirs is set too.

You can check it with this code, and tell the users what to do if it is not.



Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function

[Cora419]

Thanks xld! I'll give this a try.