VBA - Lists with dynamic content for dropdowns

MalcolmGill

New Member
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
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
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top