And finally.... calculations update buy change value does not

[jmw]

I have a problem (Excel 2002) which isn't[/1] solved by setting calculation to automatic.

I already have calculation set to automatic.

On the user sheet, in some results cells, the results are calculated in one cell and copied to the result cell using the change value code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("AC17").Value = 1 Then
Range("J11") = Range("AD11")
End If

My problem is that for some of the cells controlled by this change value function, the value in the source cell may change (e.g. AD11 may change) but C17 does not change.
C17 is the result of just one combo box which determines which cell values should be copied to which other cells.
The other combo coxes control which results are displayed in the source cells.
One of a number of such may cause the value in AD11 to change.
So even though the value in AD11 changes, the value in J11 doesn't change - unless I do something else such as tabbing into a new cell.
I need that as AD11 updates, so too does J11 without some other action being necessary.
I can use a cell which sums all the target cell values as a trigger, but how?
Or is there a way I can force the change any time the value in any of the source cells change simply by monitoring the source cell?

JMW

http://www.ViscoAnalyser.com

 

[electricpete]

How about:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("AC17").Value = 1 Then
    Range("J11").formula = "=AD11"
End If

That way you don't transfer the value of AD11 to J11, you put a formula into J11 that points to AD11 and whose value changes whenever AD11 changes.



=====================================
(2B)+(2B)' ?

 

[electricpete]

A few thoughts about style.

1 - Relying on Worksheet_SelectionChange probably gets the job done, but also can get messy. You might think about whether you can accomplish the same thing in a more straightforward way. One way would be to build a vba special-purpose user-defined function like myJ11formula(inputCel11,inputCell2,inputCell3) and inserting =myJ11formula directly into the cell. That will again accomplish the result that J11 is updated any time one of the associated inputCells listed in the argument list changes.

Also you may be able to build the formula you need directly in the cell formula using built-in excel function or some general-purpose user defined functions. I made a "switch" function for excel that can help avoid the nested if syndrome:
Thread770-281108

2 - You can gives your important cells meaningful names (named ranges) to improve the readability of your spreadsheet.


=====================================
(2B)+(2B)' ?

 

[jmw]

This looks interesting, Electric Pete, but will it affect the reason I'm using this approach to start with?
J11 is an open unprotected cell With AC17=1, it puts the result from AD11 into J11 but if AC17 = 1 is false, the user can write a value into J11 and have it used in other calculations.

There was something that google found about including the instruction:
Application.Volatile
Another thing I was looking for was hinted at by this I found in a google search:

"If your macro relies on an updated cell value, you must force a Calculate ..."

Unfortunately, following the link that text nor the possible topic was on the page. It may be in the site but I didn't find it.

JMW

http://www.ViscoAnalyser.com

 

[MintJulep]

Maybe it's about time I point out that [red]I told you so[/red] back in thread770-303695 where I wrote:

It could be achieved with VBA. But I would suggest rethinking the arrangement.

and

I suggest that you use a VBA User Form for the interaction with the user.

It will make life much easier, and give you much greater flexibility with far less opportunity for things to go wrong.

 

[electricpete]

Aside from discussion of style (which is important as it leads to understandability, maintainability, troubleshootability, and a few other ility words I could make up), the code posted 6 Aug 11 12:17 resolves the particular problem of the original post, doesn't it?

Following on the discussion of style, FWIW, I tend to use named cells for user input. I always color them green as a visual cue that they are input (green means GO/ok to change... anything else is hands-off for the user). I can use excel tools to provide a list-of-values and validation on the input. I can access them by their name from vba. I also tend to arrange them all in one sheet in a simple table that contains most or all the interfaces to the program that the user needs. It's not as slick as a user-form, but a heckuva lot easier imo.

Attached is an example I developed fwiw in tab labled "Main"

=====================================
(2B)+(2B)' ?

 

[electricpete]

Also in above example note that if you click on the green input cells, in some cases (like Gryoscopic options), you see the pull-down arrow appear on the right indicating list-of-values available, as well as a displayed help comment indicating the meaning of the options 1, 2, 3. All of this were built with standard excel (non-vba) tools.

=====================================
(2B)+(2B)' ?

 

[electricpete]

As a last comment about my example, you'll notice I laid out a table of input variables including description, value (for input), units etc. It also includes a column for "symbol". That is for the programmers benefit. All of these variables have a named range assigned simply by higlighting B2:C16 and selecting "Insert / Names / Create / Left-Column. Now as vba programmer I can very easily look at the input page to figure out the name of the cell (variable) that I want to access from vba.

=====================================
(2B)+(2B)' ?

 

[electricpete]

Just to recap and address specific points raised:

This looks interesting, Electric Pete, but will it affect the reason I'm using this approach to start with?
J11 is an open unprotected cell With AC17=1, it puts the result from AD11 into J11 but if AC17 = 1 is false, the user can write a value into J11 and have it used in other calculations.

The code I posted 6 Aug 11 12:36 won't over-write any user-specified value in J11 unless AC17 is 1. When AC17 is 0, the macro doesn't change J11, and J11 is free to accept any user-specified value without fear of over-writing by the macro. Isn't that what you want?

I can see you might consider it a danger that if you had a user-specified value in J11 and user changes AC17 from 0 to 1 and back again to 0, the A11 value is now over-written by the formula pointing to AD11 and user value is gone. That seems to me almost unavoidable with the existing data and logic structure. One way to avoid it you could have a completely separate new input cell to store the user-specified input value, and then have J11 toggle between pointing to that new cell and AD11, based upon the value of AC17 (in fact you can do that with a excel formula if statement, not requiring vba worksheet change logic... a move in the right direction imo). If you think the user may be confused as to whether his input is being used or not, you can use conditional formatting to turn the input cell red when the value of AC11 indicates it is not being used. Ther may be many other ways to skin that cat...

=====================================
(2B)+(2B)' ?

 

[electricpete]

Another thing I was looking for was hinted at by this I found in a google search:

"If your macro relies on an updated cell value, you must force a Calculate ..."

It is a trap to beware of and also something I have tried to address above.

Let's explore it some more by referring to the example above where I said to build a UDF labeled myJ11formula. and insert into J11 the formula "=myJ11formula"

IF the BODY of the UDF attempts to read input data directly from the spreadsheet, then excel is not smart enough to recalculate your cell (J11) when that "input" data changed.

In contrast, if the input data is passed as an ARGUMENT to the UDF, then your cell J11 will calculate whenever those input cells change and life is good. That is why I said: "One way would be to build a vba special-purpose user-defined function like myJ11formula(inputCel11,inputCell2,inputCell3) and inserting =myJ11formula directly into the cell. That will again accomplish the result that J11 is updated any time one of the associated inputCells listed in the argument list changes."

(by the way J11formula is a horrible name...I was forced to use it because I know nothing of your application...so I can't pick out the more natural name like "MassFlowRateFormula")

=====================================
(2B)+(2B)' ?

 

[electricpete]

Correction in bold:

If you think the user may be confused as to whether his input is being used or not, you can use conditional formatting to turn the input cell red when the value of AC11 indicates it is not being used.

should have been:

If you think the user may be confused as to whether his input is being used or not, you can use conditional formatting to turn the input cell unreadable (black background or white text) when the value of AC17 indicates it is not being used.


=====================================
(2B)+(2B)' ?

 

[electricpete]

I can see you might consider it a danger that if you had a user-specified value in J11 and user changes AC17 from 0 to 1 and back again to 0, the A11 value is now over-written by the formula pointing to AD11 and user value is gone.

By the way, this danger is inherent in your existing code, not anything introduced by my modification. My modification removed the problem you mentioned that the value you copied into J11 from AD11 would not update when AD11 re-computed if that re-computation was not accompanied by a worksheet change event (even though the value of AC17 was 1).

=====================================
(2B)+(2B)' ?

 

[jmw]

I am using conditional formatting to change the cell colours and indicate if they should have values entered or if the values will be written to them by the code.
If the code causes a value to be written to a cell, the user cannot overwrite it.
If the cell is open to be written to then the value may be changed by the code, if the options are changed, overwriting the user data. But this is not a problem because, by the nature of it, the users is alerted to any such changes by the conditional formatting.
And the instructions are clear: configure the calculation set up first.
The only user data that is vulnerable are the target values of which, at any one time, there is only one:

Names:
This is a blend calculation spreadsheet, just two components at the moment:
[ul][li]set ratio and find blend properties.[/li]
[li]set a selected property as a target and find the ratio to achieve it.[/li][/ul]

He can choose to use density, viscosity or sulphur as a target.
Whichever property he chooses, the code controls the conditional formatting and writes the calculation results for which ever values are being calculated.

This all works a treat including the user choosing the blending temperature (before or after configuring the other calculation setup) and all properties are calculated for that temperature.

I have imported my viscosity multi-curve sheet to take care of viscosity at any temperature and most blend properties are m/m anyway.

In fact I have only one property of note that is v/v., not normally a problem since if I know the mass ratio and densities, the volume ratio is easily found.

My real problem comes because the user may choose to enter either %mass for the distillate stream or % volume.
Since I have the density at 15C and the industry uses volumes at 15C, it ought not to be a problem.

But it is this last toggle of %mass or %volume which is causing the most problems.

I think I have a sequential calculation resulting from this.
Toggle %mass to %volume and I have a calculation that doesn't update till I tab into a cell.

But once this calculation update occurs it then triggers another recalculation and I again need to tab into another cell to complete the sequence.
Very unsatisfactory.

I am about to start afresh and try and make it clean. It's a pain writing it and then making changes.



JMW

http://www.ViscoAnalyser.com

 

[electricpete]

Since you are going back to the drawing board, I should probably wait to see what you come up with because I doubt I understand what all your challenges are.

Nevertheless, I have attached a very simple (trivial?) input framework for toggling based on StreamType "MASS" or "VOLUME" (user-selected from pull-down menu).

There are separate cells for user input depending on the choice of MASS or VOLUME. The CalcInput cell pulls from these depending on the value of StreamType.

For the CalcInput Cell:

I could have simply used the following which works since there is errorchecking to ensure that StreamType can contain only MASS or VOLUME:

=if(StreamType="MASS",MassInput,VolumeInput)

Instead, I preferred to use my switch function because (imo) it is easier to see what's going on, also also provides a redundant level of error checking after the final "true" (which acts like an else clause):

=sw(StreamType="Mass",MassInput,StreamType="Volume",VolumeInput,TRUE,"ERROR_STREAMTYPE_MUST_BE_MASS_OR_VOLUME")

I apologize if the input is obvious or completely missing the point.

=====================================
(2B)+(2B)' ?

 

[jmw]

Well, guys. Thanks again.

I have re-written it from scratch but since I now had a better idea of what I needed to begin with and all the changes requested were known up front, I now have a cleaner functioning solution.

Part of my problem wasn't so much circular references but a number of calculations that came close.
Cleaned up I am left with only one annoyance which I will live with for the moment.

All those cells with internal formulae update whenever I change the combo selection that affects them.

All those where I use the code to copy the values into empty unprotected cells, I need to hit the TAB key after changing the combo selection. I'd prefer if this did the same as those cells with internal formulae but this is workable.
I just add an instruction that says "hit tab after a change".

For various reasons, I won't attempt to solve this now (though I do want to solve it) and because it gets converted into a web application. I want to see what the issues are that come out of that.

MintJulep,
I haven't been ignoring your advice, I just need to take some more relaxed time to try this. I'm thinking that this might be the time to start thinking about Visual Basic outside of Excel since these all seem to end up as applications.



JMW

http://www.ViscoAnalyser.com