Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WORKSHEET CHECKBOX ALTERNATIVE

VBA How To

WORKSHEET CHECKBOX ALTERNATIVE

by  BrainB  Posted    (Edited  )
Here is some code you might find useful. It uses column(s) of cells as tickboxes that toggle when double-clicked. It solves several problems - such as having to set up and align numerous controls, which, if from the Controls Toolbox could contain bugs.

Format the column(s) as Wingdings font etc.. The code goes into the worksheet code module.


Code: 
'------------------------------------------------------
'- WORKSHEET CHEKBOX ALTERNATIVE
'- ADDS OR REMOVES TICK IN A CELL. Font = Wingdings.
'- This example uses columns A and E.
'- Brian Baulsom October 2000
'------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, _
        Cancel As Boolean)
    Dim MyTick As String
    MyTick = Chr(252)
    TopRow = 10
    BottomRow = 38
    '-
    If (ActiveCell.Column = 2 Or ActiveCell.Column = 5) _
        And ActiveCell.Row >= TopRow _
        And ActiveCell.Row <= BottomRow Then
        If ActiveCell = "" And ActiveCell.Offset(0, 1).Value <> "" Then
            ActiveCell.Value = MyTick
        Else
            ActiveCell.Value = ""
        End If
        ActiveCell.Offset(1, 0).Select
    Else
        MsgBox ("Cannot tick here.")
    End If
End Sub
'-------------------------------------------------------------------------

Say you want to check that all cells in range B10:B38 are ticked , put this formula in a cell :-

=IF(COUNTBLANK(B10:B38)=0,"All rows ticked","")

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top