VBA select entry from dropdown menu in worksheet

[fboehlandt]

Hi everyone,
I have a worksheet with two dropdown menus. The first contains months from January to December, the second contains years from 2011 to 2025. Upon opening the worksheet, I would like the entries selected to match to the month and year of the current system date. I know this is relatively easy to implement but, unfortunately, the macro recorder is of no help here. Could someone please indicate how to refer to dropdown menus in an Excel worksheet and how to select a specific entry from the dropdown menu?

 

[Jaafar Tribak]

Hi everyone,
I have a worksheet with two dropdown menus. The first contains months from January to December, the second contains years from 2011 to 2025. Upon opening the worksheet, I would like the entries selected to match to the month and year of the current system date. I know this is relatively easy to implement but, unfortunately, the macro recorder is of no help here. Could someone please indicate how to refer to dropdown menus in an Excel worksheet and how to select a specific entry from the dropdown menu?

Maybe this :

Sheet1.ComboBox1.Value = Format(Month(Now), "MMMM")
Sheet1.ComboBox2.Value = Year(Now)

Change the name of the sheet and comboboxes as required.

 

[Jaafar Tribak]

If you are using dropdowns from the Forms toolbar instead of ActiveX controls then you can try something along these lines :

Sheet1.Shapes("Drop Down 1").OLEFormat.Object.Text = Year(Now)

 

[paddydive]

Hi,

I am new to VBA but tried to write some codes for this. let me know is it correct of there is some improvements needed.

Sub dropdw()
Dim currentyear As Long
Dim currentmonth As String
Dim yeari, monthi As Variant
Dim p, q As Long
currentyear = Year(Now())
currentmonth = Month(Now())
yeari = [yeartable]
monthi = [monthtable]
p = LBound(yeari)
q = UBound(monthi)
Do Until [Y_ear].Value = currentyear
[Y_ear] = yeari(p, 1)
p = p + 1
Loop

For q = 1 To currentmonth
[Mth] = monthi(q, 1)
Next
End Sub

 

[fboehlandt]

Thanks all for your great input. i have now settled on the following:

Private Sub Workbook_Open()
'Sets month/year to system month/year
Dim wb As Workbook
Dim ws As Worksheet
Dim MyMonth As String
Dim MyYear As Integer
MyMonth = WorksheetFunction.Text(Date, "[$-407]MMMM;@")
MyYear = Year(Date)
Set wb = ThisWorkbook
Set ws = wb.Sheets("Aktionen pro Monat")
With ws
    .Range("C4").Value = MyMonth
    .Range("F4").Value = MyYear
End With
End Sub

Since the dropdown menus are actually placed into cells, I have decided to 'fill' the cells with the appropriate month and year. I had to translate the month into German as well, hence the [$-407] locale ID. Are there any issues to this approach that i might not be aware of? Many thanks again