My Multi-Page Userform - Please Help

Can someone please help me with my multi-page UserForm?

I have created a simple form to demonstrate and work-out what I am trying to do. After I get this figured out, I have a more complex form I must make and I only have a couple of weeks to do it. Your help would be tremendously appreciated.

This content and labels on this form mean nothing. They were just a means to experiment and figure this out.

So, attached is a UserForm (using Microsoft 2010).

This multi-page UserForm has information on page 1, page 2, and page 3.

1. The answer to Name on Page 1 needs to show at the top of Page 2 and Page 3
2. The ‘Next’ button doesn’t work. I haven’t even tried the ‘Previous’ button.
3. On Page 3, if the user clicks the box for ‘male’ or selects ‘male’ from the Male or Female drop-down list (I am trying both options to see which I prefer), then I don’t want the user to be able to enter Height or Weight and I want the cells where that data would enter into the excel worksheet to have a black fill.
4. I want a page 4 with all of the exact information and content that was on page 3, except the selection of their Favorite Color and their Size. (I saw somewhere online a designer had a way for the user to duplicate a page).
5. When the user duplicates page 3 to a page 4, to a page 5, or even to a page 6, is there a way for the excel spreadsheet to also add a worksheet duplicating the same content? Or, must the additional worksheets already exist?

Thank you ahead of time for your help!

!Multi-PageTest.xlsm

Ugh! I had typed up a thorough answer and then had to pause to take care of something. I returned and finished my response and posted. My answer disappeared and did not post. Apparently I had been logged out.

So, instead of re-typing it. I will go make the changes as you described above and post them with my explanation for the reason for duplicating a page of the UserForm at that time.

I appreciate your help!

Alright! I'm ready!

Attached is my updated Multi-Page UserForm. I am so excited to have this moving forward with success. Here are my next set of questions….. first, let me explain why I want it to duplicate able.

We schedule interviews for the company nationwide. We will receive a request from the hiring manager with a list of their selected candidates to interview. Sometimes it’s only one candidate, but most often it’s a number of candidates. Rather than create a UserForm covering a certain number of candidates and the user have too many, or worst, not enough, I would like them to be able to add another candidate page. So…

…in the end, I will have a Multi-Page Userform where the user can view the first page where it will list information pertaining to the job as a whole. The second page will contain information pertaining to the interview specs for that job opening. Both of those pages will typically apply to all candidates on the job, though some of them have a chance of changing (see question 5 below) – for instance, the names of the interviewers. Finally, the third page will contain all information for one particular candidate – for example, name, address, phone number as well as their pre-work information (research we conduct to confirm the candidate is hirable to the company and/or to the job). The idea is…

… user, for example – me, will receive the names of candidates from hiring manager – in this example three candidates. I will enter the information on page one pertaining to the job as a whole. I will move to the third tab to enter information for the first candidate – enter their information and complete their pre-work checks. Then, I will duplicate the candidate information (page 3 of the UserForm) and enter the information for candidate two into page 4 of the UserForm. Then, I will duplicate the candidate information (page 4 of the UserForm) and enter the information for candidate three into page 5 of the UserForm). Pre-work complete. Next….

….I save the form and wait for the hiring manager to get back to me with the interview specs (names of interview team, where the interview will take place, etc). When I receive that information, I re-open the saved excel worksheet and the UserForm and enter the information into page 2 of the UserForm. I’m now done. I print the excel worksheets and proceed with scheduling the interviews.

However! Before I get started with the messy process of creating this complex UserForm, I am practicing these techniques on this smaller and simple example.

Attached is my updated Multi-Page UserForm. Yay!

So, here are my questions…
1. I moved the ‘Done’ button outside of the multi-page, as suggested. However, when tabbing through the pages, ‘Done’ is not directly after ‘Next’ in the tab order. That would be frustrating for a user. I try not to lift my hand from the keyboard as much as possible.
2. On Page 3, I have changed the code so if Male is selected, Height and Weight is disabled. However, I have tested this and it stays disabled if Female is also selected. Also, it only works on the drop-down box and not the checkboxes. Do I just use the same code for them?
3. In addition to those boxes becoming disabled, I want the cell in the worksheet to have a black fill.
4. How do I have page 3 duplicate, carry content from some fields over and keep some fields empty.
5. I LOVE how the Name on Page 1 carries over as I wanted. LOVE, LOVE, LOVE that. Most of them will be like that, however, I anticipate that a couple of fields may need to be changeable. As it is right now, the code is calling the answer to Name on Page 1 to repeat as a caption on Page 2. What if I want the answer to Name on Page 1 to also repeat in the answer box on page 2, BUT still remain changeable in case they happen to be different in one particular case?
6. I am wondering if in order to have this duplication work as I intended, that a macro will need to be created (which is VBA code, right) that will not only duplicate page 3, but will duplicate the worksheet, too. Because in our use, we have what looks like a form on one worksheet in Excel. Other than the Values tab, each additional tab would be another candidate.
7. I’ve decided I will also want a ‘Print’ button, so all the user has to do is click it and it will print all of the worksheets in the workbook (except the Values tab) – but, we can tackle that one later. I just want to make sure it will be possible.

!Multi-PageTest.xlsm

Thank you again for your help!

Yes, I would end up with 18 pages.

I would duplicate page three becauase Page 1 and Page 2 apply to ALL candidates, while the third page only applies to each individual candidate.

A drop down box on Page 3 would work, too, but each candidate listed in the drop down box would have to somehow be applied to the underlying worksheet on the excel worksheet. I would want to make sure we have a way to print each worksheet out for each individual candidate. Would that still work? Also, I would like to add a "print" button to the Multi-page UserForm so when the user has finished entering all data for everything, including all individual candidates, all they have to do is press the print button and it will print out the underlying form in Excel for each candidate.

I am happy to consider your suggestion, I just want to make sure it will work and be user friendly.

See this example of a UserForm I created with all information on ONE page. Some of that information applies to ALL candidates within that job opening, while the other 1/2 applies to an individual candidate and will or could change from candidate to candidate.

!Interview UserForm Test.xls

Thank you for your help!

The data is stored in the UserForm and on the underlining worksheet that looks like a form. It must be printed as it appears and then used by the coordinator scheduling the interviews.

As far as the Multi-page goes, it is possible to have the form as one page, and then duplicate that one page so the user can have a second candidate - thus still creating a multi-page userform.

someone has also suggested I entertain the idea of using drop down box where I could select the name of the other candidates and the UserForm then fills with that candidate's information.

Either way, I only want to enter Job Information, Hiring Manager Information, Interview Information, Team Information and Pre-Work Information once and make changes to that information within each candidate as necessary (as sometimes the team information or pre-work information is NOT the same for each candidate).

Thank you for your help!

I had actually thought of that and played around with the formatting for such an idea, but I didn't like the result.

I created an Excel Macros to experiment changing the fill in a cell. The resulting code is below. All I would need to do is plug in the three cell ranges I would want to have a black fill if a certain option is made within the UserForm. Now, I need to know how to connect the IF function to this code? If a person, for example, selects 'Male', then the cell on the Excel Worksheet will recieve a black fill. This will allow the end user, when the worksheet it printed, to remember those three items are unecessary to cover for this person.

Sub Black_Fill()
'
' Black_Fill Macro
'

'
    Range("I6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 4.99893185216834E-02
        .PatternTintAndShade = 0
    End With
End Sub