change cell value on text box change

[asylum]

Hi -

I have the below VB on a sheet which forces a page to recalculate itself when a text box is changed.

I am trying to force the value of cell C21 to be 1 when the text box changes also, I added the thrid line but this does not seem to work, this must be very easy, what am i missing?

thanks

Andy

Private Sub TextBox1_Change()
ActiveSheet.Calculate
Range(c21).Value = "1"

End Sub

 

[Richard Schollar]

Private Sub TextBox1_Change()
ActiveSheet.Calculate
Range("c21").Value = "1"

End Sub

 

[asylum]

Hi Thaks for that, I spotte dthe speech marks, and have also included the sheet name, but it still wont work... perplexing me now, the active sheet calculate bit works but the other doesn't!

Private Sub TextBox1_Change()
ActiveSheet.Calculate
Sheet("lookup").Range("c21").Value = "1"
End Sub

 

[Richard Schollar]

Is the Textbox on the sheet which you are trying to change? I know this may seem like an obvious question, but how do you know the calculate is working but the Range() = 1 isn't?

 

[asylum]

HI Richard,

Many thanks for the pointer! - that sorted it out nicely, oddly i ha sthe same bit of code on two sheet, but of course the text box was on the other one! I shall go put on my dunces cap!

Cheers

Andy