Edit Combo Box Properties on a Worksheet

[Izran]

I have a combo box, and I have code to generate the list I would like
in the combo box. This list is to be dynamic as it can be added to or
removed from. I am trying to develop some code that will enable me to
adjust the combo box's listrows and listfillrange. I can't seem to get
it to work. Here is the code I have written so far:


Sub Primary_Voltage_List_Generator()
'Generates the Primary Voltage Combo Box list

Dim lRow As Long

Dim lEnd As Long
Dim lCount As Long

Dim sh1 As Worksheet
Dim sh2 As Worksheet


Application.StatusBar = True
Application.StatusBar = "Generating Primary Voltage ComboBox List
...."
Application.ScreenUpdating = False


lRow = 5
lCount = 0
lEnd = 4

Set sh1 = Sheets("Box_Lists")
Set sh2 = Sheets("Home_Page")

sh1.Select


'Find End of Primary Voltage Listings
For lRow = 5 To 65536

If Cells(lRow, 9).Value = "" Then

lRow = 65536

End If

If Cells(lRow, 9).Value <> "" Then

lEnd = lEnd + 1
lCount = lCount + 1

End If

Next lRow


sh2.Select

With Primary_Voltage_ComboBox

.ListRows = lCount + 1
.ListFillRange = sh1.Range((Cells(5, 9)), (Cells(lEnd, 9)))

End With

Application.ScreenUpdating = True
Application.StatusBar = False

End Sub




Any help will be greatly appreciated.

Best Regards,
MPManzi

 

[Tom Ogilvy]

.ListFillRange = sh1.Range(sh1.Cells(5, 9), sh1.Cells(lEnd,
9)).Address(external:=True)

and easier method might be

Sub FixRange()
Dim sh1 as Worksheet
Dim sh2 as Worksheet
Dim rng as Range, rng1 as Range
Set sh1 = Sheets("Box_Lists")
Set sh2 = Sheets("Home_Page")

set rng = sh1.cells(rows.count,9).end(xlup)
set rng1 = sh1.Range(sh1.Cells(5,9),rng).Address(External:=True)
With sh2.Primary_Voltage_Combobox
.ListFillRange = rng1.Address(1,1,xlA1,True)
.ListRows = rng1.count
End With
End Sub