Show VBA dialog from Ribbon

<?xml encoding=”UTF-8″>By Adam Nagy

Anything that shows in the Macros dialog can also be hooked up to a new Ribbon button: http://adndevblog.typepad.com/manufacturing/2015/01/userform-function-not-listed-in-macros-dialog.html

There are two types of functions:
– Sub: that does not return a value
– Function: that returns a value and so its declaration
                   ends with an “As <object type>”, e.g.:

Public Function GetMeAString() <strong>As String</strong>
GetMeAString = "Some string"
End Function

In the Macros dialog only Sub‘s will show up as running a macro does not require a result. The Customize dialog also only shows Sub‘s, but also only the ones from the Default VBA project – the one set here:

Inside the Customize dialog you can narrow down the search to Macros to make it easier to find and then place it on any of the Ribbon tabs: 

In order for our form to pop up when the button on the Ribbon is clicked we have to set things up the same way as it was done in the other blog post:

Public myForm As UserForm1
Public Sub MySub()
Set myForm = New UserForm1
myForm.Show
End Sub

The way you can edit your form should be the same in all VBA environments, like the one in Excel, so you can find other resources on that on the web, e.g. https://msdn.microsoft.com/en-us/library/office/aa192538(v=office.11).aspx