VBA select entry from dropdown menu in worksheet

fboehlandt

Active Member
Joined
Sep 9, 2008
Messages
334
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
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 :

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

Change the name of the sheet and comboboxes as required.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
If you are using dropdowns from the Forms toolbar instead of ActiveX controls then you can try something along these lines :

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

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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

Active Member
Joined
Sep 9, 2008
Messages
334
Thanks all for your great input. i have now settled on the following:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

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