Help with my Multi-Page UserForm

[eemiller1997]

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

[Bob Phillips]

Option Explicit

Private Sub buttonCancel_Click()
   Me.Hide
End Sub


Private Sub CustomerName_Change()
    Me.Label1.Caption = "Name " & CustomerName.Text
    Me.Label2.Caption = "Name " & CustomerName.Text
End Sub


Private Sub DoneButton1_Click()


   Sheet1.Range("A2") = Me.CustomerName
   Sheet1.Range("B2") = Me.Address
   Sheet1.Range("C2") = Me.Company_Name
   Sheet1.Range("D2") = Me.Email
   Sheet1.Range("G2") = Me.Male_Female
   Sheet1.Range("E2") = Me.Favorite_Color
   Sheet1.Range("F2") = Me.Size
   Sheet1.Range("H2") = Me.Height1
   Sheet1.Range("I2") = Me.Weight
   Me.Hide
   
End Sub


Private Sub NextButtonPage1_Click()
MultiPage1.Value = 1
End Sub


Private Sub NextButtonPage2_Click()
MultiPage1.Value = 2
End Sub


Private Sub PreviousButtonPage2_Click()
MultiPage1.Value = 0
End Sub


Private Sub PrevoiusButtonPage3_Click()
MultiPage1.Value = 1
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the 'Done' button!"
  End If
End Sub

Having the user have to exit via the Done button is bad, but having that on the 3rd page is awful.

You should have Next/Previous/done buttons off the multipage.

[eemiller1997]

Thank you for this information. I will give it a try right now.

I'm curious, I can understand why having the 'done' button only on the last page is a bad idea, but why is it not a good way to exit? Also, why should it be off the multipage? If it is, will it be part of the tab order for the multi-page?

[Bob Phillips]

A done button on the last page is bad because they have to select the last page if they want to exit and they are on some other page. You are forcing them an unnecessary click.

But it is also bad to not allow them to use the X button to quit, again you are forcing them to acknowledge the message, then select the last page, then click done, all to achieve exactly what the X button does.

[eemiller1997]

Ok, I've made the changes so far.

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

So, here are my next 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 while carring 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!

[Bob Phillips]

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.

But you still force the user to click Done to exit, I hate that.


Personally, I would have all of the navigation buttons off-multipage, so that if page 1 is in view Next is enabled, Pervious disabled, Page 2 both enabled, Page 3 Next disabled and previous enabled.

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?

Your use of the boolean test to set the enabled property is good, pity you test the wrong property for the wrong value. Test for

Width.Enabled = Male_Female.Value <> "Male"

3. In addition to those boxes becoming disabled, I want the cell in the worksheet to have a black fill.

Black fill the worksheet? What do you mean?

4. How do I have page 3 duplicate while carring content from some fields over and keep some fields empty.

Again, what do you mean?

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?

Do you? I don't like the way you have done it. On page 2 the label starts off as Name, but if the name boc gets a value on Page 1 you overwrite the label with that name. So it jsut says bill for instance.Then if I blank out Bill, the label on page 2 disappears. And you have a text box on pag 2 as well, what is that for?


A better way of doing it is to have the label and text box on page 1, two labels on other pages. The first label says Name and is fixed. The second lable starts blank and gets overwitten by the name text box value.


The rest yoou have lost me on (it is late here).

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

Lost again.

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.

Piece of cake.


BTW, you really should open up the multipage on page 1 when the button is clicked. It is disconcerting opening at 2 or 3.

[eemiller1997]

I tried to make the changes to the male/female disabling, but I didn’t understand where to place that code. I also didn’t understand the code. Width.enabled didn’t make sense to me. So, I wasn’t sure if I was supposed to erase what I had done or if I was supposed to add to what I had done or what. Please advise.

Let me explain the end purpose of this test form. Attached you will find another worksheet where I have created a Userform.

!Interview UserForm Test.xls

It’s only one page. It was while I was making this UserForm that I discovered I could have a Multi-Page Userform. That would work out so much better for our use for some of the following reasons. Currently, the one page UserForm works great, EXCEPT we typically have more than one candidate. So, I have to enter information and print, change candidate information and print, change candidate information and print. If I find I made a mistake on one of the previous candidates, I have to start over by re-entering that candidate’s information. So, having a page within the UserForm for each candidate as well as a worksheet tab on the excel spreadsheet will be wonderful. Here’s how it works…

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 (which would be page 3 of the UserForm). So…

…in the end, I will have a Multi-Page Userform and 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 (occasionally the interview team will change per candidate). 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 (in order to complete their pre-work checks I have to be reminded of the Req No, Job Location, and Job Code – hence the reason for simply repeating a couple of answers given on page 1 of the UesrForm). 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).

With regard to the underlining excel worksheet, all of this duplication will require inserting another worksheet into the excel spreadsheet identical to the first worksheet, but with the next candidate’s information. Also, depending if the candidate is an external or an internal candidate will determine which steps will be required for that candidate, hence, the example of having Male disable some of the cells in the UserForm. If you look at the attached single-page UserForm I’ve created, 3 cells have a black fill to remind the user those steps are not pertinent to the internal candidate.

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. (Sometimes, the interview team may be different from candidate to candidate, hence the reason for duplicating those answers onto the candidate page within the UserForm and allowing the answers to be changed.

I’m now done. I print all excel worksheet(s) 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.

Does that answer the reason for the black fill, the reason for duplicating a page on the UserForm and why some information will be carried over?

As far as the placement of the next, previous and done tabs…. I can understand why the Done should be off of the Multi-Page, but not why the rest should. As long as the user can enter data and tab through everything, reach the next field and click that using enter, and then continue entering data without having to reach for their mouse, that is great. It appears the Done button being placed off the page, is causing an unwanted hop in the tab order. I think it would be best to just have all three options on all pages of the Multi-Page than to have that hop at all. (Hop = the tab order goes from the Next button to somewhere else before going to “done”, that extra, unnecessary step is what I am calling a hop).

I really appreciate your help.

[Bob Phillips]

I tried to make the changes to the male/female disabling, but I didn’t understand where to place that code. I also didn’t understand the code. Width.enabled didn’t make sense to me. So, I wasn’t sure if I was supposed to erase what I had done or if I was supposed to add to what I had done or what. Please advise.

Sorry, my mistake, that should have been

Weight.Enabled = Male_Female.Value <> "Male"

[eemiller1997]

Great. I was able to correct the disabling when the Male was chosen from the drop-down box, but not when the male checkbox is selected. Since I'm not certain which option I will later go with, I want to make sure I have done this correctly. Please see attached.

!Multi-PageTest.xlsm

Next, How do I duplicate that third page? Or, how do I set up the drop down boxes as you recommended?

Thank you for your help!

[Bob Phillips]

Why do you have two ways to do one thing, a drop-down or checkboxes? If you have two, you should keep them synchronised, which is likely to create a perpetual loop (clickin the checkbox will trigger the checkbox event ,and set the dropdown from the checkbox event will trigger the dropdown event, which sets the checkbox value, which triggers the checbox event ad infinitum).

I still don't know what duplicating the 3rd page means, nor do I recall saying anything about setting up dropdowns.