Alternative for checkboxes

I would put all the employee names in Column A. Put the training programs along Row 1.

Instead of using check boxes, use data validation:

Off to the side in an un-used area of your spreadsheet (maybe column M or so), put a checkmark in a cell (maybe M1).

Select Cells B2:K126 (approximating your range). Then choose Data>Validation. In the "allow" field, choose "list." Then highlight M1 (where your checkmark is) and M2 (a blank cell).

Upon completing this, each time you click on a cell in the B2:K126 range, you'll see a dropdown box that will allow you to select a checkmark.

If you are not opposed to using some code, you can make this even friendlier than Outlaw's suggestion.

Download Example Workbook: Buckhorn36.297703.zip

Open a new workbook or download the example.

If, for instance, your check marks will be in range b2 to b30...

Range B2 to B30 is named "Checkmarks"
The font is set to WingDings2
Selecting a blank cell in this range will insert a checkmark
Selecting a cell that already contains a checkmark will remove it

Enlarge the font and center it for better viewing....

This code goes into the worksheet.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Worksheet_SelectionChange(ByVal Target <font color="#0000A0">As</font> Range)
       <font color="#0000A0">If</font> Target.Cells.Count = 1 <font color="#0000A0">Then</font>
           <font color="#0000A0">If</font> <font color="#0000A0">Not</font> Intersect(Range("Checkmarks"), Target) <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
               Target = UCase(Target)
               <font color="#0000A0">If</font> Target <> "P" <font color="#0000A0">Then</font> Target = "P" <font color="#0000A0">Else</font> Target = ""
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("10192007145257965").value=document.all("10192007145257965").value.replace(/<br \/>\s\s/g,"");document.all("10192007145257965").value=document.all("10192007145257965").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("10192007145257965").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10192007145257965" wrap="virtual">
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Range("Checkmarks"), Target) Is Nothing Then
Target = UCase(Target)
If Target <> "P" Then Target = "P" Else Target = ""
End If
End If
End Sub</textarea>

Don't use checkmarks!!

http://vbaexpress.com/kb/getarticle.php?kb_id=879

lenze

Hi All,

I tried to send a PM to Lenze but apparently his mailbox is overflowing and has no room for new questions!

I am creating a budget workbook with one master sheet populated by 33 slave sheets. I want to add a column to the 33 slave sheets that utilizes the VB code for "alternative to checkboxes" that Lenze provided.

I am new to VB so I apologize in advance if my questions are juvenile.

There are two VB panels for each worksheet, one called "general" and one called "worksheet" with many "sub" options. Do I need to paste this code in to both panels of every worksheet? If yes to any part of this, is there a way to do this paste enmasse or is it only done one sheet at a time?

Thank you in advance for any advice you can give!

Renee'

There are instructions on the bottom of the page that Lenze posted.

There is also a download zip file with all the code put together.
Did you try that?

Yes, I've tried about everything. It is not working across mutiple pages, but I have no doubt it is user error.

Hi, Renee,
WELCOME to the Board!!!!!

details deleted

kind regards,
Erik

select all sheets
(click first and shift click last sheet)
select column 1
apply font: "wingdings 2"

TO INSTALL IN THISWORKBOOK CODE WINDOW:
1. Rightclick the little Excel-icon on the topleft of your page just beside the Filemenu
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

works well for me

EDIT:
instead of
If Target <> "P" Then Target = "P" Else Target = ""
I like this syntax