Opening a User Form from Custom Ribbon Button Run-time and Complie issues

[Event2020]

Excel 2019

I have user form "frmMenu" that has a number of command buttons. Each command button runs a Sub that performs actions on a certain worksheet.
This all works fine but I am having a problem when it comes to opening and closing the userfrom.

I have created a custom group on the ribbon menu and I want a sub that is run when the appropiate button on the Ribbon is clicked.

What I am trying to achieve with the code is the following logic.
If, when the button is clicked, the user form is not open, then open it, but if the user form is open then close it. Really simple right?

This sub is in its own module called "UserFrm_Menu"

In case it is related, I have a UserForm_Initialize sub that I am using to set some common elements of the command buttons which I have
put at the bottom of this post.

This sub is in the correct place (I think): Right Click on the user form > View Code > Left hand drop down = UserForm & Right hand drop down = Initialize'

Below are the three differnt methods that appear the most in searches that I have tried so far but each cause a error.'

Hopefully someone can tell me where I am going wrong.

Thanks

Compile error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic.
derbug hightlights

.Visable

Sub frmMediaInfoMenuOpen()
    If frmMenu.Visible = True Then
        frmMenu.Visible = False
    Else
        frmMenu.Visible = True
    End If
End Sub

This causes Run-time error '438: Object doesen't support this property or method
Debug highlights

If frmMenu.Visible = True Then

Sub frmMediaInfoMenuOpen()
    If frmMenu.Visible = True Then
        frmMenu.Hide
    Else
        frmMenu.Show
    End If

End Sub

This also causes Run-time error '438: Object doesen't support this property or method but there is not the option to debug.

Sub OpenOrCloseUserForm()
    On Error Resume Next
    If UserForms("frmMenu").Visible = True Then
        Unload Data
    Else
        Load Data
        Data.Show
    End If
End Sub

This is my UserForm Initialize sub.

Private Sub UserForm_Initialize()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            With ctrl
                .Height = 24
                .Width = 282
                .Left = 6
                .Font.Name = "Arial"
                .Font.FontStyle = "Regular"
                .Font.Size = 20
            End With
        End If
    Next ctrl
End Sub

 

[Micron]

AFAIK, you cannot run subs from ribbon controls, custom context menus or formula bar (when creating a call in a cell). Change whichever sub your ribbon button calls to a function to start with.

This sub is in its own module

Also declare the function as Public

 

[Event2020]

Hey Micron.

You can, you have to Right Click on the Ribbon, > Customize the Ribbon > add a custom Tab, > add a custom group.
The click on the custom group you just created and then from the Left Hand menu drop down list select Macros, then all of your macros are listed.
Next, click on a macro of your choice, then click the Add>> button and you macro will be added to your custom group.

At this point the macro name is shown on your custom ribbon so to change that, on the Maco you just added on the right hand side, Select rename,
enter what ever text you wish to appear under the button, if you wish you can also choose a Symbol, then select OK, and OK again and your Macro
will now be assigned to a button on your custom Tab on the Ribbon.

I am not sure if this is what you mean, but you can create a user form and add Command Buttons to it. In the buttons click event you simply use "Call "sub name".

I have also just found that the issue was the UserForm_Initialize sub.
If I clear that to be an empty sub the button on the ribbon works with out an issue.

 

[Micron]

You are correct. Sorry, that was my MS Access brain butting in.
OK, first posted code issue is that you mis-spelled visible.
As for the rest, I did some quick research and found that simply by referring to a userform, you load it. Unless its load event hides it, then it will be visible by default. If you want it to be closed and you refer to it, you end up loading it. You might want to consider that behaviour. If it is enough to know that it is loaded, then a solution is to set a variable in a standard module in the declarations section (at the very top, before any subs/functions.

That being said, this works for me (button is on "userform") whether or not Userform1 is loaded.
If UserForm1.Visible Then UserForm1.Hide
However note the printouts for this code snippet

Debug.Print UserForms.count
If UserForm1.Visible Then UserForm1.Hide
Debug.Print UserForms.count

printouts:
1
2
First is because only userform was open. Button was clicked, then userform1 loaded and became hidden.
This may be part of the reason for those code error messages.