Consulting

Results 1 to 10 of 10

Thread: Solved: Remove Excel Userform Using VBA

  1. #1
    VBAX Regular
    Joined
    Aug 2008
    Posts
    6
    Location

    Solved: Remove Excel Userform Using VBA

    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!

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    What code do you have already?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    In a normal module, to be run when the userform is not loaded:[VBA]With ThisWorkbook.VBProject
    .VBComponents.Remove .VBComponents("UserForm1")
    End With[/VBA]

  4. #4
    VBAX Regular
    Joined
    Aug 2008
    Posts
    6
    Location

    Excel - Removing Userform with VBA

    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!!!!!

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    The code works fine, so perhaps syou are running it in the wrong place. Where are you using that code?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    How high is your security set?

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Also, do you have the Trust Access to Visual Basic Project in Tools>Macro>Security...>Trusted Publishers?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Aug 2008
    Posts
    6
    Location

    Remove Excel Userform Using VBA

    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!

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.

    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Regular
    Joined
    Aug 2008
    Posts
    6
    Location

    Remove Excel Userform Using VBA

    Thanks xld! I'll give this a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •