How to set TextBox ControlSource to a hidden worksheet NOT active worksheet?

[tearsmith]

Hello All! A bit of help please?

I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.

I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.

The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.

Can someone tell me how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?

Thank you,
TearSmith

 

[Gavin T]

Did you try:

TextBox1.ControlSource = "[COLOR=#ff0000][B]Sheet26[/B][/COLOR]!E4"

Put your actual sheet name instead of Sheet26.

 

[tearsmith]

Gavin,

I tried it, but not sure if I put it in the correct place. Where do I put that code, specifically? Do I put it under UserForm_Initialize () or where?

Thank you in advance. Tearnie

 

[Gavin T]

Yes, you would put that in UserForm_Initialize().