macro window with buttons to accept user input

**joebelou** · 01-02-2009, 01:32 PM

I'm very new to Excel and definately to macros/vba. I've already received a tremendous amount of help with this file but I'm am really struggling here and getting very frustrated! I've got a pretty good handle on the excel part but the macros/vba is another story. Here is what i'm attempting to do: I've created a form "frmMacroButtons" which has 8 buttons. When the workbook is opened I want this form/window to launch. I want the user to be able to select any of the buttons but also be able to just click into the worksheet manually if wanted but have the window stay open to go back to use the buttons. Each of the buttons I've typed a description of what I want each of the buttons to do in the code view. I'm attaching the workbook in hopes that someone would be willing to help...Thanks in advance to everyone! joebelou

**Leith Ross** · 01-02-2009, 02:34 PM

Hello joebelou,

I have modified the attached workbook to do the following: Convert your UserForm to a Window and have it open when the workbook does. I will need some more details on what you want each button to do before I can help you program them.

Sincerely,
Leith Ross

**joebelou** · 01-02-2009, 03:28 PM

Hello again Leith, you are really awesome at this stuff man! Anyway, more information:
cmdAddNewPatient: I would like this button to open a prompt box for the user to enter the patients name "Lastname, First" then click ok or press enter and have the macro/script to enter the input on the "Data" sheet under the "Census" range in alphabetical order. Then have the script make a copy of the "New Patient Template" sheet and put it in front of the data sheet (with all the other patient sheets) in alphabetical order and make the sheet tab name the same as the patients name the user entered as well as select the patients name from the list box for cell H4 (which pulls from the "Data" sheet in the Census Range).

cmdEditPatient: This button should open a prompt box for the user to enter the patients name "Lastname, First" then drop into the worksheet named the same as the name the user input so they can edit the sheet.

cmdDeletePatient: This button should open a prompt box for the user to enter the patient name "Lastname, First" then based on that input find the input name in the "Data" sheet in the "Census" range and remove it from the list (but not leaving any holes in the list and making sure everything is still in alphabetical order) then have it delete the worksheet with the name the user input.

cmdCreateSummaryReport: This button should just delete all the data on the two sheets "1st - 15th Summary Report" & "16th - "31st Summary Report" in the ranges A3:ZZ200 on both sheets and then run the macro "Summarize".

cmdPrintSummary1st_15th: This button should just select a print range on the sheet "1st - 15th Summary Report" including all data and send it to a printer of the users choice. Is it possible to have the printer selection window popup so the user can decide which printer to sent it to?

cmdPrintSummary16th_31st: This button should just select a print range on the sheet "16th - 31st Summary Report" including all data and send it to a printer of the users choice. Is it possible to have the printer selection window popup so the user can decide which printer to sent it to?

cmdPrintPatientReport: This button should prompt user the user for the patients name "Lastname, First" upon pressing OK/enter the macro should find the sheet that matches the input and select the print range and prompt the user for which printer to send the print job.

cmdPrintAllPatientReports: This button should print all of the worksheets with patient names. This should exclude the "Data", "New Patient Template", "1st - 15th Summary Report" & "16th - 31st Summary Report" sheets. It should also prompt the user with a printer window to select which printer the job should be sent to.

I'm hoping this makes more sense. Please let me know if you need more information. Thanks a million! joebelou

**Leith Ross** · 01-02-2009, 04:11 PM

Hello joebelou,

Thank for the detailed write up. Looks like you have covered everything. I don't have any questions right now, but if I do come across something I have a question about then I will ask. This will take me awhile to code. If all goes well, you should have the workbook in 3 to 4 hours. By the way, I wanted to complement you on the design and layout. Both are very nice and have made writing code for it straightforward.

Sincerely,
Leith Ross

**joebelou** · 01-02-2009, 04:31 PM

Thanks Leith for the complement and all the help! Yeah, I don't have a problem figuring out what I want it's just making it happen. That's where I have a bit of issues...But, thanks to you I am learning a lot...slowly of course but not due to your skills! That's for sure! I hope to figure this macro/vba stuff out (VERY SOON). I'm a little frustrated at this point. Thanks for taking the time! joebelou

**Leith Ross** · 01-03-2009, 09:28 PM

Hello joebelou,

After a couple of crashes and other problems, it is done. The window comes up when the workbook opens and be activated again at anytime by using CTRL+R. I re-did your user form to give it a professional look. See the jpg. I have tested it quite thoroughly, but I may have missed something. Check it out and let me know.

Sincerely,
Leith Ross

**joebelou** · 01-03-2009, 11:07 PM

Hi Leith! You were right...I am very pleased with the outcome! Thanks for the form modification it looks really good. I was testing it and there was only a couple of things that I noticed. The button "Create Summaries" doesn't delete all the data on the two summary report sheets before running the macro. It duplicates the data over and over again. That is really the only issue with what is already completed. I just thought of a nice to have feature but if it is going to be a major change it isn't necessary. I would like to have a button that Saves & a button that Closes the entire worksheet located on the form. Like I said, the change I really need is the summaries button and the other would be a nice to have. Again, this is really GREAT! joebelou

**Leith Ross** · 01-03-2009, 11:20 PM

Hello joebelou,

I missed only thing. I can correct that problem quickly enough. This part I'm not sure what you mean...

I would like to have a button that Saves & a button that Closes the entire worksheet located on the form.

Sincerely,
Leith Ross

**joebelou** · 01-03-2009, 11:48 PM

I'd say that's pretty good only missing one thing after all I've thrown at you. On the other I'm just talking about 2 more buttons on the bottom of the input form. One that will save the workbook as an excel 2003 file (my other machine has excel 2007 but I prefer 2003) called "Save File" and another button that says "Close Workbook" and closes both the workbook and excel. Does that make more sense?
Thanks, joebelou

**Leith Ross** · 01-03-2009, 11:53 PM

Hello joebelou,

No problem. It will take a few minutes to add the buttons and code.

Sincerely,
Leith Ross

**Leith Ross** · 01-04-2009, 12:21 AM

Hello joebelou,

OK, the changes have been added. The revised workbook is attached.

Sincerely,
Leith Ross

**joebelou** · 01-04-2009, 01:32 AM

Hi Leith.
Looks like the delete is attempting to delete everything on the sheets "1st - 15th Summary Report" & "16th - 31st Summary Report" including lines 1 & 2 (the headings) before running the summarize macro. I'm getting a debug error stating the sheet is protected (which it is protected but only on the headings). When I unprotect the sheets and run the "Create Summaries" macro it removes all the data but it also removes row 2 that contains the headings. Everything else looks awesome! Thanks, joebelou

**Leith Ross** · 01-04-2009, 02:58 AM

Hello joebelou,

Sorry about the oversight on that. I had set the first row of the summary reports to row 2 instead of 3 and forgot about the password protection. All fixed and working now.

Sincerely,
Leith Ross

**joebelou** · 01-04-2009, 08:24 AM

Hi Leith.
The protection issues seems to be good now. However, the summary report is only pulling data from the patient sheet that is immediately to the left of the "Data" sheet. So, it is only adding the patient data from "Doe, John" to "1st -15th Summary Report" & "16th - 31st Summary Report" leaving "Doe, Jane" information off the Report sheets. I added another patient sheet "Doe, Tom" and then that was the only data added to the report sheets leaving "Doe, John" & "Doe, Jane" information off the report sheets.
Thanks, joebelou

**Leith Ross** · 01-04-2009, 02:42 PM

Hello joebelou,

This should be last correction. Check it over and let me know.

Sincerely,
Leith Ross

**joebelou** · 01-04-2009, 05:12 PM

Hey Leith. Everything is working great except for some reason when you select the button "Print All Patient Reports" it only prints the current sheet. It was working when I tested it before but there has been numerous changes since then so not sure what happened. Would you mind taking another look at this file? I know your probably tired of looking at it by now but I really do appreciate all your knowledge and help!
Not sure if I should start another thread for this but if so please let me know. I'm also trying to do conditional formatting for each of the "Discipline" values but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red.

Thanks again!
joebelou

**Leith Ross** · 01-04-2009, 05:52 PM

Hello joebelou,

I am testing the function right now. There shouldn't be a problem since I haven't altered that code in any of the changes.

Sincerely,
Leith Ross

**Leith Ross** · 01-04-2009, 09:07 PM

Hello joebelou,

I have spent the last 3 hours trying to post this workbook. Kept getting "Database Error" and then FireFox wouldn't let me do anything until it decided to release the internet. It finally disconnected and I logged back on. I moved the print routine to its own module. It should work correctly. It will display the Printer Dialog so you can set the printer up or cancel the printing before all the sheets are printed. The sheets will be printed in alphabetically order. You can make the changes to the workbook or wait until the system is working again..

Macro Code

Please Login or Register  to view this content.

Button Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**joebelou** · 01-04-2009, 10:33 PM

Hello Leith.

I created a new module named "PrintAllPatientReports" and copied the code from the last post into the code window and replaced the button code with the code posted. Is there something else I should do to make this work? It is giving me an error stating "compile error" and the debugger is pointing to the button code. Thanks, joebelou