How to link user form to cell with formula

[earlewallbank]

I have a number of user forms that contain form controls (Text box, List Box, Combo Box, Labels) linking directly to cells in the Workbook.
The controls are programmatically setup using VBA, e.g using .ControlSource, .RowSource etc.
I simply want the form to reflect the values that are in cells in the workbook and be able to have both read/write and read-only values displayed on the form.
My intent is not to have to use change events in the form to drive the values in the form controls.

When you link a Text Box to a cell that contains a formula, regardless of locking the control or not, if the value in the cell changes, the new value appears in the control on the form; this appears to cause the control to write the new value back down to the cell and overwrite the formula in the cell with the value.
I have seen this question asked before but I have not seen an answer that is suitable.

I am using Excel 2007 for this.

Is there a simple way to display the value of a single calculated cell (i.e. a cell with a formula in it) on a form without the form causing the formula to be overwritten?

[earlewallbank]

I have used a Label control with some success.
Although .ControlSource is not a visible property of a Label control, it is accepted in the VBA code and successfully allows the control to be linked to the cell.
Also, if the value in the cell changes, the Label control does (most of the time) display the new value without overwriting the formula in the cell.
However, I have found that the value does not always update properly and can end up getting stuck/frozen - the value in the cell changes and the form continues to show an old value. So for my purposes, this is not a viable solution.

An alternative to this is to use a ListBox control and connect the cell using the .RowSource property.
Sounds silly but it appears to work. I have not seen any update issues with this but, unfortunately, the control is automatically re-sized at runtime according to the font size - so the height of the ListBox control ends up less than my other controls. It's not a show-stopper but I was hoping that by now someone would know a proper way of doing this.

[AndyLitch]

If you are linking the userform textbox to a cell with a function why overwrite the function from textbox input?

[earlewallbank]

Hi Andy, thanks for the quick response.
In this case, I want the form to display the value in the cell (read only) - I don't want to write to it.
As I have found, I know that you cannot link a TextBox control to a cell with a formula - since the formula gets overwritten with the value.
I am successfully using TextBox controls on cells that I want the user to enter values into.
But my issue is the apparently simple one of displaying the value of a cell on a form.
Labels work most of the time but not all; ListBoxes seem to work all the time but then I have a formatting issue.
Does that make sense?

[AndyLitch]

As I understand it then, The worksheet values are being updated by functions and this sin't always being reflected on the userform ?

You may need an event triggered macro on the worksheet to refresh the userform whenever a value changes.
I'm not sure if this code in the worksheet module will work but it's worth a try

Please Login or Register  to view this content.

[earlewallbank]

Correct, some worksheet values are updated by functions.
Basically, I have a worksheet that acts like a database (in the future this will all be re-worked in a proper application with database back-end but for now it's in Excel).
My forms link to specific rows (records) in this worksheet (database). Some columns are writeable and some are read-only (updated by functions or simply static text like a Units field).
Majority of these forms are modeless.
I'll try your suggestion - but did you mean Worksheet_SelectionChange or Worksheet_Change?
Since there is no 'selection' in the worksheet I don't see it'll be triggered.

[AndyLitch]

Hmm. good point... I was thinking of the value changing - just rethinking :-)

[AndyLitch]

OK.. Tried and confirmed it LOL..
If the display text is a single line then i found a Listbox and a Named Range work ok.
The listbox updates without reflecting back to the sheet and if you set the fontsize to the size you want the listbox will shrink to it.. So you set the height of the listbox by the fontsize rather than the actual height property

[earlewallbank]

Just fired up the work pc. Wasn't sure which event I was already using - it's WorkSheet_Change (I'm using it to set a global data changed flag).
I don't yet have a DoEvents in this sub.
Since I've already replaced many of my labels with List Boxes, I've just reverted one of the instances where the Label Value had got stuck - unfortunately (typically), it's not reproducing the same issue at the moment.
FYI - I previously had a single form with over 880 controls on it. I then ran into the "Excel has stopped working" issue because I was passing a control object into a generic sub in order to then set the .ControlSource (and other properties) on each relevant control. Seems to be a well known inconsistent issue with Excel. I then had to cut this one form into many separate forms to avoid this problem.
The form I actually saw the Label value sticking issue on only had about 2 text boxes and 1 label - so I'm not even in the ball park of any Excel memory issues.

I'm going to pull back a previous version and see if I can find one I can re-produce this issue with. Then I can try out any suggestions.

[earlewallbank]

Good to know that someone else can see the same issue.
So sort out my List Boxes and then set my other controls to the equivalent size.
That works for me - but I do find it particularly weird (not to say annoying) that Excel has these issues in the first place.

[AndyLitch]

Welcome to the club....
Few grey hairs at this end courtesy of microsoft

[earlewallbank]

Oh I'm a long time member of that club!
Thanks for your input Andy - good to have the support.
If it's OK, I'll hold off marking this as solved until after the Easter break. I'd be interested to know if any other members have come across this kind of thing.

[AndyLitch]

No problem Earl.. Be interested in a perfect solution myself

[earlewallbank]

I have managed to replicate this issue and seem to have found a way to force an update to user form Label controls if they do not automatically update when the associated cell value changes (via the cell formula).
Because my data entry is controlled via forms to a single master Worksheet, the Worksheet_Change(ByVal Target As Range) event is triggered on that worksheet each time a value is written.
In my case, the data entered goes to the master Worksheet, off to other worksheets, through calculations then the results (e.g. a total) is fed back to the master worksheet via other formula. It is this result that doesn't refresh properly on the form.
By tweaking the Excel calculation mode, the form Label controls get refreshed.
I am already using the Worksheet_Change event on my master Worksheet - but here is the simplified version:

Please Login or Register  to view this content.

Just forcing a re-calculation (Application.Calculate) does not cause the Label controls to be updated.
Hiding the form and showing the form does not cause the Label controls to be updated.
Unloading and Reloading the form causes the Labels to be updated (a bit obvious but thought I'd state it for the record).

This appears to give a generic and simple solution (other than using ListBoxes - which avoids the problem in the first place)... but it will not work if you make changes directly in other worksheets that have an impact on the results of the formula cell that is linked to the form.

[AndyLitch]

Nice one .... As ever the workings of Microsoft can boggle the mind..

[Norie]

There is a way you could avoid this problem, don't use ControlSource/RowSource.

Instead add a couple of lines of code to update/populate the userform/worksheet when you want it to be updated.

[earlewallbank]

Hi Norie. Yes, that is a solution that would avoid this - and if developing something like a VB app say, that's how you would do it since you don't have direct native cell connections.
I have about 15 forms that contain various quantities of data links to the Worksheet, say 15 data entry values and 30 read-only values for one of the more populated ones.
Each data entry value impacts the outputs of numerous calculations and the results of these need to be instantly seen by the user on this an other non-modal forms.
The only solution I would see using this approach is to refresh every data value on all the loaded forms after any value in any form gets changed. This would require code behind every data entry point (both to write the value to the Worksheet then to refresh the other values).
I do use well defined control object names so if there is a way to generically code for a user write to any control on a form without having to put an event script on every user input object then I'm all ears - I just don't want hundreds of little scripts.

[Norie]

Couldn't you use the various events of the controls on the userform to execute code that would update values instantly.

If there are a lot of controls that need to be monitored then you could use a Class.

[earlewallbank]

Well, yes - that was the only way I could think of to support the suggestion. All the tools are there - it's just not neat (and considering where I am already, sounds like a lot of work to convert over).
Sorry, I'm not very familiar with Classes (I may have used them and instantiated them using other peoples sample code but that's it).
Would this be along the lines of an object in memory that has its own events triggered when one of the controls it is monitoring is changed? One per form or for all forms possibly?
So if I add more controls to a form, I'd need to update the Class object to include the new control? but I wouldn't need to add a change event on each data entry object on the forms!
Then I could add generic code to the Class event to avoid having to specifiy each control.
If so, sounds interesting... but no idea where to start on that one. Any pointers/references?