Object variable not set error

[HimanshuK]

HI There,

I am trying the below code to use a dynamic range for lastfillrange in a combobox. It gives an error of Object variable not set

dont know what happened!! Can anyone help?

Private Sub ComboBox1_Change()
Dim lastrow As Long
Dim dyrange As Range
lastrow = Sheets("BG").Range("E7").End(xlUp).Row
dyrange = Sheets("BG").Range("E7:E" & lastrow)
ComboBox1.ListFillRange = dyrange


End Sub

 

[Craggs82]

you need to set a range object

set dyrange = Sheets("BG").Range("E7:E" & lastrow)

 

[HimanshuK]

Hi,
Thanks for your response. It really helped!!!

But the code is not running

Below is the code am using, with minor changes

Option Explicit
Private Sub ComboBox1_Change()
Dim lastrow As Long
Dim dyrange As Range
Dim sht As Worksheet
Set sht = Worksheets("BG")
With sht
lastrow = .Range("E7").End(xlUp).Row
Set dyrange = .Range("E7:E" & lastrow)
End With


ComboBox1.ListFillRange = "=dyrange"


End Sub

 

[Norie]

Where is the combobox located?

 

[HimanshuK]

Combobox is in sheet - Input and data is on sheet BG

 

[Norie]

Put this in the module of the sheet (Input?) that has the combobox on it.

Private Sub Worksheet_Activate()
Dim lastrow As Long
Dim dyrange As Range

    lastrow = Sheets("BG").Range("E" & Rows.Count).End(xlUp).Row
    Set dyrange = Sheets("BG").Range("E7:E" & lastrow)
    ComboBox1.List = dyrange.Value

End Sub

 

[HimanshuK]

Hi Norrie thanks for your response. This code gives a run-time error 424 - object required and marks "ComboBox1.List = dyrange.Value" yellow

 

[Norie]

Where exactly did you put the code?

Also, how did you create the combobox?

 

[HimanshuK]

I put the code in the module of sheet (Input), the sheet has the combobox. I used activex controls to make the combobox.

I just used the comboboxlist script from your code with the initial code i was writing and it worked.

Thanks for your help

 

[Norie]

What code did you end up with?