Userform Object Linked To Cell Value

[APElliott]

Help,

I want to display a cell value on some kind of object on a userform - can this be done, if so how?

Cheers

 

[xlbo]

in a textbox, you would use
textbox1.value = sheets("Sheetname&quot.range("A1&quot.text 'or value
HTH Rgds
~Geoff~

 

[APElliott]

Thanks Geoff,

I'm new to all this, so bare with me please.

I've tried this and got nothing displayed in the text box.

Private Sub TextBox1_Change()
TextBox1.Value = Sheets(ActiveWorkSheet).Range("f3&quot.Value
End Sub

Also tried it with a specific worksheet name!

Any suggestions

Thank Andrew

 

[xlbo]

It all depends on whether this value is to be changed or whether you just want to show a static piece of text / value

To show a piece of static text / value, go to the controlsource property and enter
Sheet1!F3
for example - where range F3 on Sheets Sheet1 is the data source. If your sheet name has spaces in it, you'll need to use
'Name With Spaces'!F3

Other than that, you shouldn't use the code in the textbox change event - it should be in the form load / activate event
also - you can't use the Activeworksheet syntax as there is no active worksheet - the form is active. Use
sheets("Sheetname&quot.range("F3&quot
HTH Rgds
~Geoff~

 

[APElliott]

Thanks Geoff,

I'm affraid your going too fast for me.

Yes I don't want to edit the text, but i do want the value to = the cell value of the sheet in use.

Tried entering controlsource property but got the following error message "Could not set the ControlSource property. Invalid property value".

Not too sure about the placing of the code. I could do with a bit more help if possible

Cheers Andrew

 

[xlbo]

If you just want the static value, you don't need any code.
To set the controlsource property, highlight the textbox
Go to the properties window
In the box next to Controlsource, enter
Sheetname!F3

Substitute whatever your sheet is called for "Sheetname"

If the name of the sheet has spaces in it eg "Master Sheet"
you will need to emclose the sheet name in ' 'ie
'Master Sheet'!F3
HTH Rgds
~Geoff~

 

[APElliott]

Almost there - i think

My first sheet is called 1 so have enter 1!F3 and the value is now displayed in the text box.

But what i want to do is when i select my next sheet, 2, i want this sheet f3 value to be display.

Thanks

Andrew

 

[xlbo]

Right - ok - becomes clear now

This needs to go in the Form ACTIVATE event (right click on form, choose view code, use right side drop down to choose activate)

'Private Sub UserForm_Activate()
TextBox1.ControlSource = ActiveSheet.Name & "!F3"
'End Sub

Replace Textbox1 with whatever you have called your textbox

HTH Rgds
~Geoff~

 

[APElliott]

Cheers Geoff

I bow to your superior knowledge.

Thankyou very much

Andrew