Set value based on combo box selection

[paulcraigdainty]

I've written the following routine however it's not working for some reason. Basically I want to set the value of AG3 to match the value of H7 on sheet 2 when number 1 is selected in the combo box.

Is there something wrong with my syntax?

Private Sub ComboBox1_Change()

If Me.ComboBox1.Value = "1" Then

Range["ag3"].Value = ("Sheet2")Range("h7")).Value


End If

End Sub

 

[boblarson]

Is the combo value a number or text? If a number, leave off the quotes: 1

 

[paulcraigdainty]

The combo value is a number. I have tried without quotes but still get the error.

 

[ecawilkinson]

hi Paul,
your syntax is all wrong. I'm surprised you did not get a syntax error. Also, which sheet is AG3, using Range("AG3") refers to the active sheet. Try this syntax, which will change cell AG3 of the currently active sheet to the value of cell H7 on Sheet2:

Range("AG3").value = Worksheets("Sheet2").Range("H7").value

Just a thought, is the active sheet protected?

What is the error message?

HTH,
Chris