Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a listbox
Hi
I have the following code that creates a list of Months that just happen to correspond to Tab names in my sheet. This works fine. However, I have a ListBox1_Click routine that when I select the particular date it lists all the data from 2 of the columns in that tab. It all works OK if I have 3 or more lines worth of data (not including header) but if I only have one line of data, not including header then it just hangs. When I do a Funtion Break it highlights the "Next" which I guess means it is stuck in a loop. I have already had to add the Msg "Month Empty" to stop it throwing an error then. Admittidly I did copy this code from somewhere else and tried to adapt it. Any help would be appreciated. If EditListBox1.ListIndex < -1 Then EditListBox2.Clear Set sh = Worksheets(EditListBox1.Value) If sh.Cells(2, 1).Value < "" Then Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) For Each cell In rng EditListBox2.AddItem cell(1, 1).Value & " " & cell(1, 3).Value Next Else MsgBox "Month is empty" End If End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a listbox
hi
here is the line i think is causing you problems. If sh.Cells(2, 1).Value < "" Then Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) if you only have 1 line in your months then this will select the entire column. your for next loop is then trying to add the entire column to your list box. (i think....i didn't test that.) so you need to qualify how many line you do have before activating your loop. try something like this. (not to pretty but it works.) Dim sh As Worksheet Set sh = Sheets("sheet1") If sh.Cells(2, 1) = "" Then MsgBox "Month is empty" Else If sh.Cells(3, 1) = "" And _ sh.Cells(2, 1) < "" Then Set rng = sh.Cells(2, 1) rng.Select 'test purposes only Else If sh.Cells(2, 1).Value < "" And _ sh.Cells(3, 1) < "" Then Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) rng.Select 'test purposes only 'For Each cell In rng ' EditListBox2.AddItem cell(1, 1).Value & " " & cell(1, 3).Value 'Next End If End If End If regards FSt1 "Steve" wrote: Hi I have the following code that creates a list of Months that just happen to correspond to Tab names in my sheet. This works fine. However, I have a ListBox1_Click routine that when I select the particular date it lists all the data from 2 of the columns in that tab. It all works OK if I have 3 or more lines worth of data (not including header) but if I only have one line of data, not including header then it just hangs. When I do a Funtion Break it highlights the "Next" which I guess means it is stuck in a loop. I have already had to add the Msg "Month Empty" to stop it throwing an error then. Admittidly I did copy this code from somewhere else and tried to adapt it. Any help would be appreciated. If EditListBox1.ListIndex < -1 Then EditListBox2.Clear Set sh = Worksheets(EditListBox1.Value) If sh.Cells(2, 1).Value < "" Then Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) For Each cell In rng EditListBox2.AddItem cell(1, 1).Value & " " & cell(1, 3).Value Next Else MsgBox "Month is empty" End If End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
Checking range of cells for entry then checking for total | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Checking Listbox Items | Excel Programming |