1 Attachment(s)
Excel - Checkbox Alternative using Marlett across multiple sheets
First, I apologize for the length of this post. But, I wasn’t sure how to simplify.
Background – I am an Analyst and was tasked with creating a tracking document onto which multiple users will enter data. The file is to include summary pages for the data. I did my best not having any VBA knowledge and basically being able to muck my way through Excel 2003 and Excel 2007 (when project was started used both versions). Users and managers have identified through initial use some desired changes, including simplifying data entry tracking the data for the entire fiscal year instead of monthly. I am hoping that if I can grasp the concepts that I currently have questions on that I can work out the rest.
At first I tried to apply individual checkboxes to the 2500 x umpteen columns and realized I was going to crash my computer when it took 10 minutes just to paste all 2500 to just one column. Further, exploration for alternatives led me to the kb article submitted by lenze for Checkbox Alternative (I can't include the link as my post count isn't high enough)
The VBA code from the kb article is as follows:
Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then
Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("myChecks")) Is Nothing Then
Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub
Code:
'Code for Worksheet "Mutually Exclusive examples"
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then
Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("Ckboxes")) Is Nothing Then
Exit Sub
'Set Target font to "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets target Value = ""
Cancel = True
Exit Sub
End If
End Sub
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Limit Target count to 1
If Target.Count > 1 Then
Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("Ckboxes")) Is Nothing Then
Exit Sub
'Select a specific subset of the range "Ckboxes"
Select Case Target.Address
Case Is = "$D$2", "$D$4", "$D$6"
'Clear Contents of cells that are not the target
If Target.Address = "$D$2" Then [D4,D6].ClearContents
If Target.Address = "$D$4" Then [D2,D6].ClearContents
If Target.Address = "$D$6" Then [D2,D4].ClearContents
'Place the address of the "checked" cell in "$D$11"
Range("$D$11").Value = Target.Address
Case Is = "$H$3", "$H$5"
'Clear Contents of cells that are not the target
If Target.Address = "$H$3" Then [H5,H5].ClearContents
If Target.Address = "$H$5" Then [H3,H3].ClearContents
'Place the address of the "checked" cell in "$H$11"
Range("$H$11").Value = Target.Address
Case Else
'Populate the cell to the right of Target with its status
If Target.Value = "a" Then
Target.Offset(0, 1) = "Checked"
Else:
Target.Offset(0, 1).Value = "Not Checked"
End If
End Select
End Sub
End of VBA script from kb article
I reviewed all of the material in the kb and thought I understood (but maybe not) the part regarding using Marlett for checkboxes. The Martlett checks work for one sheet but as soon as I add another sheet to the mix it doesn’t work. And I am still having trouble grasping aspects of the mutually exclusive coding.
I have provided a copy of my workbook. I am using Excel 2007 exclusively (as are the users who will be accessing the workbook). The file will be hosted on a server and accessed by multiple users in different locations (i.e. the file will be placed in shared status). We use Windows 7 Professional.
The workbook currently has 15 worksheets. The following worksheets are for notes, tabulation, and summary (Data Dictionary, MakeItWorkCalcs, Calculations, Drop-In). Therefore, these worksheets can be ignored.
Each data entry worksheet will have multiple columns and approximately 2500 rows.
I have highlighted the cells where I would like to utilize the Marlett check. The yellow highlight are just Marlett checks. The blue highlights (assuming I can get to a point of understanding to implement) would be mutually exclusive Marlett checks.
Orientation Worksheet: Column G&H; N-V checks (yellow highlight) and Column I&J; K&L mutually exclusive for the pair (blue highlight)
MakeItWork2 Worksheet: Column E&F, Q-V (yellow highlight) and Column H&I, K&L; N&O, X&Y, AD&AE (blue highlight)
Supervised_Job_Search Worksheet: Column F&G; H&I (blue highlight)
The following worksheets will just have check marks no mutually exclusive cells: Work_Training_Placement, OJT, Employment & Activity and AssessmentsOne of the error messages I get is Run-time error ‘1004’ Method ‘Range’ of object ‘_Worksheet’ failed.
Note when I go to Name Manager in Formulas and review what exists for the range myChecks the refers to locations have changed from the original columns to something like XEQ7:XER2506.
I really appreciate any input that can be shared to get me moving in a better direction. I hope to understand this “stuff” someday and am looking at taking the training offered by the site.
