CommandButton1_Click()
Dim i As Long
Dim cell As Range
Dim rng As Range

With UserForm1
.ListBox1.Clear
.ListBox1.ColumnCount = 2
.ListBox1.ColumnWidths = "20;0"
End With
With Worksheets("Sheet1")
For Each cell In .Range("A15:A25")
Set rng = cell.MergeArea
If rng(1, 1).Address = cell(1, 1).Address Then
If Not IsEmpty(rng(1, 1)) Then
UserForm1.ListBox1.AddItem rng(1, 1).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) = _
.Name & "!" & rng(1, 1).Address(0, 0)

End If
End If
Next cell
End With
For i = 2 To 3
With Worksheets(i)
For Each cell In .Range("A3:A45")
Set rng = cell.MergeArea
If rng(1, 1).Address = cell(1, 1).Address Then
If Not IsEmpty(rng(1, 1)) Then
UserForm1.ListBox1.AddItem rng(1, 1).Value
UserForm1.ListBox1.List( _
UserForm1.ListBox1.ListCount - 1, 1) = _
.Name & "!" & rng(1, 1).Address(0, 0)
End If
End If
Next cell
End With
Next i
End Sub

Private Sub ListBox1_Click()
Dim rng As Range
With UserForm1.ListBox1
sStr = .List(.ListIndex, 1)
End With
On Error Resume Next
Set rng = Range(sStr)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Parent.Activate
rng.Select
End If
End Sub

--
Regards,
Tom Ogilvy



"CAA " wrote in message
Sub

I have a listbox additem question which i cannot resolve with my limited
experience.
I have a form which is used to add details to worksheets, names in the
first column, i would like to add to a list when you click a button.
However not all the rows are used on any sheet and they don't have a
common reference. say, a15:a25 & a3:a45 although from sheet 2 onwards
they are the same location. The other frustration is that two rows are
merged at column a:a, this i can manage usting the step2 in a for next
loop.

I guess my problem is having the list populate if any rows contain a
value, if a sheet doesn't exist is there an if exist command? also how
can i click the list item and make that row current?

Thanks for looking
CAA


---
Message posted from http://www.ExcelForum.com/