The Great Calendar Project Journey to a Solution
The Story of A Journey: The Journey Begins Phase I: Outlook to Excel Phase II: Cleaning Up the Excel Data Phase III: Call In the Experts Phase IV: Making It Goof-Proof Phase V: A Test Run and More Goof-Proofing Phase VI: Fine-Tuning Trouble In Paradise Credits and Thanks Home	PHASE FIVE: A TEST RUN AND MORE GOOF-PROOFING Kitty’s overactive brain gets her up at 5:00 a.m. on Friday, October 15, and tells her to clean up all the bits and pieces because it’s time to do a test run. Kitty realizes that her project would run even more smoothly if she could attach the macros to custom buttons with easily-recognized graphics and then direct the user to “Click the happy face.” Kitty consults Excel Online Help to find out how to create custom buttons. She succeeds in attaching the macros to the buttons, making it a lot simpler for the user. One snag arises – since she hasn’t used custom buttons before, she didn’t realize she’d need to change the tool tip, so all her custom buttons now provide the useless tool tip “Custom Button.” At 5:55 a.m., she posts to Experts Exchange: Solution Title: Modify properties on a toolbar button Asked by prettykittyq on 10/15/2004 05:55AM EDT This solution was worth 450 Points and received a grade of A I added some custom buttons to my toolbar but didn't set the properties (like changing the tip name to something a bit more illustrative than "Custom Button"). Now I can't figure out how to go back and modify the properties. Help? Kitty At 5:59 a.m, her trusty Excel guru brettdj responds: Hi Kitty, Right click the button select Customise Right click the button again change the Name Cheers Dave Kitty tries it out but it doesn’t work. Crestfallen, at 6:27 a.m., she goes back to the drawing board. Hey, Dave! I must be sleepier than I thought but it's not working. From the worksheet, I right-clicked on the button I had added. A menu pops up and I select "Customize." That took me to the Customize dialogue which has tabs for Toolbar, Commands, and Options. I went to Toolbar tab and highlighted the Formatting Toolbar (where I stored my custom buttons), then went back to the Command tab. I can't find my buttons (there are five of 'em) anywhere. *oh* You mean the second time I should right-click the button ON THE TOOLBAR again. You are brilliant. Thanks a whole bunch for all of your help. Kitty (Kitty has learned how to switch back and forth between Excel and her browser – a skill needed to report exactly what she did and what went wrong. In the process of doing so, she figured out what Dave was trying to tell her. Doh.) At 7:49 a.m., Kitty sends the following e-mail to her boss: -----Original Message----- From: Kitty Sent: Friday, October 15, 2004 7:49 AM To: Kitty’s Boss Subject: Free at last! Just braggin' on the fact that The Boss's Six Month Calendar Production Process is now as close to finished and turnkey as I can make it. (Turnkey = turn a key and it operates itself). As always, I learned a lot about programming in Excel from my trusted experts at my computer whiz site so it was a Good Objective for Kitty. It now takes less than 15 minutes to produce a rough version of the Outlook Calendar as a Word document. That version still needs to be edited by hand (to clean up goofy-looking details) but my computer whiz friends managed to automate the process of seeking out and deleting all of those "Lunch" appointments Himself hates, as well as all of the "fake" times Outlook inserts when you thought you were entering an All Day Event. I'm going to see if this plays in Peoria today. Planning a demonstration for the Kid and the Lt Cmdr so they can learn to do it themselves. Ultimately, one of them should do the final edit since they know the schedule details better than I do. Will also demonstrate for you if you can find a free fifteen minutes. See ya later (probably in around 10:30 unless told otherwise). Kitty Kitty’s boss responds with an encouraging e-mail. Kitty sends e-mails to The Kid and the Lt Cmdr (the newest Assistant) suggesting they meet for a demonstration of Kitty’s Great Calendar Project. The Lt Cmdr sets up the appointment for 11 a.m. With a bit of time on her hands before she’s due at work, Kitty decides to continue the goof-proofing. One thing she thinks is going to cause problems is users using the same file to clean the data. If someone doesn’t clear the contents of the file, subsequent users will open a file filled with data and get confused. At 8:57 a.m., Kitty posts: Solution Title: Clear workbook contents when it opens Asked by prettykittyq on 10/15/2004 08:57AM EDT This solution was worth 400 Points and received a grade of A I'm just tweaking The Boss's Big Calendar Project now. How do I use VBA code to automatically clear the contents of an Excel workbook/worksheet when the file opens? The relevant workbook only has one worksheet which should be empty. Users are directed to open that file, import data from an external source, then SAVE AS under a different file name. Knowing my users, someone is going to SAVE instead of SAVE AS and subsequent users are going to get all upset to see leftover data when they open the file. As they say, it's hard to soar with the eagles when you are surrounded by turkeys. Thanks in advance for your help. Kitty At 8:59 a.m., matthewspatrick posts a solution: In the ThisWorkbook code module, put the following code: Private Sub Workbook_Open() Me.Worksheets("TargetSheet").Cells.ClearContents End Sub Kitty adds the code as directed and encounters a problem. She goes back to EE with a report of the error and Patrick sorts it out for her. (She needed to replace the words “TargetSheet” with the real name of the worksheet she wants cleared.) At 9:04 a.m., brettdj pops in with a suggestion – prevent the user from SAVEing the file at all: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then MsgBox "You can't SAVE this file, use SAVE AS" Cancel = True End If End Sub Kitty adopts that suggestion too. Kitty saves everything to the trusty memory stick and heads on out to work. Immediately upon arriving at work, Kitty installs her Excel file and Access database on the drive shared by all of the Chairman’s Office assistants and is dismayed to find the cute little buttons don’t work! Fortunately, the VBA code transferred safely so she decides to just walk them through running the macros manually via Tools, Macros, Macro. At 10:55 a.m., the Lt Cmdr gathers the assistants into his office. Kitty gives him verbal instructions, starting from the process of logging in as The Boss and exporting the Outlook calendar. 11:17 a.m. – the Lt Cmdr has successfully produced a usable Word document version of the six-month calendar. Current time to produce the document: 17 minutes (down from 35 minutes) Cheers of great rejoicing fill the land. The Lt Cmdr tells The Kid he needs to practice the procedure. Kitty intervenes and suggests The Kid can “test-drive” the written instructions for her so that she can find out where her instructions need improvement. Since Kitty’s desk is fifteen feet away from The Kid’s, he can get her attention quickly if something goes wrong. Half an hour passes. Something goes wrong. Kitty diagnoses the problem: The Kid missed a step. She starts him over and away he goes. Kitty notes down the problem. An hour passes. Something else goes wrong. The Kid skipped another step. Kitty fixes the problem. With 95 minutes elapsed, The Kid produces his own Word document of the six-month calendar. Knowing from personal experience how awful it was to maintain and update The Boss’s hard-copy calendar, The Kid is enthusiastic about the new procedure. Kitty realizes the procedure is not yet goof-proof since The Kid kept skipping steps. She fusses around with the custom buttons concept and succeeds in adding them to a custom toolbar which she attaches to the Excel worksheet. At 5:50 p.m., Kitty bounces into her boss’s office and asks if she can demonstrate Kitty’s Great Calendar Project, complete with buttons. Kitty’s boss sits back to watch. Using the new custom buttons (and being intimately familiar with the process from start to finish), Kitty produces a Word document six month calendar. Current time to produce the document: 12 minutes (down from 17 minutes) Kitty’s boss is duly impressed. They print out a hard copy and FedEx it to The Boss for his use and approval. Kitty goes home a happy puppy. End of phase five. Click here to go on to Phase VI...	Some VBA Code: All Macros CompareDates DeleteFakeTimes Empty A ForgetThePast GetDate ImportCalendarData KillB ReplaceApos SaveMyFile (v. 1) SaveMyFile (v. 2) SortDateTime SuperMacro Other Stuff Calendar Template (Excel file) dbCalendar.mdb (Access database) Report Screenshot
A KittyTours website maintained by Jean K. Rosales, Ph.D. Comments, corrections, and suggestions should be sent to webmaster@kittytours.org © 2004 kittytours.org, All Rights Reserved