How to use the Userform Global Variable value in different Workbook using VBA

Question

I have maintained two excel workbooks in different folders, in the first excel i have created an Userform which has two comboboxes in it, i have declared an global variable "EpcName" in Userform and this global variable stores the second combobox selected value. Now my question is how can i use the value of Global variable from Userform to other workbook which is there in different folder.

Workbook 1 path(Where i have created userform) C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Start Screen -UI\UI.xlsm

Workbook 2 path C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm

Here is the code which i have written in the Userform

 Public EpcName As String
 Private Sub CommandButton1_Click()

 Dim wb As Workbook
 Dim ws As Worksheet


 Set wb = Workbooks("UI.xlsm")
 Set ws = wb.Worksheets("Sheet2")

  Dim qwerty As String
 Dim cf As Range



 EpcName = Me.ComboBox2.Text  'Want to pass this String to other workbook

 With ws
.Activate
With .Range("C1:C10000")

  Set cf = .Find(what:=EpcName, _
    lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If cf = "" Then
 MsgBox "nothing"
 Else
 Range(cf.Address).Select
 Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

 End If

    End With

    End With

 End Sub

Any help is appreciated.

Are the files open at the same time? The userform stays open when you want to use the stored value? If you close the userform, the value disappears from its global variable. If you close the excel file, all values disappear from all variables. — vacip
Also, you say worksheets, but I'm pretty sure you mean workbooks. If yes, please edit the title and the question. — vacip
Yes its a "Workbook" sorry changed it. At first Userform will be Opened form there i am opening the second workbook. — Karthik P B
Well, as long as your userform is open, you can reference the global variable. But you can also reference any field also, so no real need for a global variable. If you post your code, it will be easier to help. — vacip
I have added the code which i have written inside the Userform, kindly check it out — Karthik P B

Daniel Dušek Daniel Dušek · Accepted Answer · 2015-12-10T09:08:57

It should be possible. Declare and initialize a variable in the workbook which contains the User Form, e.g. like this.

Option Explicit

Public EpcName As String
Private m_theOtherWorkbook As Workbook

Private Sub UserForm_Initialize()
    On Error GoTo ErrInit

    Set m_theOtherWorkbook = Workbooks("Power Plant EPC 1.xlsm")

    Exit Sub

ErrInit:
    MsgBox "Error in " & Me.Name & ". UserForm_Initialize. " & Err.Description, vbCritical, "Error"
End Sub

Then in the other workbook, class module ThisWorkbook, add e.g. a public property like this (could be just a public variable).

Option Explicit

Private m_epcName As String

Public Property Get EpcName() As String
    EpcName = m_epcName
End Property

Public Property Let EpcName(ByVal vNewValue As String)
    m_epcName = vNewValue
End Property

And in the code of User form simply call the EpcName property of the other workbook (the other workbook must be opened of course). The property EpcName is now part of a new interface of the other workbook and it should work just fine. HTH

 EpcName = "Want to pass this String to other workbook" ' Me.ComboBox2.Text

 If Not m_theOtherWorkbook Is Nothing Then _
    m_theOtherWorkbook.EpcName = EpcName

How to use the Userform Global Variable value in different Workbook using VBA

1 Answers