Linking user form text box to a cell that contains a formula

bisel

Board Regular
Joined
Jan 4, 2010
Messages
223
Office Version
  1. 365
Platform
  1. Windows
I have a need to link a text box in a user form to cell that contains a formula. I would then want to disable the text box to prevent user entry. My reason for this is that I wish to have the text box (other other indicator) on the form to automatically update its value when the cell it points to changes.

Now, simply inserting a text box and putting the cell name in the ControlSource parameter only works once or twice. When excising the form, the value in the form text box will over write the formula in the cell. So, that is not going to work.

I have also tried inserting a label on the user form and update the label caption when things change on the worksheet itself. This has limited success as I have found instances when the label does not update. Plus ... I dislike having populate the form and worksheet with numerous lines of code just to accomplish this simple task.

Ideally, if I could have a text box or label where I could insert a formula ... would be fantastic. But, alas, I cannot find a way to do this. Does anyone have a work around that is simple and allows me to use text box on the form and not have to rely on VBA code to update a label caption?

Steve
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Steve

Why would you need numerous lines of code to update the label/textbox?

All you would need is one line and that could be in a separate sub the could be called whenever required.

PS What's the formula?
 
Upvote 0
The formula in the worksheet is quite simple ... merely setting a cell = to a simple IF statement. The purpose of the cell is to provide an indicator to the user so he/she has an indicator on the minimum value they can enter in an adjacent cell.

I want to link the same cell to the user form for the same reason. But using a text box is not functional because the value of the text box over writes the formula in the cell on the worksheet.

Yes ... I know I have create a simple line or two of VBA to update the user form ... and have done this to update the value of the caption using a label instead of a text box. Using this method, when the sub is run on the userform, it updates the labels to a value of the cell(s) on the worksheet. My issue, is that I do not want to have this being a manual thing. I want the userform to update automatically.

Now, I can get this to update automatically when the user is using the form for data entry. I created a sub that runs whenever the user updates something on the userform. So far ... so good. The problem occurs if the user were to perform an update data directly on the worksheet and not use the form. When the user does this, the labels in the userform do not update because the update was not performed using the form. I expect some might ask why I allow the user to have the option to use either the user form or the worksheet to perform updates ... because I want to and this gives the user greater flexibility.

So, what I would really like is something like a text box, but such that the value in the text box would never update the cells in the worksheet. Only have it the other way around.

Steve
 
Last edited:
Upvote 0
Steve

Have you looked into using the Change event of the worksheet where the user might make changes?
 
Upvote 0
Steve

Have you looked into using the Change event of the worksheet where the user might make changes?

Hi Norrie,

Yes. I have. In fact, I am using change events in both the worksheet and the user form. They both work fine. I have a subroutine defined in the user form called "update_controlpanel". This runs whenever a change event occurs. This is simple subroutine that updates all the label captions in the user form. Otherwise, the captions do not have a control source that a text box possesses.

I also have a change event subroutine in the worksheet that I initiate using the SelectionChange(ByVal Target As Range). This runs when a change occurs in a target range that I defined within the worksheet. But the VBA is put there was to update some specific ranges within the worksheet only.

Your thought triggered me to try to running the "update_controlpanel" subroutine when a change occurs on the worksheet. And, it works! I had not thought of that and before actually trying it, I thought it would bomb out. Well, I inserted an application.run command to run that subroutine .... and my initial trial works. I need to do more testing.

Thanks for triggering that thought.

Steve
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top