Help needed on FORM Checkboxes...

[Platem]

I create a column of Checkboxes (FORM) at the end of rows of data as follows (WORKS FINE):

Sub DoCheckboxes()
    For Each cell In Range("O3:O60,O66:O123")
       With Sheets(1).CheckBoxes.Add(cell.Left + 2, cell.Top, 15, cell.Height)
           .LinkedCell = cell.Offset(, 0).Address(External:=True)
           .Interior.ColorIndex = xlClear
           .Caption = ""
           .OnAction = "Chk_Click"
       End With
    Next
End Sub

This function creates a border marker next to the first cell in the row when the corresponding box is checked (WORKS FINE):

Sub Chk_Click()
    Dim chk As CheckBox: Set chk = ActiveSheet.CheckBoxes(Application.Caller)
    
    If chk.Value = xlOn Then
        Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = xlDouble
    Else
        Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = xlDot
    End If
End Sub

Here's the problem:

After a SORT is performed, the data (including the checkboxes) is shifted around, but NOT the corresponding borders (Excel's SORT does not move cell borders)! So, I am trying to recreate the border markers (which were created with the above function) based on the current status of my 120 Checkboxes!

This is what I have:

Sub UpdateMarker()
    Dim i As Integer
    Dim chk As CheckBox: Set chk = ActiveSheet.CheckBoxes(Application.Caller)      '? not sure if this is right
    For i = 3 To 123
        If i < 61 Or i > 65 Then
            If chk.Value = "TRUE" Then                        '? need to check state of current checkbox
                Cells(i, 1).Borders(xlEdgeRight).LineStyle = xlDouble
            Else
                Cells(i, 1).Borders(xlEdgeRight).LineStyle = xlDot
            End If
        End If
    Next
End Sub

Help please! Project due...

 

[Rick Rothstein]

See if this macro does what you want...

Sub UpdateMarker()
    Dim i As Integer
    Dim chk As Shape
    For Each chk In Sheet1.Shapes
        If TypeName(chk.OLEFormat.Object) = "CheckBox" Then
            If chk.OLEFormat.Object.Value = 1 Then
                Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = xlDouble
            Else
                Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = xlDot
            End If
        End If
    Next
End Sub

 

[shg]

I'd punt on forms checkboxes and use Marlett checkboxes, which are on the same layer as the cells (they are cells) rather than the drawing layer. Then you can use conditional formatting to appliy the borders.

Search the board for many examples of Marlett checkboxes.

Or, you could use conditional formatting based on the linked cell to apply the formatting.

 

[Platem]

Rick, you are a GENIUS...

 

[Platem]

Thanks,shg

Never heard of the Marlett. I will certainly research that.

Peace

 

[snb_]

@Rick

See if this macro does what you like...

Sub snb()
  For Each chk In Sheet1.CheckBoxes
    Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = IIf(chk.Value = 1, xlDouble, xlDot)
  Next
End Sub

 

[Rick Rothstein]

@Rick

See if this macro does what you like...

Sub snb()
  For Each chk In Sheet1.CheckBoxes
    Cells(chk.TopLeftCell.Row, 1).Borders(xlEdgeRight).LineStyle = IIf(chk.Value = 1, xlDouble, xlDot)
  Next
End Sub

With the exception of your not having declared the chk variable, that is a fine modification to what I posted. (Hmm, I just noticed that I left in a declaration for an 'i' variable that I did not make use of in my originally posted code.) Although 120 checkboxes is probably not a large enough amount to make a noticeable difference, I did want to point out the IIF function executes much slower than its If..Then..Else equivalent, so the savings in space may not always be worth using it for that reason.

 

[Norie]

Couldn't the formatting be done with conditional formatting?

Just base it on the linked cells of the checkboxes.

 

[snb_]

@Rick

your not having declared the chk variable

What error message did you get ?

IIF function executes much slower than its If..Then..Else equivalent

Are you still using that Intel 8086 Machine ?