PDA

View Full Version : Using Marlett for optional selections - multiple ranges



bwait
05-02-2019, 03:30 PM
Hi,
I am using Marlett checkboxes to implement three mutually-exclusive checkboxes (columns A, B, and C). I found some Worksheet code that makes these mutually exclusive (for example, checking A1, clears A1:C1, then sets A1 to Marlett "a"). But, I want the checkboxes on a row to be optional as well as mutually-exclusive. For example, a user can check box A or B or C, or none of them. The code I have wont allow this. Any ideas how to implement this?

Thanks
Brad

Here is the code I am using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column >= 1 And Target.Column <= 4 Then
If Not Intersect(Target, Range("A" & Target.Row & ":C" & Target.Row)) Is Nothing Then
Target.Font.Name = "Marlett"
Range("A" & Target.Row & ":C" & Target.Row).ClearContents
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End If
End Sub

bwait
05-06-2019, 03:10 PM
FYI, I did a workaround, where I inserted a new column A that serves as a "clear all" checkbox for that row. The remaining three checkbox columns (now B, C, and D) remain mutually exclusive, but not optional.
A user can clear their checkboxes on a row by clicking in the A cell. For example, click A3 clears B3:D3.
Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub
If Target.Column >= 1 And Target.Column <= 5 Then
'Enable mutually-exclusive selection of cells B, C, or D.
'That is, clear B:D and then reset target-selected cell to be
'a Marlett 'a', which displays as a checkmark.
If Not Intersect(Target, Range("B" & Target.Row & ":D" & Target.Row)) Is Nothing Then
Target.Font.Name = "Marlett"
Range("B" & Target.Row & ":D" & Target.Row).ClearContents
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
Else
'Enable clicking cell in column A to clear columns B:D
'Column A is Marlett 'r', which displays as an X, but not until it
'is clicked the first time.
If Not Intersect(Target, Range("A" & Target.Row)) Is Nothing Then
Target.Font.Name = "Marlett"
Target = "r"
Range("B" & Target.Row & ":D" & Target.Row).ClearContents
End If
End If
End If
End Sub

大灰狼1976
05-07-2019, 12:32 AM
Hi bwait!
The following code is a little simpler.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column > 4 Then Exit Sub
Dim s$
If Target.Column = 1 Then s = "r" Else s = "a"
Cells(Target.Row, 2).Resize(, 3).ClearContents
Target.Font.Name = "Marlett"
Target = s
End Sub