calling VBA forms from a button in Excel

Question

I have a macro that parses through a large file in Excel. I created a form that allows me to search for and provide a summary of specific messages. I can put

FormName.Show

at the end of the macro and it works. The form displays as the parsing macro completes. But I don't always want the form to appear and sometimes I want to call it again after I've saved the spreadsheet. So I wrote another function that can create a button that can open the form. In the .OnAction statement I have OnAction = "FormName.Show"

Sub Create_Button()
ActiveSheet.Buttons.Add(437.25, 72, 125.25, 47.25).Select
Selection.OnAction = "FormName.Show"
Selection.Characters.Text = "Search Messages"
End Sub

This doesn't work, it created the button but when I click on the button I get

"Cannot run the macro "xxxx.xlam'!FormName.Show' The macro may not be available in this workbook or all macros may be disabled.

Why does it work in the main macro but not in the button OnAction?

Thanks

OpiesDad · Accepted Answer · 2016-10-27 17:37:19Z

0

Per MSDN, Shape.OnAction requires a macro name, not VBA code. You have tried to pass it VBA code. Instead try this:

Selection.OnAction = "showForm"
....
End Sub

Public Sub showForm()
     FormName.Show
End Sub

answered Oct 27, 2016 at 17:37

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OpiesDad Over a year ago

No problem. Consider marking one of the answers as the answer if they solved your problem.

user6432984 · Accepted Answer · 2016-10-27 17:40:40Z

0

.OnAction will not only run Macros not it will not evaluate code. Paste thiss in a standard module.

Sub ShowUserform()
    FormName.Show
End Sub

Sub Create_Button()
    With ActiveSheet.Buttons.Add(437.25, 72, 125.25, 47.25)
        .OnAction = "ShowUserform"
        .Characters.Text = "Search Messages"
    End With
End Sub

answered Oct 27, 2016 at 17:40

user6432984

Collectives™ on Stack Overflow

calling VBA forms from a button in Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related