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

 

[HimanshuK]

I am new to vba coding, so there's lot to learn...anyways thanks for your help.. It just shows additional lines after the complete list in drop down menu. I am trying to figure out a way to get rid of that

 

[Norie]

What code did you end up with and what's in column E?

 

[HimanshuK]

Below is the code
Private Sub ComboBox1_Change()
Dim lastrow As Long
Dim dyrange As Range
Dim dy_range As Name
Dim sht As Worksheet
Set sht = Worksheets("BG")
With sht
lastrow = .Range("E6").SpecialCells(xlCellTypeLastCell).Row
Set dyrange = .Range("E6:E" & lastrow)
End With
ComboBox1.List = dyrange.Value





End Sub

Column E has names of project codes starting from cell E7

 

[Norie]

Where did you put this code and why are you using the combobox's own change event to populate it?

Does column E only have data?

 

[HimanshuK]

Sorry, for replying late

Yes, column E only has data, starting from E7

I have put the code in the Input worksheet itself

Should i make a separate macro for populating the combobox? what do you suggest

 

[Norie]

The code should be in the worksheet module but it shouldn't really be in the combox's own Change event, it would be better in the sheet's Activate event.

By the way, when you use the exact code I posted earlier do you get additional (presumably blank) lines after the list of values?

 

[HimanshuK]

The code you gave earlier didn't worked actually. This code gave a run-time error 424 - object required and marks "ComboBox1.List = dyrange.Value" yellow

So i picked few things from yours to run the one I have right now

This one gives additional blank lines after the list. Is there a solution to that?

 

[Norie]

I'm confused both your code and mine have this exact same line,

ComboBox1.List = dyrange.Value

but you say in my code that line causes an error?

Where exactly did you put the code I suggested?

 

[HimanshuK]

Hi Norrie,

I was not able to understand that either, it is SAME!. I put the code where you told me to do so. Anyways, it is working and thanks for your help. Currently I am trying to do this for multiple comboboxes, like I have 3, and I have been able to populate all three of them using the above code as you mentioned (it has been really helpful).

I have declared dyrange public and placed it in input sheet module and further I have created three combobox change events an used the code in the input sheet as below-

Private Sub ComboBox1_Change()


ComboBox1.List = dyrange.Value

End Sub




Private Sub ComboBox2_Change()


ComboBox2.List = dyrange.Value


End Sub


Private Sub ComboBox3_Change()


ComboBox3.List = dyrange.Value


End Sub


But each time I select any one value in combobox 1. it gets same for combobox 2 and 3. I am using excel 2013 (64- bit)

 

[HimanshuK]

Below is the code in the module for populating-

Option Explicit
Public dyrange As Range




Public Sub poplist()


Dim lastrow As Long


Dim dy_range As Name
Dim sht As Worksheet
Set sht = Worksheets("BG")
With sht
lastrow = .Range("E6").SpecialCells(xlCellTypeLastCell).Row
Set dyrange = .Range("E6:E" & lastrow)
End With


End Sub