Link to home
Create AccountLog in
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

asked on

Excel VBA – create worksheet listbox

Can someone give me code to create a worksheet listbox at runtime?


Title corrected Excek > Excel

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Here's an example workbook. Click in the header row to create a ListBox which autosizes to the length of text below
add_listbox-v5a.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Noah
Noah
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Saqib Husain

ASKER

Thanks both. I am going with the method from Noah. I have created the listbox. 


Can someone tell me the syntax to change the font size of the listbox?


Also, how to assign code to an item clicked.


Pointers to websites are welcome.

I have updated my code to change the font size of the listbox. I have included notes in the code for your reference.

Sub CreateDynamicListbox()
    Dim ws As Worksheet
    Dim lb As ListBox
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
    
    ' Set the worksheet where you want to create the listbox
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your desired sheet name
    
    ' Set the range of data for the listbox
    Set rng = ws.Range("A1:A5") ' Replace "A1:A5" with your desired range
    
    ' Create the listbox control
    Set lb = ws.ListBoxes.Add(Left:=100, Top:=100, Width:=100, Height:=100)
    
    With lb
        ' Set the properties of the listbox
        .Name = "DynamicListbox"
        .ListStyle = fmListStylePlain
        .LinkedCell = ""
        
        ' Change the font size of the listbox
        .Font.Size = 12 ' Replace 12 with your desired font size
        
        ' Add items to the listbox from the range
        For Each cell In rng
            .AddItem cell.Value
        Next cell
    End With
End Sub

Open in new window

Hi, thanks. But lb.font.size does not work.User generated image


Apologies. How about this?

Sub CreateDynamicListbox()
    Dim ws As Worksheet
    Dim lb As ListBox
    Dim rng As Range
    Dim cell As Range
    Dim i As Integer
    
    ' Set the worksheet where you want to create the listbox
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your desired sheet name
    
    ' Set the range of data for the listbox
    Set rng = ws.Range("A1:A5") ' Replace "A1:A5" with your desired range
    
    ' Create the listbox control
    Set lb = ws.ListBoxes.Add(Left:=100, Top:=100, Width:=100, Height:=100)
    
    With lb
        ' Set the properties of the listbox
        .Name = "DynamicListbox"
        .ListStyle = fmListStylePlain
        .LinkedCell = ""
        
        ' Add items to the listbox from the range
        For Each cell In rng
            .AddItem cell.Value
        Next cell
    End With
    
    ' Modify the font size of each listbox item
    With lb.Object
        For i = 0 To .ListCount - 1
            .List(i, 0).Font.Size = 12 ' Replace 12 with your desired font size
        Next i
    End With
End Sub

Open in new window

No joy.


User generated image


Roy, since there is silence from Noah I am now trying your solution. 


Please tell me how to change the font size and how to select an item and run a macro.

A ListBox on a sheet is completely different from one on a UserForm.

Adding code to a dynamic object is possible but requires the user to change security settings and is probably not the best way to go.

What is the purpose for creating the userform dynamically?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Rory, that's what I thought.

I think a userform with a ListBox would be the best way, depending on what the OP wants to do with it.

Rory, I might live with the font size but can I assign a macro to process the selection?

As I said, adding code during the creation of the ListBox should be possible but you need to get the user to  enable programmatic access to the VBA Project. I  think this can only be done manually.

If you explain what the end use of the ListBox is I am sure there is a simpler alternative.

Never mind. I shall use a userform. I know how to do that. Thanks all.

Post back if you need help with the UserForm