How to use variables from a user form in a macro

[Rainmanne]

I've got a user form I want to use to determine how to run a macro:

Private Sub UserForm_Initialize()
'Reset the form
radiotempl1.Value = False
radiotempl2.Value = False
datatype.Clear
With datatype
    .AddItem "Public"
    .AddItem "Private"
End With
radiotier1.Value = False
radiotier2.Value = False
radiotier1.Enabled = False
radiotier2.Enabled = False
wipe_format.Value = True
End Sub

It has two radio buttons, Template 1 and Template 2, a combo box with two options, Public and Private, another set of radio buttons, Tier 1 and Tier 2, which only get activated if Private is selected and a checkbox at the end, Wipe Conditional Formatting.

If, for example, Template 1 is selected, I need to import two worksheets from a source file, but if Template 2 selected, I just need only one. The code for Template 1 looks currently as shown below:

Set activeWB = Application.ActiveWorkbook
FileOpenDial = Application.GetOpenFilename(FileFilter:="Excel Files (*.XML), *.XML", Title:="Select File To Be Opened")
If FileOpenDial = False Then Exit Sub
Set wb = Workbooks.Open(FileOpenDial)
    Sheets(Array("Accounts", "Types")).Select
    Sheets(Array("Accounts", "Types")).Copy Before:=activeWB.Sheets(1)
wb.Close savechanges:=False 'or True

I want to have something like Sheets(Array(VARIABLE)).Select, where VARIABLE means "Accounts", "Types" if Template 1 is selected and "Accounts" in case of Template 2.

But further down the code, it should trigger another difference in code. This is for Template 1:

'FillRight Formulas
Set cfyearsno = ThisWorkbook.Sheets("FS").Range("C1:XFD1")
numrowscf = Application.WorksheetFunction.CountA(cfyearsno)
With ThisWorkbook.Sheets("CF")
    Set startcell = .Range("F1")
  
    If numrowscf = 3 Then
        Set endcell = Range("F" & Rows.Count).End(xlUp).Offset(, 1)
        Set finstart = .Range(startcell.Address & ":" & endcell.Address)
        finstart.FillRight
    ElseIf numrowscf > 3 Then
        Set endcell = Range("F" & Rows.Count).End(xlUp).Offset(, 2)
        Set finstart = .Range(startcell.Address & ":" & endcell.Address)
        finstart.FillRight
    Else
    End If
End With

And this is for Template 2:

Set cfyearsno = ThisWorkbook.Sheets("FS").Range("F1:J1")
numrowscf = Application.WorksheetFunction.CountA(cfyearsno)

With ThisWorkbook.Sheets("CF")
    Set startcell = .Range("G1")
    
    If numrowscf = 3 Then
        Set endcell = Range("G150").Offset(, 1)
        Set finstart = .Range(startcell.Address & ":" & endcell.Address)
        finstart.FillRight
    ElseIf numrowscf > 3 Then
        Set endcell = Range("G150").Offset(, 2)
        Set finstart = .Range(startcell.Address & ":" & endcell.Address)
        finstart.FillRight
    Else
    End If
End With

So I would like to use the VARIABLE from the user form to merge it in one piece of code.

Same for the variable from the combobox and other user form controls.

I hope if I can figure out how to do the first two pieces of the code I can dean with the rest myself. Currently, my modest knowledge of VBA is clearly not enough to deal with the first hurdle.

 

[dmt32]

If, for example, Template 1 is selected, I need to import two worksheets from a source file, but if Template 2 selected, I just need only one.

Hi,
try this update to your code & see if does what you want

     Dim arr As Variant
    
    arr = IIf(Me.Template1, Array("Accounts", "Types"), Array("Accounts"))
    
    Set activeWB = Application.ActiveWorkbook
    FileOpenDial = Application.GetOpenFilename(FileFilter:="Excel Files (*.XML), *.XML", Title:="Select File To Be Opened")
    'cancel pressed
    If FileOpenDial = False Then Exit Sub
    
    Set wb = Workbooks.Open(FileOpenDial, 0, True)
    
    wb.Worksheets(arr).Copy Before:=activeWB.Sheets(1)
    wb.Close savechanges:=False 'or True

Dave

 

[Rainmanne]

Hi,
try this update to your code & see if does what you want

     Dim arr As Variant
   
    arr = IIf(Me.Template1, Array("Accounts", "Types"), Array("Accounts"))
   
    Set activeWB = Application.ActiveWorkbook
    FileOpenDial = Application.GetOpenFilename(FileFilter:="Excel Files (*.XML), *.XML", Title:="Select File To Be Opened")
    'cancel pressed
    If FileOpenDial = False Then Exit Sub
   
    Set wb = Workbooks.Open(FileOpenDial, 0, True)
   
    wb.Worksheets(arr).Copy Before:=activeWB.Sheets(1)
    wb.Close savechanges:=False 'or True

Dave

Thanks a lot! It works. I have modified it to make work in a module rather than in the user form code (not sure if it's a right way to do it but it's easier for me to keep the main code in the module)

Dim arr As Variant
    
    arr = IIf(UserForm1.radiotempl1, Array("Accounts", "Types"), Array("Accounts"))
    
    Set activeWB = Application.ActiveWorkbook
    FileOpenDial = Application.GetOpenFilename(FileFilter:="Excel Files (*.XML), *.XML", Title:="Select File To Be Opened")
    'cancel pressed
    If FileOpenDial = False Then Exit Sub
    
    Set wb = Workbooks.Open(FileOpenDial, 0, True)
    
    wb.Worksheets(arr).Copy Before:=activeWB.Sheets(1)
    wb.Close savechanges:=False 'or True

Do you think this approach will work with the second example?

 

[dmt32]

Do you think this approach will work with the second example?

glad suggestion helped
Placing code in standard module perfectly ok although I would pass an instance of the userform using the Me keyword as an argument to the procedure as this negates need for hard coding the userform name.

example

stasndard module

Sub Myprocedure(ByVal Form As Object)

    MsgBox Form.Name
    
End Sub

and to call the procedure from your userform

  Myprocedure Me

for the second part of your question just test the state of the first optionbutton

example

If Me.Template1.Value = True Then

    'do stuff here for template1
Else

    'do stuff here for template2
End If

Hope Helpful

Dave

 

[Rainmanne]

Hi,
try this update to your code & see if does what you want

     Dim arr As Variant
   
    arr = IIf(Me.Template1, Array("Accounts", "Types"), Array("Accounts"))
   
    Set activeWB = Application.ActiveWorkbook
    FileOpenDial = Application.GetOpenFilename(FileFilter:="Excel Files (*.XML), *.XML", Title:="Select File To Be Opened")
    'cancel pressed
    If FileOpenDial = False Then Exit Sub
   
    Set wb = Workbooks.Open(FileOpenDial, 0, True)
   
    wb.Worksheets(arr).Copy Before:=activeWB.Sheets(1)
    wb.Close savechanges:=False 'or True

Dave

I also tried to use it to unhide sheets based on datatype but for some reason (probably quite obvious, I am sure) it does not work:

Dim datavar as Variant

'Unhide sheets

If UserForm1.datatype.Value = "Public" Then
    datavar = """FS"", ""CF"", ""tables"""
Else
    datavar = """FS"", ""CF"", ""tables"", ""calcs"", ""tables_for_output"", ""Tier_I"", ""Tier_II"""
End If
    
For Each MySheets In Array(datavar)
    Worksheets(MySheets).Visible = True
Next

I tried to use something like datavar = "Array ("FS","CF","tables")", but it gives me a syntax error.

I am using the IF structure instead of IIF (for which thanks a lot again as I have never used it before) because I might havce more than two data types.

 

[dmt32]

I tried to use something like datavar = "Array ("FS","CF","tables")" , but it gives me a syntax error.

you have not followed solution I provided correctly, It should be

datavar = Array("FS", "CF", "tables")

without the surrounding quote (speech) marks

you can read more on the Array function in the helpfile here: Array function (Visual Basic for Applications)

Dave