Select current month in combobox on userform for English language

[Ali M]

Hi,
I would when run the form then will select DEC-24 automatically , if I try select month is not current month then will show message "sorry, the current month is wrong" and should select DEC-24 again.
I would deal with English months as inside the code , because the language is Arabic in my PC .

Option Base 1

Private Sub ComboBox1_Change()
If ComboBox1.Value <> Format(ComboBox1, "mmm-yy") Then MsgBox "sorry, the current month is wrong": Exit Sub
End Sub

Private Sub UserForm_Activate()
 Dim MonthName As Variant

    Dim Yr As Long

    MonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

    ' get last 2 digits of current year

    Yr = Right(Year(Date), 2)

    Dim i As Integer

    ' add 12 English months from position in the array

    For i = 1 To 12

        ' append current year to month

        Me.ComboBox1.AddItem MonthName(i) & "-" & Yr

    Next

End Sub

thanks

 

[dmt32]

ok
so I guess the problem from EXCEL version !.
currently use 2010,2016

I no longer have access to either of those Excel versions but would not have thought, there is anything in the structure of the code that would be version dependent.
If can place copy of your workbook on file sharing site like dropbox will have further look.

Dave

 

[Ali M]

If can place copy of your workbook on file sharing site like dropbox will have further look.

Dropbox

 

[dmt32]

Hi
worked perfectly ok for me.

Just a guess but try this updated version & see what happens

Option Base 1
Dim DisableEvents   As Boolean
Dim WhichMonth       As String
Private Sub ComboBox1_Change()
    If DisableEvents Then Exit Sub
    With Me.ComboBox1
        If .Value <> WhichMonth Then
            DisableEvents = True
            MsgBox "sorry, the current month Is wrong", 48, "Invalid Selection"
            .Value = WhichMonth
        End If
    End With
    DisableEvents = False
End Sub

Private Sub UserForm_Activate()
    Dim Yr          As Long, i As Long
    Dim MonthNames  As Variant
    
    ' get last 2 digits of current year
    Yr = Right(Year(Date), 2)
    
    WhichMonth = Format(Date, "mmm-yy")
    
    MonthNames = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    ' add 12 months from monthname function
    With Me.ComboBox1
        .Clear
        For i = 1 To 12
            ' append current year to month
            .AddItem MonthNames(i) & "-" & Yr
        Next i
        .Value = WhichMonth
        .Style = fmStyleDropDownList
    End With
    
End Sub

Dave

 

[Ali M]

worked perfectly ok for me.

this is big mystery !!!

Just a guess but try this updated version & see what happens

thanks, nothing changes , sorry !
I can't see what's the problem !

 

[RoryA]

It's probably a regional issue. Try changing this line:

    WhichMonth = Format(Date, "mmm-yy")

to this:

    WhichMonth = MonthNames(Month(Date)-1) & "-" & Yr

 

[Ali M]

It's probably a regional issue. Try changing this line:

will show mismatch error in your suggestion !

 

[RoryA]

I don't see how that is possible - please post the code as you used it. I did just note that you are using Option Base 1 for some reason, so it should just be:

WhichMonth = MonthNames(Month(Date)) & "-" & Yr

 

[Ali M]

here is the whole code

Option Base 1
Dim DisableEvents   As Boolean
Dim WhichMonth       As String
Private Sub ComboBox1_Change()
    If DisableEvents Then Exit Sub
    With Me.ComboBox1
        If .Value <> WhichMonth Then
            DisableEvents = True
            MsgBox "sorry, the current month Is wrong", 48, "Invalid Selection"
            .Value = WhichMonth
        End If
    End With
    DisableEvents = False
End Sub

Private Sub UserForm_Activate()
    Dim Yr          As Long, i As Long
    Dim MonthNames  As Variant
    
    ' get last 2 digits of current year
    Yr = Right(Year(Date), 2)
    
    'WhichMonth = Format(Date, "mmm-yy")
        'WhichMonth = MonthNames(Month(Date) - 1) & "-" & Yr
        WhichMonth = MonthNames(Month(Date)) & "-" & Yr


    
    MonthNames = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    ' add 12 months from monthname function
    With Me.ComboBox1
        .Clear
        For i = 1 To 12
            ' append current year to month
            .AddItem MonthNames(i) & "-" & Yr
        Next i
        .Value = WhichMonth
        .Style = fmStyleDropDownList
    End With
    
End Sub

 

[RoryA]

Ah - move this line above the one I posted:

MonthNames = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

 

[Ali M]

finally works perfectly !
many thanks Rorya
also thanks for Dave,Kaper.