Check box if cell value is X - vba code not working... (newbie)

[RobbieC]

Hi everyone, this is my first post here and I am a relative newbie with Excel VBA. I am primarily a php programmer, but I have been asked by my company to put together and develop a rather complex spreadsheet. I have the feeling that I will be asking quite a few searching questions on this forum, so please bear with me....

Firstly, I'm trying to get a checkbow to tick/untick depending on a value in another cell on the same (active) worksheet. My checkbox is 'Check Box 1' and I inserted it as a 'Form Control' not 'ActiveX' item:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.CheckBoxes("Check Box 1").Value = IIf(Range("AU13").Value = "SPT", True, False)
End Sub

but nothing happens. Am I missing something?

Thanks for your time in advance

Regards

Rob

 

[seguin85]

You need to set your target at cell au13, otherwise the code will not be called. Here is what I came up with, let me know if it works:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("AU13")) Is Nothing Then
    If ActiveSheet.Range("AU13").Value = "SPT" Then
        ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn
    Else
        ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOff
    End If
End If

End Sub

 

[RobbieC]

Thanks seguin. As your email came in, I realised my mistake.... my code was correct, but I had MISSPELLED the name on the checkbox.... DOH! (not a great start for a first post on the forum...)

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.CheckBoxes("CheckBox1").Value = IIf(Range("AS13").Value = "SPT", True, False)
End Sub

- this works fine now....

I wonder if you know how I could put an OR statement in here eg:

if Value = "STP" Or "CPT", True, False (if you get what I mean)

 

[seguin85]

I wonder if you know how I could put an OR statement in here eg:

if Value = "STP" Or "CPT", True, False (if you get what I mean)

Private Sub Worksheet_Change(ByVal Target As Range)    
   ActiveSheet.CheckBoxes("Check Box 1").Value = IIf(Range("AS13").Value = "SPT" Or Range("AS13").Value = "CPT", True, False)
End Sub

 

[RobbieC]

Ah, brilliant! Thanks mate. I've got to get my head around the VBA syntax.... it's a bit of a learning curve!