automatically check a checkbox depending on source

**bvchrisb** · 05-18-2009, 05:21 PM

Hi everyone I'm new to the forum.

I've searched through the forum and I haven't found the answer, hopefully you guys can help me if not direct me to the thread

It might be very simple, but all I need is if I have data on A1, and i have 3 check boxes labeled: NEW, EXISTING, and ARCHIVED. I want it to automatically check the corresponding boxes. So if "NEW" is typed under A1, it'll check "NEW" box. "EXISTING" will check "EXISTING" check box and so on.

**Leith Ross** · 05-18-2009, 06:54 PM

Hello bvchrisb,

Welcome to the Forum!

The macro shown here will set only one check box at a time based on the input in cell "A1". If there is no match then all the check boxes are cleared. This type of mutual exclusion can be achieved more easily using Option Buttons as this is part of their design. The macro has already been added to the attached workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim ChkBox As Excel.CheckBox
  
    If Target.Cells.Count = 1 And Target.Address = "$A$1" Then
     'Clear all check boxes
      For Each ChkBox In CheckBoxes
        ChkBox.Value = xlOff
      Next ChkBox
     'Add check marks based on input
      Select Case LCase(Target.Text)
        Case "new"
          CheckBoxes(1).Value = xlOn
        Case "existing"
          CheckBoxes(2).Value = xlOn
        Case "archived"
          CheckBoxes(3).Value = xlOn
      End Select
    End If
    
End Sub

**bvchrisb** · 05-21-2009, 02:14 PM

thanks Leith that worked great!

the reason i'm using a checkbox is i'm duplicating a form that has check boxes. It's a PDF format and I'm making an excel template so all fields can be filled out automatically including checkboxes.

The other problem I'm running into is making the checkbox bigger, I've seen another post by someone using bullets. He put an image of a bigger bullet on top of the actual bullet and was able to put info on a cell and have it checked the appropriate bullets. Do you know how to make it do that w/ the checkboxes? (I can't seem to find that post

)

**Leith Ross** · 05-21-2009, 02:56 PM

Hello bvchrisb,

The Forms controls can not be resized. You would need to use ActiveX (Control Toolbox) controls. However, this will also change the macro and how it is called.

Another approach might be to use a font, like Marlett, to produce a check box. This approach is good if you just need a check box that doesn't need to handle events, just to be checked or cleared.

**bvchrisb** · 05-21-2009, 05:50 PM

thanks again. you opened the door. I looked at the Marlett and wingdings 2 and nothing would match. So I looked around and found a font "FontCreator" and made my own. Works perfect.

Thanks again.