Getting "object variable or with variable not set for combobox

[Caveman1964]

I have tried to resolve this through threads and videos but can't get it to work. My understanding of set and objects are not there yet.
I am getting the error "object variable or with variable not set when I click the combobox selection to put in cell.
Here is my code and would appreciate any help and guidence.
My task I am wanting to do is,
Click any one of 3 cells, a form pop up with a combobox with selections, just click the selection and it goes in the active cell and form disappears.


Private Sub ComboBox1_Change()
Dim selectedOption As String
selectedOption = ComboBox1.Value

' Get the active cell
Dim activeCell As Range
Set activeCell = activeCell

' Place the selected option into the active cell
activeCell.Value = selectedOption
End Sub

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Apple"
.AddItem "Orange"
.AddItem "Pear"
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
' Show the user form
UserForm1.Show
End If
End Sub

 

[Joe4]

I am not sure if it is the only problem with your code, but NEVER, EVER do this!

Dim activeCell As Range
Set activeCell = activeCell

You should NEVER use reserved words (words already used by Excel VBA for other things, like methods, properties, and objects) for the name of your variables, procedures, and functions.

"ActiveCell" is a pre-defined default range object in VBA, referring to the active cell on your sheet.
The issue is that if you create a variable named "activecell" (case does not matter) like you did, then in your code whenever you reference "activecell", Excel cannot tell if you mean the default "ActiveCell", or your variable. This ambiguity can cause errors and/or unexpected results.

Choose a different name for your variable.

Or if you are just trying to assign it to whatever actually is the activecell, get rid of the whole variable declaration and assignment, so you are just left with this:

Private Sub ComboBox1_Change()
Dim selectedOption As String
selectedOption = ComboBox1.Value

' Place the selected option into the active cell
ActiveCell.Value = selectedOption

End Sub

(there is no need to "get" the active cell, by default "ActiveCell" is the active cell!)

 

[bobsan42]

Private Sub ComboBox1_Change()
Dim selectedOption As String
selectedOption = ComboBox1.Value

' Place the selected option into the active cell
activeCell.Value = selectedOption
End Sub

activeCell is already an predefined object/class. So you do not need to try to set it to itself.

 

[Caveman1964]

I am not sure if it is the only problem with your code, but NEVER, EVER do this!

Dim activeCell As Range
Set activeCell = activeCell

You should NEVER use reserved words (words already used by Excel VBA for other things, like methods, properties, and objects) for the name of your variables, procedures, and functions.

"ActiveCell" is a pre-defined default range object in VBA, referring to the active cell on your sheet.
The issue is that if you create a variable named "activecell" (case does not matter) like you did, then in your code whenever you reference "activecell", Excel cannot tell if you mean the default "ActiveCell", or your variable. This ambiguity can cause errors and/or unexpected results.

Choose a different name for your variable.

Or if you are just trying to assign it to whatever actually is the activecell, get rid of the whole variable declaration and assignment, so you are just left with this:

Private Sub ComboBox1_Change()
Dim selectedOption As String
selectedOption = ComboBox1.Value

' Place the selected option into the active cell
ActiveCell.Value = selectedOption

End Sub

(there is no need to "get" the active cell, by default "ActiveCell" is the active cell!)

Ah ok! thank you for advice!

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[Caveman1964]

Private Sub ComboBox1_Change()
Dim selectedOption As String
selectedOption = ComboBox1.Value

' Place the selected option into the active cell
activeCell.Value = selectedOption
End Sub

activeCell is already an predefined object/class. So you do not need to try to set it to itself.

Thank You!

 

[bobsan42]

you are welcome.