CODEWITHSUNDEEP
vbaexcel
Trevor
Trevor

Reputation: 141

VBA macro not triggering when target cell changes via form control option buttons

I literally just got my feet wet with VBA as this is my first macro. After many hours of searching, I couldn't seem to find an answer that had a solution that worked for me so here I am.

On Sheet3 I have 3 option buttons in a group box that are linked to cell "B18" on Sheet4 (Sheet4 is hidden to the user, a backstage if you will). When any of the three option buttons are selected, 'Sheet4!B18' gets updated as it should (e.g. 1, 2, or 3). What I want to happen is to have 'Sheet3!B17' changed based upon the value in 'Sheet4!B18', or effectively: IF('Sheet4!B18'=2,SET('Sheet3!B17'="Some Text Here:"),SET('Sheet3!B17'="0%")), but still allow user input in 'Sheet3!B17'. I have one VBA macro on Sheet4 with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Worksheet.Range("B18") = 2 Then
        Worksheets("Sheet3").Range("B17") = "Some Text Here:"

    Else
        Worksheets("Sheet3").Range("B17") = "0%"

    End If

End Sub

If I manually update 'Sheet4!B18' then the macro gets triggered with the desired results. If I use any of the 3 option buttons on Sheet3, the macro does not get triggered even though the target cell is getting updated.

In my searching I couldn't seem to find anything concrete, but from what I could tell the "Worksheet_Change" function doesn't see changes to cells from form control as changes to the linked cell are considered a "recalculation" as if it were from a formula. I don't know how correct that is, but my searching led me to believe that I would need another macro assigned on the 3 buttons and/or group box that when either of those get selected/changed, it would somehow trigger the working macro on Sheet4.

I thought that perhaps I could create a new macro that I would assign to the group box or option buttons themselves so I tried that and could not get anything to work. I tried adding the above macro code to another sub, Sub mode() and assigning to only the group box, then only the buttons, but nothing happened in either case. I proceeded to try tweaking the code just in case the references were not correct, but saw no change regardless of how I specified the reference. I am not getting any error messages, but nothing gets triggered unless I manually change the value in 'Sheet4!B18'.

Is there a way to get the first macro that I have working on Sheet4 to trigger off of the option buttons changing the target cell value, something like forcing it to look only at that one specific cell for changes? Am I stuck making another macro for the buttons and/or group box to trigger the macro on Sheet4? Am I over-complicating this and there is some built in Excel sheets function(s) that I can use?

Upvotes: 1

Views: 1529

Answers (2)

Trevor
Trevor

Reputation: 141

Sometimes you just have to go through your entire thought process and type everything out before you have an "ah-hah!" moment because that is exactly what I had happen. I said to myself, "Why can't I have just one macro that gets triggered by the option buttons that checks my linked cell then proceeds to update the cell I want?" Well, eventually I was able to find the right code and this is what worked perfectly:

Sub mode()  ' mode is the name of this macro

    If Worksheets("Sheet4").Range("B18") = 2 Then
        Worksheets("Sheet3").Range("B17") = "Some Text Here:"

    Else
        Worksheets("Sheet3").Range("B17") = "0%"

    End If

End Sub

As it turns out, I was overlooking the simple solution and the above macro is all I need once I assigned it to the 3 option buttons in my group box, but not the group box itself. Since users will not have access to the hidden Sheet4 and therefore 'Sheet4!B18' will never have manual user input, the macro I first had on Sheet4 could be removed safely. Due to the fact that the option buttons being chosen is the trigger for the assigned macro, it executes each time the option is changed and only when the option is changed. Perfect!

EDIT:

Thanks to Chris Strickland for some tips for better code! I went on to modify the above into what you see below for slightly better performance (using Cells() instead of Range()), to save the original value to another cell and restore it if option 1 or 3 were selected, used aliases, and finally the IIf operator.

Sub mode()  ' mode is the name of this macro
Dim S3 As Worksheet: Set S3 = Worksheets("Sheet3")
Dim S4 As Worksheet: Set S4 = Worksheets("Sheet4")

If IsNumeric(Cells(17, 2)) = True Then
    S3.Activate
    S4.Cells(18, 3) = Cells(17, 2).Value
End If
S3.Cells(17, 2) = IIf(S4.Cells(18, 2) = 2, "Some Text Here:", S4.Cells(18, 3))
End Sub

Upvotes: 0

Chris Strickland
Chris Strickland

Reputation: 3490

IF/THEN is a fine way to do it. VBA also supports ternary logic with the IIF function, like this:

Worksheets("Sheet3").Range("B17") = IIF(Worksheets("Sheet4").Range("B18") = 2, "Some Text Here:", "0%")

That may seem a little difficult to read, but it's a good concept to understand, since it's present in many languages, and usually with a more simplified implementation that makes it very useful and concise.

Also, I would suggest making a couple of other alterations that may make your code easier to write, read and maintain (especially as it becomes more complex).

First, alias the worksheets, something like this:

Dim this as Worksheet: Set this = Worksheets("Sheet3")
Dim that as Worksheet: Set that = Worksheets("Sheet4")

Now you would be able to rewrite your code like this:

If that.Range("B18") = 2 Then
    this.Range("B17") = "Some Text Here:"
Else
    this.Range("B17") = "0%"
End If

And the ternary approach would now be:

this.Range("B17") = IIF(that.Range("B18") = 2, "Some Text Here:", "0%")

And you can get as specific as you like with the aliases. For instance, you could realias the ranges, instead of just the worksheets, like this:

Dim this as range: Set this = Worksheets("Sheet3").Range("B17")
Dim that as range: Set that = Worksheets("Sheet4").Range("B18")

this = IIf(that = 2, "Some Text Here:", "0%")

Also, I find it easier to use the cells property than the range property, especially when you start having to do cell math. In that case, Range("B17") becomes Cells(17, 2).

You can also change the way the cells are referenced in the spreadsheet by typing Application.ReferenceStyle = xlR1C1 into the immediate window. That way you don't have to mentally convert between A2 style ranges to Cartesian style (2,1).

Upvotes: 1

Related Questions