Excel and vba simple q tough a?

[Donkeygirl]

I need to set the control source property for a text box on a form being used in excel 97. I need the value to reference a sheet's cell within the workbook. What is a valid property value for sheet1 cell E6?
This should be so easy, but everytime I try to set the control source it says invalid property value.
hope someone can help
ps I am working on this in the editor if you are trying to figure out where I am

 

[DarkSun]

Try this.


=Sheet1!E6

 

[Donkeygirl]

Thanks, I tried that, invalid property value
anyone else?
lol

 

[SimonDavis]

I think you might be working backwards.

I just had a look at this problem, and when I set the control source refering to a cell (In the object properties, I put the cell reference as $d$10), the textbox value is placed in the cell, rather than the cell value being placed in the text box.

Unless I've misunderstood your post, I think you want to take a value from a sheet, and either place it into, or calculate a value to be placed into a textbox on a form.

The way to do that would probably be to create an event when the form loads, which reads the cell value, then places it either to the textbox, or to a variable which you can then manipulate and place the result to the textbox.

Does that make sense?

The syntax of the code you will need is something like this:

To just place the value in the cell into the textbox:

formname.TextBox.Value = Range("a6&quot.Value

To place the cell value to a variable:

Dim celval As Variant
celval = Range("a6&quot.Value

And to write that back to a text box or another cell:

formname.TextBox.Value = celval

or

Range("b2&quot.Value = celval

(I've used celval as a variable name - be careful not to call your variables by names that vb uses, such as 'time' 'date' etc. You would find for example that sng 'value' as a variable name would probably cause problems)

You can also make calculations on the variable - just declare another one for the result etc.

 

[DarkSun]

Sorry didn't try it over multiple sheets try this:

='sheet1'!E6

 

[Donkeygirl]

Thank you so much!!!! It worked!
I used the formname.textbox=value in the activate of the form. Thank you so much, Simon Davis! I have been looking for this for way too long!
You are awesome