Data from a textbox in a user form into a cell

[Bengt]

I am using a user form to get some data from the user.

Below is - essentially - the code. It is triggered when you click an OK-button on the form. The idea is that the code should compare the value that the user has entered into txtMyBox (if any) with the old (current) value that I have placed in a label called lblOldValue. If they are not the same, then the new value should be transferred to a range called MyRangeName:

If txtMyBox.Value <> lblOldValue.Caption Then
Range("MyRangeName").Value = txtMyBox.Value
End If

At first, everything seems to work. The new value is actually transferred into MyRangeName. The problem occurs when another cell than MyRangeName tries to use this data. What it gets then is the value, but as text. And since there are formulas who tries to do calculcation with whatever is in this other cell, a VALUE ERROR occurs.

I first used txtMyBox.Text, but I thought - hoped - that that was the problem, so I changed txtMyBox.Text to txtMyBox.Value, but to no avail.

If someone has the time to write a comment, it would be very appreciated.

Thanks

Bengt

 

[VoG]

Try

Range("MyRangeName").Value = Val(txtMyBox.Value)

 

[Bengt]

Hi Peter,
Thanks for your prompt answer. It seems that your suggestion works. I will do some more testing, Meanwhile, what is the difference really if you use the .text property or the .value property. One would assume that using val(.text) would have the same meaning as .value, but obviously now there is this third option with val(.value), so what is really the difference between val(.value) and .value?

Had it been simpler if I had used ControlSource instead do you think?

Bengt

 

[Stu Dapples]

Looking at your code (and I am no expert!!) it looks OK, a long shot but have you tried the formatting in the target cells? Not sure if this would make any difference but if the range is formatted as text and not either Genreal or Number....

 

[RoryA]

There is no difference for a textbox between the Text and Value properties. Using ControlSource would make no difference (and is a bad idea, in my opinion).

 

[VoG]

A textbox contains text, and text is returned whether you use the .Text or .Value property. To return a number you need to convert the text using Val().

 

[Bengt]

A textbox contains text, and text is returned whether you use the .Text or .Value property. To return a number you need to convert the text using Val().

Thanks to both Peter and Rory and Stu for your contributions. Still, it would be interesting to find out why there are both .text and .value if there is no difference. But I will use Val and be happy with that.

Bengt