Hi everyone,
I am new to VBA and have been struggling with this task.
I have 2 multi-column comboboxes in a spreadsheet that have their lists derived from a different spreadsheet.
The columns in the reference spreadsheet looks like this.
This is what the Comboboxes look like
The Month combo box displays Pay (Column C) and hides Month (Column D).
What I want to do is get the Month (D) value based on the selected Pay (C) value minus 1 so that I can include it as a directory reference to do file copies.
Essentially I want to copy files from the previous month directory to the current month directory based on the selected "Current" Pay value.
E.G:
C:\Test\" & yr_selected & "\" & mth_selected (current month based on combobox selection)
C:\Test\" & yr_selected & "\" & prv_mth (previous month based on combobox selection - 1)
I was able to calculate the minus 1 for the Pay but cannot get to the next step of returning the value for Month.
Here's my code so far:
I was just testing that it would return Pay - 1 in the messagebox.
I tried using:
but got type mismatch error.
Once I get over this month reference hurdle, the next one is how to neatly handle when the current month is 1 and the previous month (12) is in the previous year directory .
Any assistance would be greatly appreciated!
I am new to VBA and have been struggling with this task.
I have 2 multi-column comboboxes in a spreadsheet that have their lists derived from a different spreadsheet.
The columns in the reference spreadsheet looks like this.
This is what the Comboboxes look like
The Month combo box displays Pay (Column C) and hides Month (Column D).
What I want to do is get the Month (D) value based on the selected Pay (C) value minus 1 so that I can include it as a directory reference to do file copies.
Essentially I want to copy files from the previous month directory to the current month directory based on the selected "Current" Pay value.
E.G:
C:\Test\" & yr_selected & "\" & mth_selected (current month based on combobox selection)
C:\Test\" & yr_selected & "\" & prv_mth (previous month based on combobox selection - 1)
I was able to calculate the minus 1 for the Pay but cannot get to the next step of returning the value for Month.
Here's my code so far:
VBA Code:
Private Sub WD2_Click()
Dim mth_sel As String
mth_sel = Sheets("Userform").MonthBox.Column(0) - 1
MsgBox mth_sel
End Sub
I was just testing that it would return Pay - 1 in the messagebox.
I tried using:
VBA Code:
mth_sel = Sheets("Userform").MonthBox.Column(1) - 1
but got type mismatch error.
Once I get over this month reference hurdle, the next one is how to neatly handle when the current month is 1 and the previous month (12) is in the previous year directory .
Any assistance would be greatly appreciated!