Macro prompts and user inputs

[aboveliquidice]

Hello all,

I am not new to excel - but I have never required the use of advanced excel features before today - so I apologize in advance. I am a health professional student (graduate level) assisting my preceptor in developing patient management mechanisms.

I am building an excel spreadsheet to manage current medications for several patients.

I have built the spreadsheet with the first 'sheet' being a directory and subsequent sheets being patient data files(one sheet per patient).

I am now in the process of creating a macro to build new patient data files (a new sheet for a new patient). I recorded a macro to build the new sheet with the proper formatting of information. What I am trying to do now is automate the naming and linking of the new sheet.

What I am really after is how can you create a "prompt" such that after activating the macro button it asks:

Patient last name:
Patient first name:

Then uses this data to:

1. Rename the newly made sheet to 'last, first'
2. Title the newly made sheet with 'last, first'
3. Create an entry in the directory in the 'last, first' format
4. Finally, build the hyperlink from the directory to the newly created sheet

Any and all help you can provide would be greatly appreciated. I understand you are all busy professionals, and I am thankful for any assistance I can get.

[Leith Ross]

Hello aboveliquidice,

The attached workbook is far from being comprehensive for your needs, but it does demonstrate the basic approach to your problem. VBA UserForms are by far the best way to manipulate data in database.

After you open the workbook, click the button on "Sheet 3" to activate the form. You can then add a new patient worksheet using the names you have entered. When the sheet prints, the patient's name is in Arial 24 Point bold type and centered on the page. You change the font and size to what you want to use. They are highlighted in red in code below.

Here is the code behind the UserForm...

Please Login or Register  to view this content.

[aboveliquidice]

Hello aboveliquidice,

The attached workbook is far from being comprehensive for your needs, but it does demonstrate the basic approach to your problem. VBA UserForms are by far the best way to manipulate data in database.

After you open the workbook, click the button on "Sheet 3" to activate the form. You can then add a new patient worksheet using the names you have entered. When the sheet prints, the patient's name is in Arial 24 Point bold type and centered on the page. You change the font and size to what you want to use. They are highlighted in red in code below.

Here is the code behind the UserForm...

GREAT CODE

Leith, Thank you for that great code - It really demonstrates the concept well...

Where does the code from the UserForm live? I pull up the macro, but it only shows the command to invoke the UserForm. I can understand the coding that you have provided - just not where it lives in terms of adding it to my current spreadsheet.

I can tell this is a newbie question - if in doubt - please point me in the right direction.

Thanks again.

[Leith Ross]

Hello aboveliquidice,

To see and change the UserForm code, you need to activate the Visual Basic Editor.

1. Open the workbook and press Alt+F11 to open the VBE
2. In the upper left corner of the VBE you should see a window titled Project. If not press Ctrl+R
3. Look for the Forms folder in the Project window. If it isn't open then click on the "plus sign" to left of the folder.
4. Left Click UserForm1 to select it.
5. Press F7 to view the code.

[aboveliquidice]

Hey Leith,

I did manage to find the code - I then transposed the coding for the recorded "new sheet" code into the UserForm. I had no idea code could live in the form instead. Very neat.

I now have the codes merged such that clicking the "new patient" button on the main directory page prompts for last and first (in the same manner in which you created the UserForm). With this data - it creates the new sheet in perfect format (my pre-existing macro) with the name of the patient now in the correct place, and the sheet renamed to the patient.

Thank you so much for helping me get this far.

Did you have any advice for the creation of a table utilizing the PatientName variable from the input? What I am thinking is building a simple table that has a list of all sheets in the spreadsheet excluding the directory sheet. Then automatically hyperlinking to each sheet. There are other columns of the table that will require manual input - so only one column would need to be in the code / automated...

What do you think?

EDIT:

I located a nice little macro from a message board to find all the sheets and output them to a message box.

Please Login or Register  to view this content.

After the Next command, I could remove the output to MsgBox for (s) and instead define the table range and fill in correct?

[Leith Ross]

Hello aboveliquidice,

Hyperlinks are fine if you want to jump to another program or have data that is stable in a document. In your case, patient sheets could be removed as well as added creating more more work to maintain the list. You can create a list of the all the patients on a UserForm and activate the selected sheet that way. The list will always reflect the worksheets that are in the workbook. Using the form to do this, provides the user with the freedom to move easily through hundreds or even thousands of patient sheets. The form can be displayed by user either using a keyboard shortcut or clicking a button.

Here is the code for the UserForm in the attached file. The sheet "Main" is not included in the list of worksheets. You can activate the macro by clicking the button or use the shortcut Ctrl+Shift+W.

Please Login or Register  to view this content.

[aboveliquidice]

Hey Leith - Thanks again for the great coding work...

I implemented your code for jumping to patient profiles. I was also able to rework a bit of your code to create a "Remove Patient" UserForm. It uses the same code to load the sheets - but for CommandButton2_Click it uses .ListIndex as part of the Sheets ().delete command

Please Login or Register  to view this content.

I also added Unload Me to remove the UserForm after execution (and applied the concept to the other UserForms). I am now working on simply providing a list of worksheets on the directory sheet - More of an FYI for whomever is using the database. With the "New Patient", "Jump To Patient", and "Remove Patient" forms built - The database is pretty close to being done.

As always - I appreciate your advice and mentoring nature.

[Leith Ross]

Hello aboveliquidice,

Good job on modifying the code to make a "Remove" button. I am glad I didn't include that piece. I had thought about it. What's is your plan for list on the worksheet?

[aboveliquidice]

Hello aboveliquidice,

Good job on modifying the code to make a "Remove" button. I am glad I didn't include that piece. I had thought about it. What's is your plan for list on the worksheet?

I am still working on ideas - The concept of populating a table w/ data is pretty common - Lots of people use pivot tables to display thereafter - I am still learning the usage of said tables.

What would you use to display the data??? The Table will have 3 columns: Number, PatientName, Day of pickup (specific day of the week). I am just looking for some simple code to draw the data in and display it alphabetically. It would be nice to link the "Day of Pickup" to the specific cell on the respective patient data file - but it isn't really a necessity.

One question I do have concerns forcing the user to enable macros...

I located a great code made by a Ken Pulls that opens the workbook to a "Macro" page that discusses the need to enable Macros. It also codes to hide all other sheets until Macros is enabled. This seems prudent considering the users lack of experience with excel. The code is as follows:

Please Login or Register  to view this content.

Is there any way better than a redirect? I was thinking of having a popup box from a user form that has an autorun when a sheet is opened - Thoughts?

[Leith Ross]

Hello aboveliquidice,

If this program will reside on your network and be used by others, you can make your project "trusted" by creating a digitally signed certificate. This will eliminate the macro warning dialog when the workbook is opened.

[aboveliquidice]

Okay - I am at a loss - I can build a tag on each patient file sheet to be designated as a table (really two data entries, one for name and one for date of pickup)... Then use the summarize with pivot table to build a new sheet with a table showing the patient's name and the day they pickup their medications.

However, I cannot replicate this for all of my patients - I can only summarize the data from one table on one sheet...

I have a feeling I am going about this wrong. Does anyone have a better solution?

Any and all help would be greatly appreciated.

[aboveliquidice]

I found some great code for a Table of Contents with Hyperlinks...

However, I am having trouble adapting it to my purposes.

Written by Dmc Ritchie and Bill Manville

Please Login or Register  to view this content.

The code above produces a good product - but It has way too many columns and language for various versions of excel (which I do not need)...

Thoughts?