INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

WORKSHEET CHECKBOX ALTERNATIVE by BrainB
Posted: 12 Aug 04

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","")


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close