We help IT Professionals succeed at work.

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

353 Views
Last Modified: 2017-04-20
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
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
replace this
Sub CallOpenCalculator(control As IRibbonControl)

by this
Public Sub CallOpenCalculator(control As IRibbonControl)

gowflow

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
well better attached your workbook to see what is happening
gowflow
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thank you!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions