listbox problem

  • Thread starter Thread starter CAA
  • Start date Start date
C

CAA

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
 
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
 
Your a star Tom.

I had managed to cobble together something that seems to work, your
reply gave me a few ideas though which is always good, such as, With
Worksheets(i), i didn't realise that was acceptable. easier than the
sheets("sheet" & i) approach.

Thing i'm not clear on is the, Set rng = cell.MergeArea, what does this
do?

I read quite a lot of your replies to posts and your approach never
fails to teach me something. I'm a quite nosey and wonder if this is
your job?
Have you wrote a book perhaps?

Regards
CAA
 
If A3 and A4 are merged

Range("A3").MergeArea would return a reference to Range("A3:A4")

Since the value for merged cells is stored in the upper left cell, I use
that information to see if I am working with the upper left cell.

If the cell is Merged, then Range("A3").MergeArea will just return
Range("A3"), so it causes no problem.

No books, not my job.
 
Thankyou Tom, i've jotted that one down on yet another scrap of paper.

No book!? with the amount of help you have given over the last yea
alone, would probably make a book.

cut&paste VBA for the clueless. 9.99 (almost a vision)

Thanks again
CA
 
Back
Top