Userform ListBox populated from range error if only 1 value

[RobbieC]

Hi there, I have a userform with a listbox which is populated from a range on another sheet.

I can get it all to work using:

Private Sub UserForm_Initialize()
    
  Dim ListRange As Range
  Set ListRange = Worksheets("Sheet7").Range("A1:A100")
[I][B]  If ListRange.Count = 1 Then[/B][/I]
[I][B]    ListBox1.AddItem ListRange.Value[/B][/I]
  Else
    ListBox1.List = Worksheets("Sheet7").Range("A1").CurrentRegion.Value
  End If


End Sub

but if there is only 1 value in my range (which is likely to happen), it give me a 381 error...

I can't see where I'm going wrong

If you can point me in the right direction, that'd be brilliant. Thanks

 

[Logit]

What happens if you change this :

Private Sub UserForm_Initialize()
    
  Dim ListRange As Range
  Set ListRange = Worksheets("Sheet7").Range("A1:A100")
  If ListRange.Count = 1 Then
    ListBox1.AddItem ListRange.Value
  Else
    ListBox1.List = Worksheets("Sheet7").Range("A1").CurrentRegion.Value
  End If




End Sub

To this :

Private Sub UserForm_Initialize()
    
  Dim ListRange As Range
  Set ListRange = Worksheets("Sheet7").Range("A1:A100")
 
  ListBox1.List = Worksheets("Sheet7").Range("A1").CurrentRegion.Value

  'or not the above but just this :   ListBox1.AddItem ListRange.Value






End Sub

 

[MickG]

This is one possible way:-

[COLOR="Navy"]Sub[/COLOR] MG26Feb15
[COLOR="Navy"]Dim[/COLOR] ListRange [COLOR="Navy"]As[/COLOR] Range
  ListBox1.Clear
  [COLOR="Navy"]Set[/COLOR] ListRange = Worksheets("Sheet7").Range("A1:A100")
    [COLOR="Navy"]If[/COLOR] Application.CountA(ListRange) = 1 [COLOR="Navy"]Then[/COLOR]
        ListBox1.AddItem ListRange.SpecialCells(xlCellTypeConstants).Value
    [COLOR="Navy"]Else[/COLOR]
        ListBox1.List = Worksheets("Sheet7").Range("A1").CurrentRegion.Value
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RobbieC]

Thanks Logit. Unfortunately, that gives me a whole new error:

Run-time error'-2147352571 (80020005)': Type mismatch

It also has stopped working if there are more than one value in the column (which was working before) - with the same error...

 

[RobbieC]

Brilliant Mick - that worked a treat!

Although I have no idea what ListRange.SpecialCells(xlCellTypeConstants).Value actually does, but it works!

Thanks very much (again)

 

[Logit]

If there is only one item in the range, will it always be in a specific cell ? If so, can you reference that one cell instead of the whole range ?

 

[RobbieC]

Thanks Logit, Micks solution here worked:

[COLOR=Navy]Sub[/COLOR] MG26Feb15
[COLOR=Navy]Dim[/COLOR] ListRange [COLOR=Navy]As[/COLOR] Range
  ListBox1.Clear
  [COLOR=Navy]Set[/COLOR] ListRange = Worksheets("Sheet7").Range("A1:A100")
    [COLOR=Navy]If[/COLOR] Application.CountA(ListRange) = 1 [COLOR=Navy]Then[/COLOR]
        ListBox1.AddItem ListRange.SpecialCells(xlCellTypeConstants).Value
    [COLOR=Navy]Else[/COLOR]
        ListBox1.List = Worksheets("Sheet7").Range("A1").CurrentRegion.Value
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

It now loads in if there is 1 or multiple values

 

[Logit]

Great !