Recalculate Radio-Button-Selected VBA Functions When A Cell Is Updated 2

[MattGreer]

I have a spreadsheet with a list of different chemical species, things like methane, ethane, propane, etc. I have functions/calculations that will take that list, reference physical properties on another sheet, and give results to the user. There are different equations that can be used, so I have a series of radio buttons to select which "equation of state" to choose.

If someone changes the value in one of the input fields, I want the selected EOS to re-trigger, using the updated value. Right now, I can't seem to get that to work correctly. If I change which radio button is selected, the calculation re-triggers just fine. But not when I update one of the cells in question.

Here's the code I have. I couldn't get it to work with multiple cells, so I just picked one to start with. Still having problems.

Any suggestions you might have with regards to what I'm doing, apart from my actual question, are appreciated. This is something I would really hope I could set up for others to use as well, and not just me.

edit: I'll add that the VBA functions/calcs put the answer in a specific cell on a worksheet. The equations are not built into the spreadsheet, so when one of the entry cells changes, the function needs to be recalculated. Below, I just have one function added at the moment, 'zViral'.

'When I click a different radio button, this just retriggers the calc.  Seems to work just fine.
Sub CheckOptions()   'I have this in Module1
    Select Case TypeName(Application.Caller)
        Case "Error"   'I added this because after I added the Application.Caller routine I kept getting VBA errors
            Debug.Print "Oops"
            Application.Calculate
        Case "obIdeal"
            Debug.Print "You clicked Ideal Gas"
            Application.Calculate
        Case "obViral"
            Debug.Print "You clicked Viral"
            zViral
            Application.Calculate
        Case "obSRK"
            Debug.Print "You clicked SRK"
            Application.Calculate
        Case "obPR"
            Debug.Print "You clicked PR"
            Application.Calculate
    End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'This is in the code section for the worksheet, should it be a Public sub?
    Dim WatchCells As Range
'Cell Names to watch:
'   _Comp.T
'   _Comp.P
'   _Comp.F
'   _Comp.Atm.P

'    Set WatchCells = Range("_Comp.T", "_Comp.P", "_Comp.F", "_Comp.Atm.P")
    Set WatchCells = Range("$D$3")

    If Not Application.Intersect(WatchCells, Range(Target.Address)) Is Nothing Then
'        MsgBox "Cell " & Target.Address & " has changed."
        CheckOptions
    End If
End Sub

As always, I appreciate you experts here. Thank you for what you do for all of us!


Thanks!!


Matt

 

[SkipVought]

Here's how I've always used Target in Events...

'
    [b]If Not Intersect(WatchCells, Target) Is Nothing Then[/b]
'        MsgBox "Cell " & Target.Address & " has changed."
        CheckOptions
    End If

If it's possible that the user could CHANGE multiple cells by PASTING values, then you may need to loop through the cells in Target.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

For combining cells in Range object:
Set WatchCells = Union(Range("_Comp.T"), Range("_Comp.P"), Range("_Comp.F"), Range("_Comp.Atm.P"))

combo

 

[SkipVought]

For named ranges, if my memory serves me, you can also use this...

Set WatchCells = Union([_Comp.T], [_Comp.P], [_Comp.F], [_Comp.Atm.P])

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

OK, I'm not getting errors anymore when I change one of the monitored cells. The problem now is that the Select Case... is not getting triggered when I change which radio button is selected. I honestly don't know what changed. The select case is in Module1. Does it need to reside in the code section for the specific worksheet?

Also, when I change on of the monitored locations, CheckOptions *does* get triggered, but it's getting "Error" so the Case for the selected radio button doesn't run the zViral function.

edit: I ensured that the radio buttons were all assigned to the Checkoptions sub.

Any ideas? Thank you again for your continued help!

Thanks!!


Matt

 

[SkipVought]

In your Private Sub Worksheet_Change(ByVal Target As Range) event code, put a BREAK on the statement, CheckOptions.

In the BREAK, STEP thru the next lines of code.

If yer not breaking on CheckOptions, then your Intersect code is not functioning as expected.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

OK, I put a break on it and then put:

Debug.Print TypeName(Application.Caller)

which returns the word, literally, "String", to the immediate window. So the string that's getting sent into CheckOptions doesn't have the name of the radio button when that radio button gets selected/clicked. All the radio buttons are inside the group box; I made sure each of the radio button "boxes" are well within the group box. The group box is named "Group Box 21".

I wonder if I should do something like this instead. The appearance leaves something to be desired, unfortunately. I don't know why what I have was working before, and not working now.

edit: I created another group box, a couple of radio buttons, and checked everything and the name of the radio button isn't making it into the subroutine. I used to. Not sure what's going on. I'll create a new workbook and try it and see if it's happening there as well.

Thanks!!


Matt

 

[MattGreer]

OKAY

I figured it out. The issue was the statement "TypeName" around Application.Caller. I think I added that trying to detect when there was an error due to my previous attempts at monitoring the changes in certain cells. Took that out, now it just reads "Select Case Application.Caller" and the radio buttons work as desired now EXCEPT, when I change one of the monitored cells, I get a "type mismatch" error on this sub. The error is on the line "Case obIdeal". Debug.Print TypeName(Application.Caller) returns "Error". Debug.Print Application.Caller returns Error 2023.

Sub CheckOptions()
    Select Case Application.Caller
        Case "obIdeal"
            Debug.Print "You clicked Ideal Gas"
            Application.Calculate
        Case "obViral"
            Debug.Print "You clicked Viral"
            MsgBox "You clicked Viral"
            zViral
            Application.Calculate
        Case "obSRK"
            Debug.Print "You clicked SRK"
            Application.Calculate
        Case "obPR"
            Debug.Print "You clicked PR"
            Application.Calculate
    End Select
End Sub

Thanks!!


Matt

 

[MattGreer]

Sorry for all the repeated posts.

For now, this works. All I can think of to avoid checking each radio button is to set a global string variable whenever a radio button is clicked. Honestly, this workbook isn't so calc-intensive (yet) that checking which radio button is true won't cause a big problem.

The other option, which I don't really like but at the moment I'm probably going to have to do this, is to link a cell to the radio buttons. Thinking out loud, that might not be a bad thing as I could force different radio buttons to activate if a user changes something that requires a change. I'm at the beginning of this; nothing's off the table yet.

Sub CheckOptions()
    If TypeName(Application.Caller) = "Error" Then
        zViral
    Else
        Select Case Application.Caller
            Case "obIdeal"
                Debug.Print "You clicked Ideal Gas"
                Application.Calculate
            Case "obViral"
                Debug.Print "You clicked Viral"
                MsgBox "You clicked Viral"
                zViral
                Application.Calculate
            Case "obSRK"
                Debug.Print "You clicked SRK"
                Application.Calculate
            Case "obPR"
                Debug.Print "You clicked PR"
                Application.Calculate
        End Select
    End If
End Sub

Thanks!!


Matt

 

[combo]

What kind of option buttons you use (Forms, Activex)? For me you complicate the problem, opttion buttons can be linked to range, also named. Changed selection changes underlying value, it indicates user's decision and no need to test Caller.

combo

 

[MattGreer]

combo,

I used Forms option buttons. If I understood you correctly, I do agree with you that I don't want the option buttons to be linked to a range at the moment. Or did I misunderstand? Is that "better"?

The issue I'm having is that when a "monitored" cell is changed, I need to re-trigger the calculations associated with the selection option. How would you suggest I go about making that happen?

Thanks!!


Matt

 

[combo]

The form controls is a legacy of excel 5, with sheets everywhere. Dialog controls were linked to cells, their values were used for calculations, either by code or formulas. Dialog sheets and workskheets can use nearly the same set of controls and process them similarly. So for me when working with form controls is natural to link them to worksheet. Sometimes it is possible to create no-code (xlsx file) UI, with calculations based only on form controls settings.

Concerning forms option buttons:
1.
It is useful to insert group frame first, next option buttons. Within the group box they are connected, it is possible to add another independent group with options buttons.
2.
If you add linked cell to any of option buttons within group this property propagates to all buttons witin group (if no group box - in worksheet). Change of option button changes linked cell, values indicate option set: 1, 2, 3, etc, depending on position of button within group. (AFAIK it is the order of adding option buttons to group and there is no way to change it.)
3.
Reading the user settings: write macro that works with linked data settings. Assign the macro to each of option buttons.
4.
Tip: you can use cell in hidden or very hidden worksheet, if you name it with workbook level name. Then in linked cell property directly write the name.

combo

 

[MattGreer]

(oh skip, I gave you a star for your post above. Was a helpful reminder to diagnose what was going on. Thank you!)

combo,

Thank you for taking the time to give me all that info! To be honest, I'm not married to Form vs ActiveX; I got the impression somehow that Form controls were less, idk, complicated or risky or something. I can change to ActiveX with no issues as I'm in the very early stages of this project. In your opinion, is one better than the other? I'm going to guess the answer is "it depends". In my case, this entire workbook is going to re-calc based on which of these buttons is selected. I'll explain what the calculation is below if you're interested.

My thinking is I want this spreadsheet to be mostly VBA driven to ensure the end user doesn't muck with the equations or data. I will "expose", where necessary, fields that will allow the user to change parameters as appropriate. So I don't mind this being an xlsm, or xlsb, however it needs to be.

I see what you mean with the option buttons; I linked a cell for the first button and I got one number representing which one was selected for the group. That's actually not a horrible thing. But, when I think about the end result for this, having hidden columns or rows, in my mind, is "cheesy". I don't know why I have that impression and it's most certainly based in ignorance.

So with all that being said, if I understand #3 correctly, your suggestion is to write a macro that looks at the linked cell and based off that number, trigger calcs/functions based on that linked cell value. For right now, I have four option buttons. I think a straightforward 'Select Case' sub would take care of the re-calcs nicely. Then I can stay away from code based on 'Application.Caller', which, honestly, is a bit of a mystery to me.

Thank you for your help!

(BTW I'm old and I want to call them radio buttons lol. I think I got that term from my Access programming days back in ~2003.)

More info on the calcs if it'll help
So the term EOS, or 'Equation of State', describes the function which determines the vapor-liquid equilibrium (VLE) of a given fluid, whether pure (single component) or a mixture. Changing the EOS will re-calculate the properties of the stream. What fraction of the stream is liquid? What fraction of the stream is vapor? What is the density, viscosity, molecular weight, compressibility, specific gravity, Btu content, and so on and so on. That, in turn will change the parameters going into any equipment sizing and rating the user may have specified within the workbook. So if you're familiar with something called a Process Simulator, that's essentially what I'm trying to do with Excel. Turn it into a *very* simple process simulator. We'll see how far I can take it. But, changing the EOS, or changing one of the monitored cells, will force a re-calc of the entire workbook.

Thanks!!


Matt

 

[combo]

As I wrote, I like forms controls. For me they are better adapted to be on worksheet, whereas activex controls are primarily for userforms. Forms combobox or radio buttons return numbers, it is easy to use them in formulas (as INDEX), to build excel application without macros. If used with macros, only one macro in standard module can be assigned to click/change event.

They are less secure, as use only worksheet protection (in the context of generally weak excel protection) and are not a part of vba project.

Concerning #3, yes, single macro in standard module can be assigned to each of buttons (in opposite to linked cell, this has to be done for each button). User's action changes value and calls the macro, macro processed data depending on value in linked cell.

EDIT:
Actually, unless the recalculation has to be immediate, you don't need macros assigned to radio buttons. Recalculation can be triggered by pressing commandbutton, after setting all required options and leaving decision "process" to user.

combo

 

[MattGreer]

They are less secure, as use only worksheet protection (in the context of generally weak excel protection) and are not a part of vba project.

Yeah, unlocking a spreadsheet is pretty easy as opposed to unlocking VBA projects, so there's that.

Actually, unless the recalculation has to be immediate, you don't need macros assigned to radio buttons. Recalculation can be triggered by pressing commandbutton, after setting all required options and leaving decision "process" to user.

There's that option as well which I've considered. If I do that, I need to make sure that the spreadsheet shows that the calc is "dirty" (for lack of a better term) and before using any of the indicated results a re-calc should be performed. I haven't started thinking about how to implement that yet. Perhaps in the macro that is watching certain cells, instead of running code, it just sets a boolean true or false, which then triggers conditional formatting of certain cells. Thoughts?

Thanks!!


Matt

edit: You don't have to call them radio buttons because of me lol. I hovered over them and Excel calls them option buttons, so I will change my language accordingly.

 

[SkipVought]

Option Buttons, Radio Buttons, Push Buttons.

Chrysler Motors made a Push Button Automatic Transmission Control back in the late 1950s.

So this young feller tell of his first experience of a Push Button car..

I pushed L for Leap and the car leaped forward.

Then I pushed D for Dash and the car dashed even faster.

And then I pushed R for Race...

Uh oh.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

And then I pushed R for Race...

DID YOU GET A SPEEDING TICKET??


edit: (yes, I got it lol)

Thanks!!


Matt

 

[combo]

If I do that, I need to make sure that the spreadsheet shows that the calc is "dirty"

Macros linked to radio buttons or Worksheet? Worksheet_Change does not pick changes made by forms controls..

combo

 

[strongm]

>unlocking a spreadsheet is pretty easy as opposed to unlocking VBA projects

Although unlocking VBA projects isn't exactly rocket science ...