Passing Variable from User from to Macro

[KariJones21]

I am trying to pass a variable from my user form selection to my macro. right now my Macro isn't running cause I haven't defined the "Month" since it is selected in the User Form. My "Month = Cselection.value" is not working.

Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub EnterButton_Click()

Call Test
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
With CSelection
.AddItem "Jan"
.AddItem "Feb"
.AddItem "Mar"
.AddItem "Apr"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "Aug"
.AddItem "Sept"
.AddItem "Oct"
.AddItem "Nov"
.AddItem "Dec"
.AddItem "Q1-QTD"
.AddItem "Q2-QTD"
.AddItem "Q3-QTD"
.AddItem "Q4-QTD"
.AddItem "TY-YTD"
.AddItem "QoQ"
End With
End Sub


that is the User form below is the macro

Sub Test()
' Stops screen flicking
Application.ScreenUpdating = False

With Workbooks("Consolidated-Template.xlsm")

Dim Month As String

' Month = Application.InputBox(prompt:="Enter the month you will like to consolidate: ")
Month = CSelection.Value

Dim material As String
For I = 2 To 4
material = .Worksheets(I).Range("A2").Value
Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets("Selection Criteria").Range("B8").Value = material
.Worksheets(I).Range("B8:Q15").Value = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets(Month).Range("B8:Q15").Value
.Worksheets(I).Range("B22:Q24").Value = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets(Month).Range("B22:Q24").Value
.Worksheets(I).Range("B31:Q33").Value = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets(Month).Range("B31:Q33").Value
.Worksheets(I).Range("B36:Q36").Value = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets(Month).Range("B36:Q36").Value
.Worksheets(I).Range("B47:Q49").Value = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm").Worksheets(Month).Range("B47:Q49").Value
Next I
End With
End Sub

 

[dmt32]

Hi,
You can pass a value from one procedure to another either by scoping your variables as public or pass value as an argument (my preferred approach) to your procedure.


e.g.

Private Sub EnterButton_Click()
Call Test(Me.CSelection.Text)
End Sub

Sub Test(sMonth As String)
MsgBox sMonth
End Sub

You will note that I have prefixed Month with an "s" This is because Month is a VBA keyword & generally not a good idea use as a variable.

Where you want to use these words as variables in your code, just prefix them with another letter (s in this example) to avoid any problems that could arise.

Hope Helpful

Dave
</SPAN>

 

[KariJones21]

Thank you...But I am a little new to this still. If I enter what you have, I get a msg Box now that says Jan. How do I now run the macro with that in it?

Thanks so much

 

[dmt32]

Hi,
sorry just did that as a simple example to show passing value to your procedure & with msgbox displaying, shows that it works.

I have attempted to tidy up your test code & hopefully have it correct - try following & see if it does what you want:

Sub Test(ByVal sMonth As String)
    Dim wb As Workbook
    Dim wsMonth As Worksheet
    Dim material As String
    
     Set wb = Workbooks("2014 EBITDA Bridge Template v4-Final.xlsm")
    Set wsMonth = wb.Worksheets(sMonth)

    ' Stops screen flicking
    Application.ScreenUpdating = False
    With Workbooks("Consolidated-Template.xlsm")
        For i = 2 To 4
            material = .Worksheets(i).Range("A2").Value
            wb.Worksheets("Selection Criteria").Range("B8").Value = material
            With .Worksheets(i)
                .Range("B8:Q15").Value = wsMonth.Range("B8:Q15").Value
                .Range("B22:Q24").Value = wsMonth.Range("B22:Q24").Value
                .Range("B31:Q33").Value = wsMonth.Range("B31:Q33").Value
                .Range("B36:Q36").Value = wsMonth.Range("B36:Q36").Value
                .Range("B47:Q49").Value = wsMonth.Range("B47:Q49").Value
            End With
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Also for info, here is another way for your intialize event code:

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 1 To 18
        With CSelection
            Select Case i
            Case Is < 13
                .AddItem MonthName(i, True)
            Case Is < 17
                .AddItem "Q" & i - 12 & "-QTD"
            Case 17
                .AddItem "TY-YTD"
            Case Else
                .AddItem "QoQ"
            End Select
        End With
    Next i
End Sub

Hope helpful

Dave