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

[bisel]

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

 

[Norie]

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?

 

[bisel]

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

 

[Norie]

Steve

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

 

[bisel]

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