ComboBox Values

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
J

Jim Berglund

I'm truing to change the values in a drop-down list box selector, but the following generates a Runtime Error 424 on the ComboBox line(s).

Is is possible to do what I want? What is the correct syntax, please?
______________________________________________________________________
If Cells(5, 111).Value = "" Then
p = Cells(2, 114).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112), Cells(p, 112))
End With
GoTo Equipment
Else
p = Cells(2, 118).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112), Cells(p, 112))
End With
End If
_____________________________________

Thanks,
Jim Berglund
 
is workingsheet a variable holding a reference to a worksheet?

for example

Dim workingSheet as Worksheet
set workingsheet = Worksheets("Sheet1")

if so you can do

workingSheet.OleObjects("Combobox3").ListfillRange = _
Range(Cells(5, 112), Cells(p, 112))

I would prefer to see a qualification for the sheet holding the range as
well unless that sheet is the activesheet

set sh = Worksheets("Data")

workingSheet.OleObjects("Combobox3").ListfillRange = _
sh.Range(sh.Cells(5, 112), sh.Cells(p, 112))

--
Regards,
Tom Ogilvy

"Jim Berglund" <berglunj@ric
her.ca> wrote in message I'm truing to change the values in a drop-down list box selector, but the
following generates a Runtime Error 424 on the ComboBox line(s).

Is is possible to do what I want? What is the correct syntax, please?
______________________________________________________________________
If Cells(5, 111).Value = "" Then
p = Cells(2, 114).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112),
Cells(p, 112))
End With
GoTo Equipment
Else
p = Cells(2, 118).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112),
Cells(p, 112))
End With
End If
_____________________________________

Thanks,
Jim Berglund
 
I am entering this subroutine from another sub, where the WorkingSheet has
been defined
i.e
Private Sub CommandButton4_Click() 'Load Files
...
[Module1].CreateLists "Analyzer"
....
End sub
_________________________________
Sub CreateLists(workingSheet)

Dim i, j, k, p, numberofRows As Integer
....
(the aforementioned code)
....
End Sub

I tried your suggestion - both ways, but ran into additonal errors.
 
Tom, I have called a subroutine from a subroutine as follows:

Private Sub CommandButton4_Click() 'Load Files
Dim i, j, k, x, y, numberofRows As Integer, numberofColumns, nextCol As
Integer
Dim workingSheet As Worksheet

...
[Module1].CreateLists "Analyzer"
End Sub
_______________________________________________________

Sub CreateLists(workingSheet)

Dim i, j, k, p, numberofRows As Integer

Set ws = Worksheets("Analyzer")
Application.ScreenUpdating = False

ws.Activate

ActiveSheet.Range("CX13:DD23").Select
Selection.ClearContents
ActiveSheet.Range("DG5").Select
If Cells(5, 111).Value = "" Then
p = Cells(2, 114).Value 'Number of non-blank cells in the EqptType
Column
workingSheet.OLEObjects("Combobox3").ListFillRange = _
ws.Range(ws.Cells(5, 112), ws.Cells(p, 112))
GoTo Equipment
Else
p = Cells(2, 118).Value 'Number of non-blank cells in the
EqptType2 Column
workingSheet.OLEObjects("Combobox3").ListFillRange = _
ws.Range(ws.Cells(5, 116), ws.Cells(p, 116))
End If
...
End sub

Now I'm running into a 424 "Object Required" error.

Have I interpreted your suggestions correctly? Do you have additional
advice, please?
 
Private Sub CommandButton4_Click() 'Load Files
...
[Module1].CreateLists Worksheets("Analyzer")
....
End sub
_________________________________
Sub CreateLists(workingSheet as Worksheet)

Dim i, j, k, p, numberofRows As Integer
....

....
End Sub

as declared i, j, k, p are variants, not integers, not that it probably
makes any difference.

--
Regards,
Tom Ogilvy


Jim Berglund said:
I am entering this subroutine from another sub, where the WorkingSheet has
been defined
i.e
Private Sub CommandButton4_Click() 'Load Files
...
[Module1].CreateLists "Analyzer"
...
End sub
_________________________________
Sub CreateLists(workingSheet)

Dim i, j, k, p, numberofRows As Integer
...
(the aforementioned code)
...
End Sub

I tried your suggestion - both ways, but ran into additonal errors.

Tom Ogilvy said:
is workingsheet a variable holding a reference to a > set workingsheet = Worksheets("Sheet1")

if so you can do

workingSheet.OleObjects("Combobox3").ListfillRange = _
Range(Cells(5, 112), Cells(p, 112))

I would prefer to see a qualification for the sheet holding the range as
well unless that sheet is the activesheet

set sh = Worksheets("Data")

workingSheet.OleObjects("Combobox3").ListfillRange = _
sh.Range(sh.Cells(5, 112), sh.Cells(p, 112))

--
Regards,
Tom Ogilvy

"Jim Berglund" <berglunj@ric
her.ca> wrote in message I'm truing to change the values in a drop-down list box selector, but the
following generates a Runtime Error 424 on the ComboBox line(s).

Is is possible to do what I want? What is the correct syntax, please?
______________________________________________________________________
If Cells(5, 111).Value = "" Then
p = Cells(2, 114).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112),
Cells(p, 112))
End With
GoTo Equipment
Else
p = Cells(2, 118).Value
With WorkingSheet
ComboBox3.ControlFormat.ListFillRange = Range(Cells(5, 112),
Cells(p, 112))
End With
End If
_____________________________________

Thanks,
Jim Berglund
Click to expand...
Click to expand...
 
Back
Top