Textbox ControlSource property

[GMO]

Hi all!
I have a (hopefully)simple question about the textbox ControlSource property.

I have a UserForm which includes a textbox that I want to be bound to a cell on an Excel worksheet other than the current active sheet.

I have been unable to find a reference or example that shows the proper syntax for specifying this in the ControlSource property box.

Any help will be greatly appreciated!

 

[BungleNZ]

I may have got the wrong idea with what your after, but could you just use something like the following (obviously just changing the textbox, sheet and range to your required ones)

textbox1.text = worksheets("Sheet1").range("A1").value
[code/]
 
 
[QUOTE="GMO, post: 1832852, member: 110740"]Hi all!
I have a (hopefully)simple question about the textbox ControlSource property.
 
I have a UserForm which includes a textbox that I want to be bound to a cell on an Excel worksheet other than the current active sheet.
 
I have been unable to find a reference or example that shows the proper syntax for specifying this in the ControlSource property box.
 
Any help will be greatly appreciated![/quote]

 

[GMO]

I may have got the wrong idea with what your after, but could you just use something like the following (obviously just changing the textbox, sheet and range to your required ones)

Not exactly what I was after....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I know that the code you have suggested would work if written within a procedure, but what I'm trying to do is enter a sheet and cell reference directly into the TextBox ControlSource property box that would automate the saving and recall of text box data in a completely transparent way.<o></o>
<o></o>
For example;<o></o>
If I enter "A1" into the ControlSource property box, data typed into the text box is automatically transferred to cell A1 of the currently active worksheet. Conversely, when the UserForm is opened, the data that is in cell A1 is displayed in the text box automatically.<o></o>
<o></o>
What I can't figure out is the proper syntax for specifying a cell on a worksheet other than the one that is currently active that I can enter into the TextBox "ControlSource" box.<o></o>
<o></o>
I hope that's a little more clear....I'm new at this!<o></o>
<o></o>

 

[BungleNZ]

I'm pretty new at this as well, so I may still be way off what you're after, but will this code work (I know it's not through the controlsource property - I'm not sure how to do that.)


On loading userform, textbox1 will be filled with data from Sheet1, Range A1:

userform_initialize()
textbox1.text = worksheets("Sheet1").range("A1")
end sub
[code/]
 
On changing textbox entry, change text in A1:
[code]
textbox1_change()
worksheets("Sheet1").range("A1") = textbox1.text
end sub
[code/]
 
 
 
[QUOTE="GMO, post: 1832875, member: 110740"][COLOR=black][FONT=Verdana]Not exactly what I was after....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]I know that the code you have suggested would work if written within a procedure, but what I'm trying to do is enter a sheet and cell reference [U]directly[/U] into the TextBox ControlSource property box that would automate the saving and recall of text box data in a completely transparent way.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For example;<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If I enter "A1" into the ControlSource property box, data typed into the text box is automatically transferred to cell A1 of the currently active worksheet. Conversely, when the UserForm is opened, the data that is in cell A1 is displayed in the text box automatically.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]What I can't figure out is the proper syntax for specifying a cell on a worksheet other than the one that is currently active that I can enter into the TextBox "ControlSource" box.<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]I hope that's a little more clear....I'm new at this!<o:p></o:p>[/FONT][/COLOR]
<o:p></o:p>[/quote]

 

[BungleNZ]

If you want to set the controlsource to another worksheet, it looks like all you need to do is type like the following example (of course, this is not going to help with putting text from the textbox into the cell however, you will need to do this with code):

Sheet1!A1

 

[GMO]

If you want to set the controlsource to another worksheet, it looks like all you need to do is type like the following example (of course, this is not going to help with putting text from the textbox into the cell however, you will need to do this with code):

Sheet1!A1

You are exactly right!

Thanks alot, I couldn't find the proper syntax anywhere!

 

[GMO]

If you want to set the controlsource to another worksheet, it looks like all you need to do is type like the following example (of course, this is not going to help with putting text from the textbox into the cell however, you will need to do this with code):

Sheet1!A1

By the way, I neglected to explain, when the TextBox is specified using a ControlSource, the data that is entered by the user is saved in the referenced cell automatically on a change event (clicking on another control, closing the UserForm, etc.).
When the UserForm is opened again, the data in the TextBox bound cell ("ControlSource") is displayed in the TextBox window. This works even if the data in the bound cell has been changed either manually or by another procedure.
This seems very useful in that it doesen't require any additional code to initialize the textbox data!...

 

[Randallh]

I am glad that I found this thread. This is perfect for what I am doing however, what if I am trying to reference a sheet with a space in the name like 'sheets together'!A1. It is obvious that I shouldn't name sheets this way, but I have already built a complex excel program with multiple sheets and coding that it would take me days to fix if I made all the names without spaces. I tried using that reference, but alas it comes up with errors. Any help would be appreciated. Thanks.

 

[SUPERDAD]

i have userform which contains textbox 1 as "employee ID" and textbox 2 as the "name of the employee". I want to display in textbox 2 the name as i type in textbox 1 the ID number dynamically. The userform is link on an excel table column 1 and column 2 for TB1 and TB 2 respectively.

Kindly help me how to code above .

many thanks

 

[Jon Peltier]

i have userform which contains textbox 1 as "employee ID" and textbox 2 as the "name of the employee". I want to display in textbox 2 the name as i type in textbox 1 the ID number dynamically. The userform is link on an excel table column 1 and column 2 for TB1 and TB 2 respectively.

Kindly help me how to code above .

many thanks

Your question is not relevant to the thread you posted it in. You should start your own new thread with your question.