Write Macro Code to Change based on User Choice

[AStevenG]

Hello everyone, I am relatively new to coding and have been learning as I go so forgive me if there is a very simple solution that I have yet to learn. So what I am trying to do is create a macro that will begin with a userform combo box giving the user an option to select a month and year. Then with this information, the rest of the macro code will change (or replace certain characters) based on this decision. For example, say the user chooses "April" "2014" in the userform combo boxes at the start of the macro, I would like the code in the rest of the macro to change to accommodate for that choice by replacing the code where there is a month to put in "April" and the places where there is a year to put in "2014." The reason I need this specifically to happen is because each month I do reporting and reference certain workbooks titled "Month Year", and if I can get that code to change based on what I choose in the userform then the macro will reference the proper workbook and find the correct information in that workbook. I am able to create the userform and the code for one month's worth of reporting but I cannot figure out how to change the code dynamically based on the decision. I'm trying to avoid making a separate macro for every possible month and year combination with this. I hope this makes sense, and I hope someone out there can give me some help on this issue (if it is even possible)

I appreciate any help or advice anyone can offer, thanks!

[fredlo2010]

Hi Steven,

Yes this can be done and its done through variables.

if your code shows a message box then:

Please Login or Register  to view this content.

If you have a sample workbook or code share and I can help you more.

Thanks

[AStevenG]

Thank you for your response. That has gotten me closer... Below is a sample of my code that contains an error because it is not referencing the proper excel document to find the source data. To help make sense of this the second arguement in each =GetPivotdata function should be "Copy of Month Year.xlsx" (i.e. Copy of March 2012.xlsx) and the last arguement should be the date in numbers (i.e. Date(yyyy,m,1). The userform runs but when it gets to finding the proper place to find the data it searches elsewhere (even with the source data workbook opened).

ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of A"",'[Copy of UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value.xlsx]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of B"",'[Copy of UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value.xlsx]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of C"",'[Copy of UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value.xlsx]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub



I appreciate any help on what my error might be, thanks!

[fredlo2010]

Can you use code tags when posting ? It makes the code easier to read. Just highlight it and hit the "#" button on the little toolbar. Or type [CODE ]Place Your Code Here[ /CODE]

Thanks