vendredi 1 juillet 2016

VBA - Each checkbox in loop react to when one changes, how to stop?

I'm new to VBA. I searched for a long time to find a way to have indefinite (varying) amount of ActiveX checkboxes but without the corresponding amount of _click subs but I couldn't make it work.

I thought I found a way around it with Form Controls, but it doesn't seem to work perfectly. This is my code to click cbxReady (a checkbox when a project is ready, can only be ticked if cbxStart is ticked):

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For s = 1 To LastRow - 2
    If ActiveSheet.CheckBoxes("cbxReady" & s).Value = 1 Then
        ActiveSheet.Cells(s + 2, "A").Interior.Color = RGB(255, 255, 0)
    ElseIf ActiveSheet.CheckBoxes("cbxReady" & s).Value = -4146 Then
        ActiveSheet.Cells(s + 2, "A").Interior.Color = RGB(218, 238, 243)
    End If
Next

And this is cbxStart:

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For s = 1 To LastRow - 2
    If ActiveSheet.CheckBoxes("cbxStart" & s).Value = 1 Then
        ActiveSheet.Cells(s + 2, "A").Interior.Color = RGB(218, 238, 243)
        With ActiveSheet.CheckBoxes("cbxReady" & s)
            .Enabled = True
            .Value = -4146
        End With
    ElseIf ActiveSheet.CheckBoxes("cbxStart" & s).Value = -4146 Then
        ActiveSheet.Cells(s + 2, "A").Interior.Color = RGB(255, 0, 0)
        With ActiveSheet.CheckBoxes("cbxReady" & s)
            .Enabled = False
            .Value = 2
        End With
    End If
Next

The idea was that the checkboxes are named cbxStart and cbxReady from 1 to whatever, and when I click cbxStart, the cell in column A (indicating a status of a project by colorcoding) will turn blue, then if I click cbxReady then it will turn yellow. So far so good.

However, this happened:
first row, both are ticked, colour is yellow, all is good;
second row, both are unticked, I tick cbxStart, it turns blue BUT first row turns blue too, as well as its cbxReady unticks.

So basically, even though the checkboxes are named differently, every checkbox reacts to the untick event.

Can someone help me with this please? If it's easier with ActiveX I'd appreciate that too, I'd just like to have one piece of code per checkbox type because I don't know how many I will have and I'd like them to reference one cbxStart_click() and one cbxReady_click().

Thanks!




Aucun commentaire:

Enregistrer un commentaire