1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel CustomUI Ribbon - Retrieve variable value to use in another Sub()

Discussion in 'Ask an Excel Question' started by motabrasil, Aug 11, 2017.

  1. motabrasil

    motabrasil New Member

    Hi There
    I have a macro that use a CustomUI Ribbon with dynamic Dropdown menu (see described below).
    Based on selection, the user should click in a button and run another macro that would open another workbook.

    The value from the dropdown is captured by "returnedVal1" but when I try to use it from Sub Details (), I failed. Value is blank ("")
    Could you please help me and provide the right directions to fix it?
    Another issue is: Is there a way to refresh my CustomUI Ribbon automatically every time I open the file?

    Hope to be hearing from you soon
    Thanks and regards

    Code (vb):

    'Macro 1)
    Private Sub itemCount(control As IRibbonControl, ByRef returnedVal1)
    Call DropDown1
    returnedVal1 = iItemcount5
    End Sub

    'Macro 2)
    Private Sub getItemLabel3(control As IRibbonControl, index As Integer, ByRef returnedVal1)
    returnedVal1 = vRngValues(index)
    End Sub

    'Macro 3)
    Private Sub Details(control As IRibbonControl)
    Dim wsSheet As Worksheet
    Dim sFile$

    Set ws1 = ActiveWorkbook.Sheets("Summary")

    On Error Resume Next

    If returnedVal1 = "File1_Name" Then
    sFile = ActiveWorkbook.Path & "\" & "File1_Name.xls*"
    If Dir(sFile) = Empty Then
    MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
    Exit Sub
    Set wbFile1_Name = Application.Workbooks.Open(ActiveWorkbook.Path & "\" & Dir(sFile))
    End If
    ElseIf returnedVal1 = "File2_Name" Then
    sFile = ActiveWorkbook.Path & "\" & "File2_Name.xls*"
    If Dir(sFile) = Empty Then
    MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
    Exit Sub
    Set wbFile2_Name = Application.Workbooks.Open(ActiveWorkbook.Path & "\" & Dir(sFile))
    End If
    Elseif ...

    Exit Sub
    Last edited by a moderator: Aug 12, 2017
  2. Hui

    Hui Excel Ninja Staff Member


    Please note that it has been detected that this post is Cross-Posted on other Excel Forums

    This is considered poor practice when you haven't notified us of the fact.

    This is because you may well have received an answer somewhere and yet people here are still trying to solve the problem, where they could be assisting others.

    Please ensure that you read the sites rules at:
  3. motabrasil

    motabrasil New Member

    Thank you Hui for your feedback
    I really apologize for my cross-post. I missed this rule.
    Kind regards

Share This Page