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

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