Combobox change event assistance

[robertmwaring2]

Hello all,

I am running into an issue I cannot get around.
I have a form with a combobox. when the selection of the combobox changes, i want the value of the combobox to be placed in an cell address that is the value of a set cell. i realize i probably didnt word that right so here is an example. On Sheet3, the value of cell V* will read as follows:

'SHEET 4'!$AD$15

The value of V8 will change regularly (as it actually contains a formula to calculate the address it displays) and could refernce another sheet within the same workbook entirely, and even a different column or row. The location that cell V8 references ('SHEET 4'!$AD$15) will have data in it and I need to replace it with the value of the combobox when a selection is made (change event).

I have tried so many iterations of the following code, but get I get an error every time and do not know what I am doing wrong. The error I get is "Runtime error 91 : Object variable or with block variable no set".

I first tried number one and thought perhaps it was too basic, then tried number 2, then 3, and cannot seem to accomplish what I am looking for, Can anyone offer me a pointer or assistance?

1) Sheet3.Range(Sheet3..Range("V8").Value) = CHOICELIST1.Value
I also tried using: Sheets("Sheet3").range(Sheets("Sheet3").range("v8").value=choicelist1.value
I also tried using just: range(Sheets("Sheet3").range("v8").value=choicelist1.value
as well as: Sheet3.range(Sheet3.range("v8").value=choicelist1.value
and: range(Sheet3.range("v8").value=choicelist1.value

2) Dim WS As Worksheet
WS = Sheet3
Range(WS.Range("V8").Value) = CHOICELIST1.Value
I also tried using ws.range(ws.range("v8").value=choicelist1.value on the last line

3) Dim WS As Worksheet
WS = Worksheets("Sheet3")
Range(WS.Range("V8").Value) = CHOICELIST1.Value
I also tried using ws.range(ws.range("v8").value=choicelist1.value on the last line

Any help would be greatly appreciated and I thank you in advance!

 

[robertmwaring2]

I'm glad it works.

Um, thank you - though I'm still in the same place I started essentially. I guess my ability to explain the scenario is the problem. I do appreciate your time though. I'll keep researching to figure it out. Have a great evening!

 

[Akuini]

Could you explain in more detail what you're trying to do by using an example?
You select an item in the combobox then what?
You want to sent the item to the cell which is pointed in Sheet3.Range("V8") (i.e range 'SHEET 4'!$AD$15)?
Then try:

Sub try1()
Dim c As Range
Set c = Evaluate(Sheets("Sheet3").Range("$V$8").Value)
c.Value = CHOICELIST1.Value
End Sub

it will change 'SHEET 4'!$AD$15 value to the CHOICELIST1.Value

 

[robertmwaring2]

Could you explain in more detail what you're trying to do by using an example?
You select an item in the combobox then what?
You want to sent the item to the cell which is pointed in Sheet3.Range("V8") (i.e range 'SHEET 4'!$AD$15)?
Then try:

Sub try1()
Dim c As Range
Set c = Evaluate(Sheets("Sheet3").Range("$V$8").Value)
c.Value = CHOICELIST1.Value
End Sub

it will change 'SHEET 4'!$AD$15 value to the CHOICELIST1.Value

That is precisely what I was trying to accomplish. I truly thank you! I honestly didn't mean to be so difficult in trying to get the point across. I did my best to explain it, but I guess i added too many details. Again, thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.