lundi 16 janvier 2023

Excel VBS: multiple Individual Exclusive Checkboxes with data validation (create a Macro with selecting cells to format them) (code is here)

I'm trying to introduce Checkboxes into my personal project Planning, unfortunately normal Checkboxes tend to bug out, so I found this side here and am trying to convert it into a macro to select the rows I want checks at. Specifically the last one that is NOT "Mutually Exclusive" but with data validation. http://www.vbaexpress.com/kb/getarticle.php?kb_id=879

Unfortunately it does not let me make it into a macro like I wanted to and I spent a lot of time trying already. :(

Please Help

I tried to write a SelectionRng. Or searched for a way to write it into a Macro to select it in the Worksheet.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
    If Intersect(Target, Range("Ckboxes")) Is Nothing Then Exit Sub
    'Set Target font to "marlett"
    Target.Font.Name = "marlett"
    'Check value of target
    If Target.Value <> "a" Then
        Target.Value = "a"    'Sets target Value = "a"
        Target.Interior.ColorIndex = 44
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents    'Sets target Value = ""
        Target.Interior.ColorIndex = 0
        Cancel = True
        Exit Sub
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
    If Intersect(Target, Range("Ckboxes")) Is Nothing Then Exit Sub
    'Select a specific subset of the range "Ckboxes"
    Select Case Target.Address
    Case Else
        'Populate the cell to the right of Target with its status
        If Target.Value = "a" Then
            Target.Offset(0, 6) = "Checked"
        Else:
            Target.Offset(0, 6).Value = "Not Checked"
        End If
    End Select
End Sub



Aucun commentaire:

Enregistrer un commentaire