Excel Hints

Results 1 to 10 of 10

Thread: Solved: Remove Excel Userform Using VBA

  1. #1

    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
    3,227
    Location
    What code do you have already?
    Remember To Do the Following....
    Use 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
    In a normal module, to be run when the userform is not loaded:
    VB:
    With ThisWorkbook.VBProject 
        .VBComponents.Remove .VBComponents("UserForm1") 
    End With 
    
    
    Formatting tags added by mark007

  4. #4

    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,022
    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
    How high is your security set?

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,022
    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

    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,022
    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.

    VB:
     
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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

    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
  •