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

HQ2401

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.

Columns2.JPG


This is what the Comboboxes look like

Comboboxes.JPG


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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,365
Welcome to the MrExcel Message Board!

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

With the descriptions:
VBA Code:
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.
VBA Code:
mth_sel = Month(DateValue("1 " & Split(Sheets("Userform").MonthBox.Column(1), ".")(1) & " " & Year(Now())))

* Edited my post to include previous month logic
 
Last edited:

HQ2401

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the MrExcel Message Board!

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

With the descriptions:
VBA Code:
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.
VBA Code:
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

BatCoder
Joined
Feb 28, 2002
Messages
1,365
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

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

ComboBoxes 2.JPG


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

BatCoder
Joined
Feb 28, 2002
Messages
1,365
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:
1603239991485.png


January selected:
1603240019895.png



I hope it helps.

VBA Code:
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
 
Solution

HQ2401

New Member
Joined
Oct 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,153
Messages
5,628,997
Members
416,358
Latest member
grsaltzman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top