VBA - Lists with dynamic content for dropdowns

MalcolmGill

New Member
Joined
Aug 9, 2019
Messages
7
Good morning folks.

I usually manage to find solutions by scouring forums but this time I've drawn a confusing blank. I've found stuff which partly solves the problem but I can't assemble a complete answer. So here goes.

I have
a) fixed data ws1.Range("A1:A3").
b) dynamic data in ws2.Range("A1:A" & lastRow) - +/- 100 cells of data.
c) ws3 with (hopefully) dropdown lists and with a control button to run coding using parameters after user selections from each dropdown list.

OK.

1) I'm not sure which to use from ComboBox/ListBox, Form Control/ActiveX Control for the dropdown lists.
2) I want to populate the lists each time ws3 is activated since ws2 data may have changed rather than WorkBook Open approach.
3) I want to specify font name, size and bold within the lists.

My 75 year old brain is struggling. Can anyone assist please?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,648
Office Version
2007
Platform
Windows
Good morning folks.

I usually manage to find solutions by scouring forums but this time I've drawn a confusing blank. I've found stuff which partly solves the problem but I can't assemble a complete answer. So here goes.

I have
a) fixed data ws1.Range("A1:A3").
b) dynamic data in ws2.Range("A1:A" & lastRow) - +/- 100 cells of data.
c) ws3 with (hopefully) dropdown lists and with a control button to run coding using parameters after user selections from each dropdown list.

OK.

1) I'm not sure which to use from ComboBox/ListBox, Form Control/ActiveX Control for the dropdown lists.
2) I want to populate the lists each time ws3 is activated since ws2 data may have changed rather than WorkBook Open approach.
3) I want to specify font name, size and bold within the lists.

My 75 year old brain is struggling. Can anyone assist please?

With ComboBox/ListBox ActiveX Control you can specify font, size, bold.

Try whit ComboBox. Then create an activex combobox on the ws3 sheet


Put the following code in the events on the ws3 sheet.


Change "ws2" to the name of your sheet.

Code:
Private Sub Worksheet_Activate()
  Dim ws2 As Worksheet, c As Range
  Set ws2 = Sheets("[B][COLOR=#ff0000]ws2[/COLOR][/B]")
  ComboBox1.Clear
  For Each c In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
    ComboBox1.AddItem c
  Next
End Sub
SHEET EVENT
Right click the tab of the sheet you want this to work, this case ws2, select view code and paste the code into the window that opens up.


-------------------------------------------------


Change the font of the combo as follows.

1.- Select Developer
2.- Select Design Mode
3.- Select combobox on sheet
4.- Select Properties




5.- Select Font property
6.- Click on ... button





7.- Select desired font and size
8.- Press Ok
9.- Properties window
10.- Click on Design Mode

-------------------------------------------------------------------------------

Select the ws2 sheet, now select the ws3 sheet, automatically the combo will be loaded.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,648
Office Version
2007
Platform
Windows
Edit:
Must be ws3


SHEET EVENT
Right click the tab of the sheet you want this to work, this case ws3, select view code and paste the code into the window that opens up.
 

MalcolmGill

New Member
Joined
Aug 9, 2019
Messages
7
Hi DanteAmor
What a hero! In just a few lines of code you've solved my dynamic populating of the list!
I'd already solved the font issues on start-up (see below) but it included a fixed range for Groups. This meant that if the number of Groups changed I had to close the application and reopen it.
Our local branch of U3A (University of the 3rd Age) salutes you.

Private Sub Workbook_Open()
With Sheets("Registers").ComboBox1
.Font.name = "HandelGotDBol"
.Font.Size = 20
.Font.Bold = True
.List = [SetUp!C34:C36].Value
End With

With Sheets("Registers").ComboBox2
.Font.name = "HandelGotDBol"
.Font.Size = 20
.Font.Bold = True
.List = [Groups!B2:C200].Value
End With
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,879
An alternate approach would be to use define two Named Ranges

Name: FirstRange RefersTo: =Sheet1!$A$1:$A$3
Name: SecondRange RefersTo:=Sheet2!$A$1:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A),1)

Then use those ranges as the list source for either a Forms Menu ComboBox or and in-cell Data Validation list.
 

Forum statistics

Threads
1,085,210
Messages
5,382,369
Members
401,784
Latest member
Jonnyboisme

Some videos you may like

This Week's Hot Topics

Top