0

Im opening a Workbook through Workbook.GetOpenFilename and setting it to Test_wkbk = ActiveWorkbook.Name in a subroutine and I want to make Test_wkbk available globally to the other subroutines in other modules as well. How Do i do that?

Community
  • 1
  • 1
Skyblue
  • 35
  • 1
  • 1
  • 10
  • 2
    Possible duplicate of [Declare a Workbook as a Global variable](https://stackoverflow.com/questions/31536519/declare-a-workbook-as-a-global-variable) – aaa Mar 13 '18 at 08:56
  • I tried that out and every time my workbook is being called it goes through the Get Property function. Since im opening the workbook from GetOpenFilename which asks users to choose a workbook from their Pc, my get property function has the GetOpenFilename and it keeps asking user to choose a workbook every time. – Skyblue Mar 13 '18 at 09:08
  • Poste the code you've tried – Foxfire And Burns And Burns Mar 13 '18 at 11:52

1 Answers1

4

Check to see if the private variable has a value. If it does, use it. If it doesn't use GetOpenFileName

Private wLocations As Workbook

Public Property Get Locations() As Workbook

    Dim sFile As String

    If wLocations Is Nothing Then

        sFile = Application.GetOpenFilename("*.xlsx,*.xlsx")

        On Error Resume Next
            Set wLocations = Workbooks(Dir(sFile))
            If wLocations Is Nothing Then
                Set wLocations = Workbooks.Open(sFile)
            End If
        On Error GoTo 0
    End If

    Set Locations = wLocations

End Property
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73