Return column 2 combobox value based on selected column 1 value minus 1

[HQ2401]

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:

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:

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!

 

[smozgur]

Welcome to the MrExcel Message Board!

The following macro returns the month number from the month name string.

With the descriptions:

Sub getTheMonthName()
Dim arrSelectedMonth
Dim strMonthName As String
Dim dateDateVal As Date
Dim mth_sel As Integer
Dim mth_prev As Integer

    ' Split the second column value of the combobox
    arrSelectedMonth = Split(Sheets("Userform").MonthBox.Column(1), ".")
    
    ' We need the month parth, which is second element, index = 1,
    ' of the array returned by the Split function above
    strMonthName = arrSelectedMonth(1)

    ' Use DateValue function to convert string to an actualy date value
    ' Note that we build a string for the first day of the month
    dtDateVal = DateValue("1 " & strMonthName & " " & Year(Now()))
    
    ' Use Month function to return the month number
    mth_sel = Month(dtDateVal)
    
    ' Previous month
    mth_prev = mth_sel - 1
    
    ' Make sure it is not zero but December
    If mth_prev = 0 Then
        mth_prev = 12
    End If
    
    MsgBox mth_prev
End Sub

This one is the select month as one liner version. You can apply the same logic for the previous month as I did in the detailed code with mth_prev variable.

mth_sel = Month(DateValue("1 " & Split(Sheets("Userform").MonthBox.Column(1), ".")(1) & " " & Year(Now())))

* Edited my post to include previous month logic

 

[HQ2401]

Welcome to the MrExcel Message Board!

The following macro returns the month number from the month name string.

With the descriptions:

Sub getTheMonthName()
Dim arrSelectedMonth
Dim strMonthName As String
Dim dateDateVal As Date
Dim mth_sel As Integer
Dim mth_prev As Integer

    ' Split the second column value of the combobox
    arrSelectedMonth = Split(Sheets("Userform").MonthBox.Column(1), ".")
   
    ' We need the month parth, which is second element, index = 1,
    ' of the array returned by the Split function above
    strMonthName = arrSelectedMonth(1)

    ' Use DateValue function to convert string to an actualy date value
    ' Note that we build a string for the first day of the month
    dtDateVal = DateValue("1 " & strMonthName & " " & Year(Now()))
   
    ' Use Month function to return the month number
    mth_sel = Month(dtDateVal)
   
    ' Previous month
    mth_prev = mth_sel - 1
   
    ' Make sure it is not zero but December
    If mth_prev = 0 Then
        mth_prev = 12
    End If
   
    MsgBox mth_prev
End Sub

This one is the select month as one liner version. You can apply the same logic for the previous month as I did in the detailed code with mth_prev variable.

mth_sel = Month(DateValue("1 " & Split(Sheets("Userform").MonthBox.Column(1), ".")(1) & " " & Year(Now())))

* Edited my post to include previous month logic

Hi smozgur,

Thanks so much for your reply. I tried the code and it's not quite what I am looking for.

So when the user chooses a month number (Say 3) it should return the previous number 2 (which I got working) but I would like it to return the actual month value as you see in column D of the spreadsheet snippet so in this case 2. August

I know it's a bit specific but that's what the user is wanting for their directory structures so I don't really have much control over it unfortunately.

Also with the previous month where it is "not zero but December" I should have mentioned that December would be in a different year directory. So for example if you chose 2020-21 for the year and 1 for the month, the macro would have to look in 2019-20 and 12.

Does that make sense?

Thanks again

 

[smozgur]

I thought that you need the number value of the selected month.
If you could provide the following information, then we can try to provide further help.

What is the year combo box name?
What is the expected text result that you want to get when the macro runs (I am asking for the folder name sample)?

 

[HQ2401]

I thought that you need the number value of the selected month.
If you could provide the following information, then we can try to provide further help.

What is the year combo box name?
What is the expected text result that you want to get when the macro runs (I am asking for the folder name sample)?

The year combobox is called YearBox and this is what it looks like.

So using the above image as an example. when you compare it to the original spreadsheet snippet, 5 would be 5. November (which is the hidden second column if the Month Combobox).

What I would like is to get the previous month so in this case it would be 4. October

When you choose 1 in the month this is where the macro will have to go to 2019-20 and 12. June

Thanks

 

[smozgur]

I misunderstood the question, and also missed the two lines that you provided as path string calculation. I still don't know the exact path string but you can make further customization in the code.

Please try the following code. I know it looks a bit long, but I wanted to write it step by step to explain each step.

Following shows two different month selection, and the result (first line is the selected month folder, second line is the previous month folder).

July selected:

January selected:

I hope it helps.

Sub getFolderNames()

Dim strSelMonth As String
Dim strPrevMonth As String
Dim strPrevMonthName As String
Dim strYear1 As String
Dim arrYears
Dim intFirstYear As Integer
Dim intSecondYear As Integer
Dim strYear2 As String
Dim currMonthDir As String
Dim prevMonthDir As String
   
    ' Selected month from the drop down's second column
    ' (I see that you set it up to include both columns)
    strSelMonth = Sheets("Userform").MonthBox.Column(1)
   
    ' Previous month is a bit different. We are using List and ListIndex properties
    ' also Mod function as a helper function
    strPrevMonth = Sheets("Userform").MonthBox.List((Sheets("Userform").MonthBox.ListIndex + 11) Mod 12, 1)
   
    ' Need to know the previous month's name
    ' to calculate previous year's folder name if necessary
    strPrevMonthName = Split(strPrevMonth, ".")(1)
   
    strYear1 = Sheets("Userform").YearBox.Value
   
    ' If the previous month is December, then we need to get the previous year
    If strPrevMonthName = "December" Then
        arrYears = Split(strYear1, "-")
        intFirstYear = arrYears(0) - 1
        intSecondYear = arrYears(1) - 1
        strYear2 = intFirstYear & "-" & intSecondYear
    Else
        strYear2 = strYear1
    End If
   
    ' And the path strings
    currMonthDir = "C:\Test\" & strYear1 & "\" & strSelMonth
    prevMonthDir = "C:\Test\" & strYear2 & "\" & strPrevMonth
   
    MsgBox currMonthDir & vbCrLf & prevMonthDir

   
End Sub

 

[HQ2401]

I misunderstood the question, and also missed the two lines that you provided as path string calculation. I still don't know the exact path string but you can make further customization in the code.

Please try the following code. I know it looks a bit long, but I wanted to write it step by step to explain each step.

Following shows two different month selection, and the result (first line is the selected month folder, second line is the previous month folder).

July selected:
View attachment 24559

January selected:
View attachment 24560


I hope it helps.

Sub getFolderNames()

Dim strSelMonth As String
Dim strPrevMonth As String
Dim strPrevMonthName As String
Dim strYear1 As String
Dim arrYears
Dim intFirstYear As Integer
Dim intSecondYear As Integer
Dim strYear2 As String
Dim currMonthDir As String
Dim prevMonthDir As String
  
    ' Selected month from the drop down's second column
    ' (I see that you set it up to include both columns)
    strSelMonth = Sheets("Userform").MonthBox.Column(1)
  
    ' Previous month is a bit different. We are using List and ListIndex properties
    ' also Mod function as a helper function
    strPrevMonth = Sheets("Userform").MonthBox.List((Sheets("Userform").MonthBox.ListIndex + 11) Mod 12, 1)
  
    ' Need to know the previous month's name
    ' to calculate previous year's folder name if necessary
    strPrevMonthName = Split(strPrevMonth, ".")(1)
  
    strYear1 = Sheets("Userform").YearBox.Value
  
    ' If the previous month is December, then we need to get the previous year
    If strPrevMonthName = "December" Then
        arrYears = Split(strYear1, "-")
        intFirstYear = arrYears(0) - 1
        intSecondYear = arrYears(1) - 1
        strYear2 = intFirstYear & "-" & intSecondYear
    Else
        strYear2 = strYear1
    End If
  
    ' And the path strings
    currMonthDir = "C:\Test\" & strYear1 & "\" & strSelMonth
    prevMonthDir = "C:\Test\" & strYear2 & "\" & strPrevMonth
  
    MsgBox currMonthDir & vbCrLf & prevMonthDir

  
End Sub

Thanks so much for your help smozgur! That code works for me.

 

[smozgur]

Thanks so much for your help smozgur! That code works for me.

Great to hear it helps!