Simple listbox question

[litestream]

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.

 

[jindon]

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

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]

Thanks for your help.

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

 

[DominicB]

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?

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]

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]

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.

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

HTH

DominicB

 

[litestream]

Here is the code I am using:

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]

I've tried the:

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

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

 

[jindon]

Not sure if this is what you wanted
Erase the code in sheet module

to ThisWorkbook Module
Save once and open again

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

 

[litestream]

Excellent, thanks very much.