VBA - Lists with dynamic content for dropdowns

[MalcolmGill]

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]

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.

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]

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]

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]

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.

 

[MalcolmGill]

You're just all so helpful!

 

[DanteAmor]

Hi DanteAmor
What a hero! In just a few lines of code you've solved my dynamic populating of the list!

I'm glad to help you. Thanks for the feedback.