PDA

View Full Version : Excel - Checkbox Alternative using Marlett across multiple sheets



wyrdred
07-25-2013, 04:02 PM
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:

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 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


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.

SamT
08-04-2013, 09:26 PM
The Marlett technique is not really that new. If you must use it, be sure to assign a Font name that has no possibility of ever being a real font name, such as "Candidate_Dropped_Out."

I think that you should rethink the approach you are taking. I strongly suggest that you consider using one master book on the Shared server as a database and multiple copies of the Users books to enter, view and edit the data.

The problem with using only one master book that multiple users open at the same time is that Excel cannot tell which version to save as the final version. If you lock the master when the first user accesses it, no one else can write to it until the first user closes their book. Even then only the next user to open the master can write to it. With a single Database book, it can be locked for the very few minutes it takes to read or write data, thus causing only very brief and random times that other users can't access it.

I am going to paste part of my reply to another App developer. The difference between hi App and yours is that his Database is specific to the individual using it. HIs will never be ported to a Database Server, and I think you should design yours with that porting in mind. This does add a couple of minor design restrictions: Your Field Names, (Column Headers,) can't exceed 32 characters in length, cannot contain spaces, and "Date" is not allowed."AppointmentDate," "DateTransmittalRcvd," et al, is fine.

There are three types of objects you need to design; Databases, Reports, and Forms. A Form is by definition a UserForm and is used for all data entry, and a Report can be on a worksheet or a Form and is never used for data entry. Any Report that takes more than one sheet of paper to print must be a Worksheet Report. Worksheet Reports are best for contemporaneous information like weekly and monthly reports. Dashboards are another type of Report.

Briefly, these are the steps that IMO are required to develop an App like yours:

I: Develop the database



Create a list of every possible data point you can think of. Name this sheet "Data Points List"

Use two columns, the first for the name of the data point and the second for a brief description of it. The second is mostly for me, but you will find it useful, too.
The Data Point "CaseNumber" should be at the top of most lists.

All Date names should be uniquely descriptive such as "VDOPrintDate," "ShiftStartDate," ShiftEndDate." etc. I'm not saying that you will have ShiftDates, that's just an example.


Using the description column, insure there are no duplicate data points. Indicate in the description which different place this DP is used.



Using a different sheet for each group, organize the DPs into related groups like the "Company Database" and "VDO" sheets.

Name each sheet according to its group
Put the lists in row 5 or 6 on each DP group sheet.



Review all the groups to insure that the requirements in steps 1 and 2 are met.
On each Group sheet, copy the list of Names and in cell "A1" PasteSpecial >>Transpose.
From now on, we will no longer refer to DPs and Groups, They have now transformed in to Field Names and Database Tables.
Format each Date and Time Field (Entire Columns) as number.
Save the workbook with the name "Database Descriptions."

Anytime in the future when you modify a database, edit this workbook to match, adding notes to describe what, when, why.
Keep Data Points List up to date.






Normally I would allocate several days for all the above, but you have already done most of it. By the time you complete the above, you will have a very good feel for what your App can and should provide anyUser and most important, what Form(s) you will need. The completed "Database Descriptions" is half the work of developing the UserForm(s), so do not think that it is not necessary.

Each of the below major steps will also take a few days. Only a few because of all the preparatory work done in step I. As you get to each step below, I will be expanding the minor step lists, so you will know what to expect.

II: Lay out the Userform(s)


Save book as "DTC.v.01." Suggested name.





Use the Data Points List to know what controls you'll need.

More info later



Open "Database Descriptions" and add the sheet "vbaList" to the end.

Delete all DP lists, leaving only Field Names.
Complete "vbaLists."
Make Lists of Suggested Form Control Names
Add database type code to Database sheets and "vbaLists."

I already have this code, that only needs localization to work in any DB type sheet.








III: Code the Form(s)


Increment the Version number to .02


IV: Add Reports Sheets, Increment Version number
V: Repeat II, III, & IV as needed.
VI: Beta test.
VII: Repeat step V as needed.
VIII:Put the application into production with final name + ".v1.0"
IX: Repeat Step VII as needed

SamT
08-05-2013, 04:02 PM
WyrdEd,

Any relation to Cowboy Bebop?

I posted a rather long response last night, but I think it got lost in the byte storm.

In any case, take a look at this conversation (http://www.vbaexpress.com/forum/showthread.php?47000-help-with-complicated-formula) I am having with another beginning Application developer. The main difference between his app and your is that his Databases are only meant to hold data relevant to the individual user, so his app will never be ported to a DB Server.

Your proposed App is a prime candidate for being on a DB Server, so I think that it should be designed with that possibility in mind. The main restriction you have over the other fellow is that your Field names (AKA Column labels) be no longer than 32 characters and can have no spaces in them.

It would be very frustrating for your users if the Database was in the same book as the data entry and reports were. Only one person at a time can open a book for writing. I suggest that you keep all the Data tables in a single master book on the server and allow each user to have their own data entry and reports book which would each only open the master DB book for Non restrictive reading and only open it for restrictive writing for the minimum time it takes to actually save the data the the book. The code in the Data Entry books can handle that.

Post # 5 in this thread (http://www.vbaexpress.com/forum/showthread.php?46768-Pulling-custom-footer-from-another-sheet-to-only-appear-on-*final*-page) will be of high interest.

In the first link above (http://www.vbaexpress.com/forum/showthread.php?47000-help-with-complicated-formula), post # 16 is a brief description of the steps to develop a database driven App.

#3 in this one (http://file:///F:/Desktop/VBA/My%20VBA%20Article%20Automated%20Macros.htm) may be of some slight help.

wyrdred
08-06-2013, 09:23 AM
Wow... SamT... thank you for responding in a detailed manner. Currently the worksheet(s) are "Shared" and it seems to be working. But, I understand the points you make. I have a lot to think about and map out (I wish it didn't take over a year to get assistance from my departments IT group). When you talk about a Database are you suggesting I use MS-Access or use Excel as a database?

By the way the user name of Wyrdred came from the original Deus Ex game (pc version).

wyrdred
08-06-2013, 02:23 PM
#3 in this one may be of some slight help.

the link is faulty

SamT
08-06-2013, 04:58 PM
My Bad: Here it is
Automated Macros (http://www.vbaexpress.com/forum/showthread.php?46873-Automated-Macros)