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

 

[Kaper]

Following your approach it could be:

Dim MonthName As Variant

Private Sub ComboBox1_Change()
Dim thismonth As String
thismonth = MonthName(Month(Date)) & "-" & Right(Year(Date), 2)
If ComboBox1.Value <> thismonth Then
  MsgBox "sorry, the current month is different than system date"
  ComboBox1.Value = thismonth
End If
End Sub

Private Sub UserForm_Activate()
 
    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

Or rewriting it based on international locale code:

Private Sub ComboBox1_Change()
Dim thismonth As String
thismonth = WorksheetFunction.Text(Date, "[$-409]mmm-yy")
If ComboBox1.Value <> thismonth Then
  MsgBox "sorry, the current month is different than system date"
  ComboBox1.Value = thismonth
End If
End Sub

Private Sub UserForm_Activate()
Dim i As Integer
    ' add 12 English months from position in the array
    For i = 1 To 12
        Me.ComboBox1.AddItem WorksheetFunction.Text(DateSerial(Year(Date), i, 1), "[$-409]mmm-yy")
    Next i
End Sub

 

[Ali M]

both two suggestions works but not completely !
I would also show current month automatically when run the form .
thanks

 

[Kaper]

But it is already shown in Change procedure. Use the same apprach.
So for instance:

Private Sub ComboBox1_Change()
Dim thismonth As String
thismonth = WorksheetFunction.Text(Date, "[$-409]mmm-yy")
If ComboBox1.Value <> thismonth Then
  MsgBox "sorry, the current month is different than system date"
  ComboBox1.Value = thismonth
End If
End Sub

Private Sub UserForm_Activate()
Dim i As Integer
    ' add 12 English months from position in the array
    For i = 1 To 12
        Me.ComboBox1.AddItem WorksheetFunction.Text(DateSerial(Year(Date), i, 1), "[$-409]mmm-yy")
    Next i
ComboBox1.Value = WorksheetFunction.Text(Date, "[$-409]mmm-yy")
End Sub

But in this situation - why use combobox at all? May be this date shall be just label if a user cannot change it.
Or may be Change event shall just giva a warning, but leave the value as it has been selected by user. so:

Private Sub ComboBox1_Change()
If ComboBox1.Value <> WorksheetFunction.Text(Date, "[$-409]mmm-yy") Then MsgBox "Warning: The current month is different than system date", vbExclamation
End Sub

Private Sub UserForm_Activate()
Dim i As Integer
    ' add 12 English months from position in the array
    For i = 1 To 12
        Me.ComboBox1.AddItem WorksheetFunction.Text(DateSerial(Year(Date), i, 1), "[$-409]mmm-yy")
    Next i
ComboBox1.Value = WorksheetFunction.Text(Date, "[$-409]mmm-yy")
End Sub

 

[Ali M]

this is not what I want ,sorry!

 

[Ali M]

but leave the value as it has been selected by user

should return for current month.

 

[dmt32]

should return for current month.

MonthName function exists in the application & you should not need to create an array of months.

See if following does what you want.

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

Private Sub UserForm_Activate()
    Dim Yr          As Long, i As Long
   
    ' get last 2 digits of current year
    Yr = Right(Year(Date), 2)
   
    ThisMonth = Format(Date, "mmm-yy")
   
    ' add 12 months from monthname function
    With Me.ComboBox1
        .Clear
        For i = 1 To 12
            ' append current year to month
            .AddItem MonthName(i, True) & "-" & Yr
        Next i
        .Value = ThisMonth
        .Style = fmStyleDropDownList
    End With
   
End Sub

Dave

 

[Ali M]

Thanks Dave,
but unfortunately your code deal with language based on setting regional in my PC ,in this case will show Arabic!
based on my OP should show English months even setting regional in my PC is Arabic.

 

[dmt32]

Thanks Dave,
but unfortunately your code deal with language based on setting regional in my PC ,in this case will show Arabic!
based on my OP should show English months even setting regional in my PC is Arabic.

OK my misunderstanding, will need to re-instate your array

Make changes shown in BOLD & see if helps

Option Base 1
Dim DisableEvents   As Boolean
Dim ThisMonth       As String
Private Sub ComboBox1_Change()
    If DisableEvents Then Exit Sub
    With Me.ComboBox1
        If .Value <> ThisMonth Then
            DisableEvents = True
            MsgBox "sorry, the current month Is wrong", 48, "Invalid Selection"
            .Value = ThisMonth
        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)
    
    ThisMonth = 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 = ThisMonth
        .Style = fmStyleDropDownList
    End With
    
End Sub

 

[Ali M]

when run the form will show message about no month selected , should just select current month when run the form
after message is gone will show error could not set the value property in this line

.Value = ThisMonth