Hi
Wind: XP
Excel: 2k
I have two questions:

I have created a listbox in excel, which contains values from

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 4
ListBox1.RowSource = "b11:j17"
End Sub

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
closed open
one x
two x
three x
four x
five x
six x

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:

one x

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

Sheet1.CommandButton1.Caption = "Stop Old"
Range("J2").Interior.ColorIndex = xlNone
'ActiveCell.Value = Time
Range("J2").Value = ""
Range("H2") = Now
Range("I" & CStr(ActiveCell.Row)).Activate
Call StartTimer
Call MyMainMacro
UserForm2.Hide

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
End If
Call StopTimer
Call StopIt
UserForm2.Hide
UserForm1.Show
Sheet1.CommandButton1.Caption = "Start"
End If
End Sub

Hi

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.

Trust this helps.

MTB

Hi,

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
range("A1").activate
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
End If
Next

Hi alexxx15

This code in will select the 'A' Cell of the required row when a new item is selected in the list

[code]
Private Sub ListBox1_Click()
Dim i As Integer

With ListBox1
For i = 0 To .ListCount - 1
If .ListIndex = i Then Range("A" & i + 1).Select
Next i
End With
End Sub
[code/]

MTB

Hi again

Just looking at it again (engage brain etc....), it is even simpler (based on your example) this will do the same !

Although I obviously do not know what you are ultimatly doing (ie you are not using a list box to select a cell for no reason!) but this will do what you have asked?

MTB