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!

 

[Akuini]

I tried something like this:

Sub a1160625a()
Dim c As Range

On Error Resume Next
Set c = Evaluate(Sheets("Sheet3").Range("A1").Value)
On Error GoTo 0

If Not c Is Nothing Then
    Debug.Print c.Address & " : " & c.Value
    'c.Value = CHOICELIST1.Value
Else
    Debug.Print "Can't find : " & Sheets("Sheet3").Range("A1").Value
End If

End Sub

Book1
	A
1	'SHEET 4'!$A$1
2
Sheet3

Book1
	A	B
1	YES
2
Sheet 4

If there is "SHEET 4" in the workbook, then debug.print result (in Immediate window ):
$A$1 : YES
if there is no "SHEET 4", then:
Can't find : 'SHEET 4'!$A$1

So try using Evaluate to get the range from value of V8.

 

[robertmwaring2]

I tried something like this:

Sub a1160625a()
Dim c As Range

On Error Resume Next
Set c = Evaluate(Sheets("Sheet3").Range("A1").Value)
On Error GoTo 0

If Not c Is Nothing Then
    Debug.Print c.Address & " : " & c.Value
    'c.Value = CHOICELIST1.Value
Else
    Debug.Print "Can't find : " & Sheets("Sheet3").Range("A1").Value
End If

End Sub

Book1
	A
1	'SHEET 4'!$A$1
2
Sheet3

Book1
	A	B
1	YES
2
Sheet 4

If there is "SHEET 4" in the workbook, then debug.print result (in Immediate window ):
$A$1 : YES
if there is no "SHEET 4", then:
Can't find : 'SHEET 4'!$A$1

So try using Evaluate to get the range from value of V8.

I'm not entirely sure this is what I was looking to accomplish, but as I am VERY new to this, I gave it a try and put it in the combobox click change event area and received this error:

 

[robertmwaring2]

I'm not entirely sure this is what I was looking to accomplish, but as I am VERY new to this, I gave it a try and put it in the combobox click change event area and received this error:

If I think of it in terms of formulas I am trying to accomplish something like the following:
Form.combobox.value=Indirect('SHEET3'!$V$8) if that makes sense

 

[Akuini]

If you try this (in code Mudule not userform module) what message do you get?

Sub try()
MsgBox Evaluate("'SHEET3'!$V$8")
End Sub

 

[robertmwaring2]

If you try this (in code Mudule not userform module) what message do you get?

Sub try()
MsgBox Evaluate("'SHEET3'!$V$8")
End Sub

'SHEET 4'!$AD$15

this is the exact address within the workbook that I need the value of the combobox to go into, replacing the text that exists in that location. Sorry if I am being unclear, Im not entirely sure how else to explain it.

And in regards to your question if there is a Sheet 4, there is. In fact there are 27 sheets, but 'SHEET 3'!$V$8 will ALWAYS provide me with the address that needs to be replaced no matter what sheet, column or row it is located in.

 

[robertmwaring2]

'SHEET 4'!$AD$15

this is the exact address within the workbook that I need the value of the combobox to go into, replacing the text that exists in that location. Sorry if I am being unclear, Im not entirely sure how else to explain it.

And in regards to your question if there is a Sheet 4, there is. In fact there are 27 sheets, but 'SHEET 3'!$V$8 will ALWAYS provide me with the address that needs to be replaced no matter what sheet, column or row it is located in.

There will NEVER be any more or less sheets in the workbook, and the names will not change, if that makes a difference. And
'SHEET 3'!$V$8 will NEVER be blank, the form containing the combobox (choicelist1) will ONLY be called if it is not blank and meets another criteria entirely. so no need to prepare for or work around that either.

 

[Akuini]

Ok, now try:

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

 

[robertmwaring2]

Ok, now try:

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

BREAKFAST MEAT appears in a message box. this is the value i need to replace with the value of the combobox from the form when a selection is made. It may be helpful to mention that while it reads Breakfast Meat for the current evaluation you provided, the next time the form is called, it may NOT be the same string of text it is replacing. It could be something different.

I appreciate your help with this

 

[Akuini]

I'm glad it works.