I have created a listbox in excel, which contains values from
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 4
ListBox1.RowSource = "b11:j17"
from sheet1. While it loads all the data from rang("b11:j17"), I would like it to load only those line that have an ‘X” in ‘Open’, which is the 'C' column. The first question is how can I do this?
The values from the listbox come from sheet1 and the listbox looks exactly like the example bellow where ‘closed and ‘open’ are column and the rest are values in those:
A B C
Then, I want to user to select only one value from the listbox from the listbox and perform a task. However, when the user double click on the value, say the first one:
the task that corresponds to that selection does not correspond to the line on sheet1. the only way I can make sure it does is by selecting the row before I open the listbox. Is there a way to fix this?
Here is the code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim newval As Date
If Sheet1.CommandButton1.Caption = "Start" Then
ElseIf Sheet1.CommandButton1.Caption = "Stop Old" Then
ActiveCell.Value = Time
If Range("J2").Cells.Interior.ColorIndex = 6 Then
Range("J" & CStr(ActiveCell.Row)).Cells.Interior.ColorIndex = 6
Sheet1.CommandButton1.Caption = "Start"
To load only specific items into the list box you will need to load (the required) lines of data from the sheet into an array in code and assign the array to the listbox List property ie. Listbox.List()=ArrayName()
The data from the select item can be abtained as follows
Listbox1.value - This is the value in the defined 'bound column' property, or
Listbox.List(row,column) - Where 'row' is the selected row (given by Lisbox1.ListIndex property) and column is the column containing the data required.
Using mulitlpe selection is a little more complicated if required, but all the above, and information on multiple selection properties, is available in the VBA Help.
when I click on the selected row in the list box, i want to activate the cell A(i) that the row corresponds and that is selected in the listbox. How do I transalate
where 1 is = i the loop bellow?
the line 'Range("A" & CStr(i)).Activate' does not work. How can I make it work?
Dim i As Integer
Dim ans As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.ListIndex = i Then
Range("A" & CStr(i)).Activate