Close/Open Userforms on Mouse Click

richardcranney · Aug 23rd 2006

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

**royUK** · Aug 23rd 2006

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

Dave Hawley · Aug 23rd 2006

Re: Close open Userforms on mouseclick

Why are you using a UserForm for Menu Items and not adding custom menu items?

richardcranney · Aug 23rd 2006

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.

**Andy Pope** · Aug 23rd 2006

Re: Close/Open Userforms on Mouse Click

You need to look into create a popup (context) menu. Similar in function to the right click.

richardcranney · Aug 23rd 2006

Re: Close/Open Userforms on Mouse Click

Oooo, that sounds interesting. I know how to create extra parts into the right hand menu.
Do you have any more information on this? Thanks

**Andy Pope** · Aug 23rd 2006

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.

richardcranney · Aug 23rd 2006

Re: Close/Open Userforms on Mouse Click

Woah, thats cool, and it works bloody well too. Your a genious Andy!

Any ideas on getting the buttons to run Macros?

Thanks

**Andy Pope** · Aug 23rd 2006

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.

richardcranney · Aug 23rd 2006

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:

Code

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 = "IDLE - CFT"
                .OnAction = "idle_cft"
                .BeginGroup = False
            .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

Display More

And i get the message:

Compile Error: Method or data member not found.

**Andy Pope** · Aug 23rd 2006

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]

Dave Hawley · Aug 24th 2006

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

Quote from richardcranney

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.

Display More

Close/Open Userforms on Mouse Click

Participate now!

Share

Users Viewing This Thread