Worksheet Change Macro

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
I am having a problem getting a worksheet change macro to run. I have range B1:B6 that is toggled between 1 and 2 by way of a radio button. The worksheet change macro runs fine if I manually input a value. And the macro I try to run through the worksheet change macro runs fine if I run it manually. But I can not get it to run using the radio buttons. Below are the two macros I am dealing with. Any help would be much appreciated and thanks to all of you on Mr Excel again.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim KeyCells As Range
    Set KeyCells = Worksheets("Sheet1").Range("B1:B6")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then    
        Call Update_Pick  
    End If
 
End Sub
[code]

[code]Sub Update_Pick()
 
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
 
Worksheets("Sheet1").Range("C1:C6").Calculate
 
If Worksheets("Sheet1").Range("B1") = 1 Then
   Worksheets("Sheet1").Range("D19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("D19").Interior.Color = RGB(0, 128, 0)
End If
If Worksheets("Sheet1").Range("B2") = 1 Then
   Worksheets("Sheet1").Range("H19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("H19").Interior.Color = RGB(0, 128, 0)
End If
If Worksheets("Sheet1").Range("B3") = 1 Then
   Worksheets("Sheet1").Range("L19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("L19").Interior.Color = RGB(0, 128, 0)
End If
If Worksheets("Sheet1").Range("B4") = 1 Then
   Worksheets("Sheet1").Range("P19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("P19").Interior.Color = RGB(0, 128, 0)
End If
If Worksheets("Sheet1").Range("B5") = 1 Then
   Worksheets("Sheet1").Range("T19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("T19").Interior.Color = RGB(0, 128, 0)
End If
If Worksheets("Sheet1").Range("B6") = 1 Then
   Worksheets("Sheet1").Range("X19").Interior.Color = RGB(255, 0, 0)
Else
   Worksheets("Sheet1").Range("X19").Interior.Color = RGB(0, 128, 0)
End If
Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
 
End Sub
[code]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Worksheet change events are not triggered by option buttons.
Could you replace them with Data Validation dropdowns?
 
Upvote 0
Thank you. I did use the validation drop-downs before but thought the radio buttons would work easier and faster. I guess that is what I will have to do. Thanks again.
 
Upvote 0
You're welcome.

You can assign macros to the radio buttons, but I'd tend to go with DV & event code.
 
Upvote 0
You're welcome.

You can assign macros to the radio buttons, but I'd tend to go with DV & event code.

Thank you again. I did try using the macros and it works perfectly. The data validation wouldn't work maybe because I had to turn off calculating. I don't know how I missed the macro option, so thanks again.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top