Close/Open Userforms on Mouse Click

  • Hello,


    I have a menu structure in place so when a user clicks a button, it opens a Userform as a menu. The only problem i have is that if the user doesnt want to use that menu, and clicks on the spreadsheet, it doesnt dissapear.


    Is there any VBA code that anyone knows that when a user clicks on the spreadsheet, or even just clicks their mouse anywhere (ie other menus at the top, spreadsheet, desktop) like a mouseout code on HTML so the menu will dissapear?


    Thanks in advance and thanks to everyone who has helped in previous posts.


    Rich

  • Re: Close open Userforms on mouseclick


    A UserForm has a click event, in addition to the usual X to close

    Code
    Option Explicit
    
    
    Private Sub UserForm_Click()
    Unload Me
    End Sub
  • Re: Close/Open Userforms on Mouse Click


    Because that uses menus at the top, ie a file, edit, view, favourites.


    My users have a row of buttons at the top of the excel sheet, such as IDLE - CFT, IDLE - Non CFT, Approaches.


    When the user clicks this, i was wanting a menu to appear, i think the easiest way is to open a userform underneath which I can do by using this method:
    C Pearsons way


    But if the user clicks off the userform, im trying to get it to close.

  • Re: Close/Open Userforms on Mouse Click


    Add three buttons from the control toolbox to the worksheet.


    CB1 caption = TestMe
    CB2 caption = Delete popup
    CB3 caption = create popup


    Worksheet module
    [vba]Private Sub CommandButton1_Click()


    On Error Resume Next
    myBar.ShowPopup


    End Sub
    Private Sub CommandButton2_Click()

    On Error Resume Next
    CommandBars("CustomPopup").Delete

    End Sub
    Private Sub CommandButton3_Click()

    MakeIt

    End Sub[/vba]


    Module code
    [vba]Public myBar As CommandBar


    Sub MakeIt()


    On Error Resume Next
    Set myBar = CommandBars("CustomPopup")
    If myBar Is Nothing Then
    Set myBar = CommandBars _
    .Add(Name:="CustomPopup", Position:=msoBarPopup, Temporary:=False)
    With myBar
    .Controls.Add(Type:=msoControlButton).Caption = "One"
    .Controls.Add(Type:=msoControlButton).Caption = "Two"
    .Controls.Add(Type:=msoControlButton).Caption = "Three"
    .Controls.Add(Type:=msoControlButton).Caption = "Four"
    .Controls.Add(Type:=msoControlButton).Caption = "Five"
    End With
    End If

    End Sub[/vba]


    The menu items themselves currently have no action assigned.

  • Re: Close/Open Userforms on Mouse Click


    the added controls have an OnAction property.


    Try searching the forum for Commandbar post, there have been plenty of questions over the years on adding custom commandbars.

  • Re: Close/Open Userforms on Mouse Click


    hi Andy,


    sorry to be a pain, iv e searched the forums for the .onaction code and tried to use it, but i get an error, just wondered if you could help.


    The code is:


    And i get the message:


    Compile Error: Method or data member not found.

  • Re: Close/Open Userforms on Mouse Click


    You need to use a WITH statement to assign the properties to the correct control
    [vba]Sub MakeIt()

    On Error Resume Next
    Set myBar = CommandBars("CustomPopup")
    If myBar Is Nothing Then
    Set myBar = CommandBars _
    .Add(Name:="CustomPopup", Position:=msoBarPopup, Temporary:=False)
    With myBar
    with .Controls.Add(Type:=msoControlButton)
    .Caption = "IDLE - CFT"
    .OnAction = "idle_cft"
    .BeginGroup = False
    End with
    .Controls.Add(Type:=msoControlButton).Caption = "IDLE - Non CFT"
    .Controls.Add(Type:=msoControlButton).Caption = "OPI"
    .Controls.Add(Type:=msoControlButton).Caption = "Four"
    .Controls.Add(Type:=msoControlButton).Caption = "Five"
    End With
    End If

    End Sub [/vba]

  • Re: Close/Open Userforms on Mouse Click


    There are links on the page I linked to that would have shown you this, or simliar. For others that locate this Thread see also;


    Delete/Restore Excel Toolbars || Adding a Command Button to the Excel Right Click Menu

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!