Simple listbox question

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Is it possible to populate a listbox with numbers 1 to 5 without referencing to values in cells?

I can't use the Data>Validation>List option because I have to reduce the size of my spreadsheet to fit the screen and the text size is too small to see using this method.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
assuming you r listbok is on the sheet

to the sheet module

click another sheet once and comback to the sheet after pasting the code
Code:
Private Sub Worksheet_Activate()
Dim i As Integer
With Me.ListBox1
   .Clear
   For i = 1 To 10
      .AddItem i
   Next
End With
End Sub
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Thanks for your help.

How would I populate a combo box with 2 options: "Standard" and "Euro"
in a similar way?
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon litestream

How would I populate a combo box with 2 options: "Standard" and "Euro" in a similar way?

Not quite sure what you mean by this. Do you want to have 2 columns on your listbox?

Code:
Private Sub Worksheet_Activate()
Dim data(1 To 10, 1 To 2)
For i = 1 To 10
data(i, 1) = i
Next i
For i = 1 To 10
data(i, 2) = i * 10
Next i
ListBox1.ColumnCount = 2
ListBox1.List = data
End Sub

HTH

DominicB
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

Thanks, that was great

Is there a way to retain the values in the combobox without clearing them each time?

For example, one of my comboboxes has 1 to 7 as options and is linked to a cell H14. If someone selects 4 is there a way to retain the value in H14 until the next occasion somebody changes it?
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi litestream

Do you mean you have linked cell H14 to the ListBox via the object's properties? If so you could try undoing the link and using this code which will dump the selected listbox value into H14 every time it changes.

Code:
Private Sub ListBox1_Click()
Range("H14").Value = ListBox1.Value
End Sub

HTH

DominicB
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

Here is the code I am using:


Code:
Private Sub Worksheet_Activate() 

With Me.ComboBox1 
    .Clear 
    .AddItem "Standard" 
    .AddItem "Euro" 
End With 

Dim i As Integer 
With Me.ComboBox2 
   .Clear 
   For i = 1 To 7 
      .AddItem i 
   Next 
End With 

End Sub

ComboBox1 is positioned on top of cell N10 and Combobox2 is positioned on top of O12 so that the user can see which value is currently selected.

If I remove the link from the ComboBox properties, the cells appear blank the ComboBox has been cleared and is obscuring the cell below.
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
I've tried the:

Code:
Private Sub ListBox1_Click() 
Range("H14").Value = ListBox1.Value 
End Sub

method, but that resets each time I switch worksheets also.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure if this is what you wanted
Erase the code in sheet module

to ThisWorkbook Module
Save once and open again
Code:
Private Sub Workbook_Open() 
Dim i As Integer
With Sheets("YourSheetNameHere")
   With .ComboBox1 
       .AddItem "Standard" 
       .AddItem "Euro" 
   End With 
   With .ComboBox2 
       For i = 1 To 7 
          .AddItem i 
       Next 
   End With
End With 
End Sub
 

Forum statistics

Threads
1,136,370
Messages
5,675,369
Members
419,567
Latest member
Naveen27mech

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