Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox problem
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox problem
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox problem
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 --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox problem
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. -- Regards, Tom Ogilvy "CAA " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox problem
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox Problem | Excel Discussion (Misc queries) | |||
Listbox problem | Excel Discussion (Misc queries) | |||
ListBox problem | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Listbox problem | Excel Programming |