Avatar of mawasw
mawasw
Flag for United States of America asked on

Excel - Open a UserForm from a Custom Ribbon button with ribbonxml

Hello,

Here is my code  

Option Explicit

Private Sub Workbook_Activate()

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String, user As String

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
fileName = "Excel.officeUI"

ribbonXML = "<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
ribbonXML = ribbonXML + "  <mso:ribbon>" & vbNewLine
ribbonXML = ribbonXML + "    <mso:qat/>" & vbNewLine
ribbonXML = ribbonXML + "    <mso:tabs>" & vbNewLine
ribbonXML = ribbonXML + "      <mso:tab id='reportTab' label='WBG' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Total Calculator' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' " & vbNewLine
ribbonXML = ribbonXML + "            imageMso='Calculator'      onAction='CallOpenCalculator'/>" & vbNewLine
ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
ribbonXML = ribbonXML + "      </mso:tab>" & vbNewLine
ribbonXML = ribbonXML + "    </mso:tabs>" & vbNewLine
ribbonXML = ribbonXML + "  </mso:ribbon>" & vbNewLine
ribbonXML = ribbonXML + "</mso:customUI>"

ribbonXML = Replace(ribbonXML, """", "")

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub

Option Explicit


Sub CallOpenCalculator(control As IRibbonControl)

    Call OpenCalc
   
End Sub
Sub OpenCalc()

TotCalc.Show

End Sub


Problem - When I click the custom button on the ribbon, the TotCalc form is not displayed.  Instead, I receive a message stating "....The macro may not be available in this workbook or all macros may be disabled."

Please help.

M. Wilson
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
mawasw

8/22/2022 - Mon
gowflow

replace this
Sub CallOpenCalculator(control As IRibbonControl)

by this
Public Sub CallOpenCalculator(control As IRibbonControl)

gowflow
mawasw

ASKER
Thank you for the feedback.

I now receive a message stating "Argument not optional"

Here is where I call CallOpenCalculator.......imageMso='Calculator'      onAction='CallOpenCalculator'/......

Do I need to pass it something?

Thanks,
M. Wilson
gowflow

well better attached your workbook to see what is happening
gowflow
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
gowflow

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mawasw

ASKER
Thank you!