How to use a Variable in different a Userform/module

B

bcmk29

Board Regular
Joined
Oct 20, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I've got 2 different UserForms (UserForm & DataMap) with multiple modules in them.

1. Here's the code of my first Form UserForm. Where I'm allowing the user to select a spreadsheet and assign the file name to Importworkbook.

VBA Code: 
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless

    Dim LR, IB As Long
    Dim FileLocation As String
   
    Sheets("Input").Select
    Range("A1").Select
    Sheets("Sheet3").Range("R1").Value = 0
    file = ActiveWorkbook.Name
    LR = Cells(Rows.Count, 1).End(xlUp).Row
   
    If LR = 1 Then Else GoTo ok
   
        [B]FileLocation = Application.GetOpenFilename("(*.xlsx),")
       
        If FileLocation = "False" Then
            MsgBox "No file selected to import.", 48
            Unload Me
        Exit Sub
        End If
    End If
   
    Set Importworkbook = Workbooks.Open(Filename:=FileLocation)[/B]
   
    If Importworkbook.Sheets.Count > 1 Then
reIB:
        IB = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
            If IB > Sheets.Count Then
                MsgBox "Invalid Sheet Input, Try Again.", 48, "Entry Required"
                GoTo reIB
            Else
                Sheets(IB).Select
            End If
            GoTo hi
    Else: End If
hi:
    ActiveSheet.Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Copy
    Workbooks(file).Activate
    Sheets("Sheet3").Select
    Range("P2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Application.CutCopyMode = False
    Range("P1").Select
ok:
End Sub

2. Here's the code for my second Form DataMap, where I'm replicating the same set of codes from above. Instead, I just need to get the name of Importworkbook from my earlier code which is assigned in different userform/module. Please assist.

VBA Code: 
Sub DataImport()
    Dim LR As Long
    Dim FileLocation As String
    Dim A, A1 As Integer
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    If LR = 1 Then Else GoTo ok

[B]    FileLocation = Application.GetOpenFilename("(*.xlsx),")
    If FileLocation = "False" Then
        MsgBox "No file selected to import.", 48
        Exit Sub
    End If
    Set Importworkbook = Workbooks.Open(Filename:=FileLocation)[/B]

    ThisWorkbook.Worksheets(2).Activate
    SH = ThisWorkbook.Worksheets(4).Cells(Rows.Count, 19).End(xlUp).Row
For A = 2 To SH

'MsgBox DataMap!mastrImportworkbook
    Importworkbook.Worksheets(1).Range(Sheets("Sheet3").Range("T" & A).Value).Copy ThisWorkbook.Worksheets(2).Cells(1, A - 1)
Next A
    Application.ScreenUpdating = True
    ThisWorkbook.Worksheets(3).Range("A1:u1").Copy
    ThisWorkbook.Worksheets(2).Range("A1:u1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Importworkbook.Close
    Application.ScreenUpdating = True

    ThisWorkbook.Worksheets(4).Select
    Range("P2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("R1").Select
    Selection.ClearContents
    ThisWorkbook.Worksheets(2).Select
    Range("A1").Select
ok:
   UserForm1.Show
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Try
Rich (BB code): 
Dim FileLocation As String
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless

    Dim LR, IB As Long
   
    Sheets("Input").Select
    Range("A1").Select

Rich (BB code): 
Sub DataImport()
    Dim LR As Long
 '  Dim FileLocation As String
    Dim A, A1 As Integer
 
Upvote 0
mohadin said:
Try
Rich (BB code): 
Dim FileLocation As String
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless

    Dim LR, IB As Long
  
    Sheets("Input").Select
    Range("A1").Select

Rich (BB code): 
Sub DataImport()
    Dim LR As Long
 '  Dim FileLocation As String
    Dim A, A1 As Integer
Click to expand...
Did you just share half the code? I'm not seeing any change here.
 
Upvote 0
VBA Code: 
Dim FileLocation As String '<<<<< moved up
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless

    Dim LR, IB As Long
  
    Sheets("Input").Select
    Range("A1").Select



VBA Code: 
Sub DataImport()
    Dim LR As Long
 '  Dim FileLocation As String  <<<<< deleted or neutralized
    Dim A, A1 As Integer
Unless you have ,ore problem with your code
 
Upvote 0
Hi mohadin,

AFAIR using Dim on top only makes the variabe visible in a singe module/UserForm. As OP is talking about two UserForms I would have expected to make it a global variable in a standard module like

VBA Code: 
'Standard Module
Public FileLocation As String

Ciao,
Holger
 
Upvote 0
Hi bcmk29,

maybe this may point in a way to go:

VBA Code: 
'Standard Module
Public wbkImport As Workbook

VBA Code: 
'Code for UserForm
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless
  
  Dim lngIndexWS As Long
  Dim varFile As Variant
  Dim wbActive As Workbook
  
'  Sheets("Input").Range("A1").Select
  Sheets("Sheet3").Range("R1").Value = 0
  Set wbActive = ActiveWorkbook
  
  If Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then GoTo end_here
    
  If wbkImport Is Nothing Then
    varFile = Application.GetOpenFilename("(*.xlsx),", MultiSelect:=False)
    If varFile = "False" Then
      MsgBox "No file selected to import.", 48
      GoTo end_here
    End If
    Set wbkImport = Workbooks.Open(Filename:=FileLocation)
  End If
  
  If wbkImport.Sheets.Count > 1 Then
reIB:
    lngIndexWS = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
    If lngIndexWS > Sheets.Count Then
      MsgBox "Invalid Sheet Input, Try Again.", 48, "Entry Required"
      GoTo reIB
    Else
      Sheets(lngIndexWS).Select
    End If
  End If

  ActiveSheet.Range("A1").Resize(1, Cells(1, Columns.Count).End(xlToLeft).Column).Copy
  Application.Goto wbActive.Sheets("Sheet3").Range("P2")
  ActiveSheet.PasteSpecial Paste:=xlPasteValues, _
                            Operation:=xlNone, _
                            SkipBlanks:=False, _
                            Transpose:=True
  Application.CutCopyMode = False
  Range("P1").Select
  
end_here:
  Set wbActive = Nothing
End Sub

VBA Code: 
'Part of code for DataMap
Sub DataImport()
  Dim varFile As Variant
  Dim A As Integer
  Dim SH As Long
  
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Else GoTo ok

  If wbkImport Is Nothing Then
    varFile = Application.GetOpenFilename("(*.xlsx),", MultiSelect:=False)
    If varFile = "False" Then
        MsgBox "No file selected to import.", 48
        Exit Sub
    End If
    Set wbkImport = Workbooks.Open(Filename:=FileLocation)
  End If
  
'...
'Rest of the code to follow



Ciao,
Holger
 
Upvote 0
Hi bcmk29,

I tried to work on the code for the second UF but I ran into problems understanding what you want to be done. I noticed that I had not replaced FileLocation with varFile in my above codes. Code is untested, maybe you can elaborate on the questions:

VBA Code: 
Sub DataImport()
  Dim varFile As Variant
  Dim lngImport As Long
  
  If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Else GoTo ok

  If wbkImport Is Nothing Then
    varFile = Application.GetOpenFilename("(*.xlsx),", MultiSelect:=False)
    If varFile = "False" Then
      MsgBox "No file selected to import.", 48
      Exit Sub
    End If
    Set wbkImport = Workbooks.Open(Filename:=varFile)
  End If
  
'  ThisWorkbook.Worksheets(2).Activate

  For lngImport = 2 To ThisWorkbook.Worksheets(4).Cells(Rows.Count, 19).End(xlUp).Row

    'MsgBox DataMap!mastrImportworkbook
    '/// not clear to my why the range is set up this way, as you are in ThsWorkbbo so Sheets("Sheet3").Range("T" & lngImport).Value shoud point to
    '/// ThisWorkbook. The range indicated should hold a valid Range-Address. If so, you could avoid the loop and use copy/paste transpose instead for
    '/// doing this process in one go.
    wbkImport.Worksheets(1).Range(Sheets("Sheet3").Range("T" & lngImport).Value).Copy ThisWorkbook.Worksheets(2).Cells(1, lngImport - 1)
  Next lngImport
  Application.ScreenUpdating = True
  '/// After you have imported data into the cells you copy the values from Sheet3 to Sheet2 - what am I getting wrong here?
  '/// Range expects the Column Letter first and then the Row Number while Cells expects the Row Number first followed by either Column Letter or Column Number
  With ThisWorkbook
    .Worksheets(3).Range("A1:u1").Copy
    With .Worksheets(2).Range("A1").Resize(1, 21)
      .PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
      .PasteSpecial Paste:=xlPasteFormats, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
    End With
  End With
  Application.CutCopyMode = False
  wbkImport.Close SaveChanges:=False
  Application.ScreenUpdating = True

  With ThisWorkbook.Worksheets(4)
    .Range("P2").Resize(.Cells(.Rows.Count, "P").End(xlUp).Row - 1, 1).ClearContents
    .Range("S2").Resize(.Cells(.Rows.Count, "S").End(xlUp).Row - 1, 1).ClearContents
    .Range("R1").ClearContents
  End With
  Application.Goto ThisWorkbook.Worksheets(2).Range("A1"), Scroll:=True
ok:
   UserForm1.Show
End Sub

Ciao,
Holger
 
Upvote 0
HaHoBe said:
Hi bcmk29,

maybe this may point in a way to go:

VBA Code: 
'Standard Module
Public wbkImport As Workbook

VBA Code: 
'Code for UserForm
Private Sub UserForm_Initialize()
'DataMap.Show vbModeless
 
  Dim lngIndexWS As Long
  Dim varFile As Variant
  Dim wbActive As Workbook
 
'  Sheets("Input").Range("A1").Select
  Sheets("Sheet3").Range("R1").Value = 0
  Set wbActive = ActiveWorkbook
 
  If Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then GoTo end_here
   
  If wbkImport Is Nothing Then
    varFile = Application.GetOpenFilename("(*.xlsx),", MultiSelect:=False)
    If varFile = "False" Then
      MsgBox "No file selected to import.", 48
      GoTo end_here
    End If
    Set wbkImport = Workbooks.Open(Filename:=FileLocation)
  End If
 
  If wbkImport.Sheets.Count > 1 Then
reIB:
    lngIndexWS = Application.InputBox("Enter worksheet number", "Worksheet selection", , , , , , 1)
    If lngIndexWS > Sheets.Count Then
      MsgBox "Invalid Sheet Input, Try Again.", 48, "Entry Required"
      GoTo reIB
    Else
      Sheets(lngIndexWS).Select
    End If
  End If

  ActiveSheet.Range("A1").Resize(1, Cells(1, Columns.Count).End(xlToLeft).Column).Copy
  Application.Goto wbActive.Sheets("Sheet3").Range("P2")
  ActiveSheet.PasteSpecial Paste:=xlPasteValues, _
                            Operation:=xlNone, _
                            SkipBlanks:=False, _
                            Transpose:=True
  Application.CutCopyMode = False
  Range("P1").Select
 
end_here:
  Set wbActive = Nothing
End Sub

VBA Code: 
'Part of code for DataMap
Sub DataImport()
  Dim varFile As Variant
  Dim A As Integer
  Dim SH As Long
 
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Else GoTo ok

  If wbkImport Is Nothing Then
    varFile = Application.GetOpenFilename("(*.xlsx),", MultiSelect:=False)
    If varFile = "False" Then
        MsgBox "No file selected to import.", 48
        Exit Sub
    End If
    Set wbkImport = Workbooks.Open(Filename:=FileLocation)
  End If
 
'...
'Rest of the code to follow



Ciao,
Holger
Click to expand...
Ciao,

Thanks for your suggestion, with the first userform I ended up with the below error for the line of code mentioned below.

VBA Code: 
Set wbkImport = Workbooks.Open(Filename:=FileLocation)

1666528207350.png


I haven't tested the second userform yet as I couldn't go through the above error.
 
Upvote 0
I'm replicating the same set of codes from above. Instead, I just need to get the name of Importworkbook from my earlier code which is assigned in different userform/module.
Click to expand...

Hi,
where you have code that repeats across a project but just need a return value you would make that code a common function.

place in STANDARD module

VBA Code: 
Function GetFile() As Variant
    GetFile = Application.GetOpenFilename("(*.xlsx),")
    If GetFile = False Then
        MsgBox "No file selected to import.", 48, "No Selection"
    End If
End Function

and from your userforms

VBA Code: 
Filelocation = GetFile
    If Filelocation = False Then Exit Sub
    
    'rest of code

Dave
 
Upvote 0
Hi bcmk29,

mybe this is the answer to the error:

I noticed that I had not replaced FileLocation with varFile in my above codes.
Click to expand...

Ciao,
Holger
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Import filename of .GetOpenFilename into specific sheet/cell
Replies
4
Views
310
Koosbie
K
D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
209
Daniza Morla
D
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
104
willow1985
willow1985
B
  • Solved
Referencing Cells For PrToFileName (.PrintOut)
2
Replies
11
Views
564
boyko
B
D
  • Question
VBA optimization advice
Replies
3
Views
95
bobsan42
bobsan42

Forum statistics

Threads
1,214,923
Messages
6,122,283
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top