Search code examples
excelvbacombobox

Set a variable name to a Workbook selected from a VBA ComboBox

I will always have Workbook SQ_Macro_v1 as my main DB.

Two named Workbooks old_wk and new_wk will have different names, as I will choose them among the currently active WB on my computer.

I am going for a VBA ComboBox listing it all to a choice of mine, but in the end I am not able to store the name of my chosen WB.

Sub Macro1()

    Dim main_wk, old_wk, new_wk As Workbook

    Set main_wk = Workbooks("SQ_Macro_v1.xlsm")
    Set old_wk = Workbooks(old_chosen) 'also tried UserForm1.ComboBox1.Value
    Set new_wk = Workbooks(FileName_New) '
    
    main_wk.Sheets("Main_DB").Range("C4").Value = old_wk.Worksheets("Sheet 1 Synthese").Range("C35").Value

As I run the UserForm code below, the old_chosen variable I set as empty in the main Sub. It seems that as I close the UserForm after it runs, nothing remains stored. Any clues to keep that variable saved after I close the UserForm?

Option Explicit

Public Sub UserForm_Activate()
    Dim vWorkbook As Workbook
    ComboBox1.Clear
    For Each vWorkbook In Workbooks
        ComboBox1.AddItem vWorkbook.Name
    Next
End Sub

Public Sub CommandButton1_Click()
    If ComboBox1.ListIndex <> -1 Then
        Call YourMacro(ComboBox1)
    End If
End Sub

Public Sub YourMacro(vWorkbookName As String)

    Dim old_chosen As String
    old_chosen = Me.ComboBox1.Value
    MsgBox "You choose: " & Workbooks(vWorkbookName).Name
        
End Sub

The MsgBox pops up but no value is stored afterward:

Print stating the empty variable

Solution 
  • Public Sub YourMacro(vWorkbookName As String)
    Dim old_chosen As String
    old_chosen = Me.ComboBox1.Value
    MsgBox "You choose: " & Workbooks(vWorkbookName).Name
End Sub

    You have declared the variable at procedure level and hence it is not visible after the userform is closed.

    To make this variable available to all procedures in the project, precede it with the Public statement. Insert a module and paste this there

    Public old_chosen As String

    Having said that, I would recommend moving Macro1 inside the userform and handle the code from there after declaring the variable at module level