Worksheet Change Macro

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
117
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]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,246
Office Version
365
Platform
Windows
Worksheet change events are not triggered by option buttons.
Could you replace them with Data Validation dropdowns?
 

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
117
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,246
Office Version
365
Platform
Windows
You're welcome.

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

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
117
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,246
Office Version
365
Platform
Windows
You're welcome
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top