Excel VBA - Dynamically Adding a Page to a Multi-Page UserForm

I have a Multi-Page UserForm in Excel 2010. I want there to be a field for the user to specifiy how many additional pages they need to add to the form (each additional page will reference a number of people).

The UserForm currently has three pages and the user will only need to duplicate page 3. I found the following information online,

Dynamically Add/Remove a Page:

Using the Add Method: Syntax: Set m = MultiPage1.Pages.Add(pageName, pageCaption, pageIndex). pageIndex (optional) is an integer which specifies the position for the Page to be inserted, starting from 0 for the first position/Page. pageName sets the Name for the Page, pageCaption sets the Caption. Both are optional to specify.

Set m = MultiPage1.Pages.Add("Page5", "NewPage", 1) - this code adds a new Page with name Page5 and Caption NewPage, as the second Page (viz. second position in the page order).

MultiPage1.Pages.Add "Page3"; MultiPage1.Pages(2).Caption = "NewPage" - these 2 codes add a new (third) Page with name Page3 and set its Caption to NewPage.

MultiPage1.Pages.Add - this code simply adds a new Page.

To Remove a Page - Syntax: MultiPage1.Pages.Remove (pageIndex). Example: MultiPage1.Pages.Remove (1) - this code removes the second Page.

So, to test this I created a button and below is what I put into my code, but this hasn't been working. I've been getting a Compile Error: Method or data member not found. The name of my worksheet is 'IntvwWorksheet' and the name of page 3 is 'Candidate1'.

Private Sub AddCand_CommandButton_Click()
    Set M = IntvwWorksheet.Pages.Add("Candidate2", "Candidate 2", 4)
    
End Sub

Can someone help me with this. Since I want the user to be able to say they want to add, for example, 5 more duplications of page 3, or 10 more duplications of page 3, it would be helfpul to know how to specify this as it may not always be Candidate2 or page insertion at 4.

Currently, I have this as a button, but again, I would like a field where the user can simply specify how many they want added.

Thank you for your help.

Is anyone able to help me with this part? It's the toughest part and I'm getting stumped with how to proceed.

If this is in a userform as I assume it is, why are you referencing a worksheet? Shouldn't you be referencing the multipage control?

Because, for each added Page 3, I would also need an Worksheet, I would assume. Unless, when the user presses the print button, I am able to set the code to print one by one, each worksheet for each page added.

The worksheet has what looks like a form that the UserForm will help the user to input data. Then, the user will print the final result for use as needed.

I'm confused, worksheets have nothing to do with userforms.

I think you want something like:

Private Sub AddCand_CommandButton_Click()
    Dim m As Object
    Set m = MultiPage1.Pages.Add("Candidate2", "Candidate 2")
    With ThisWorkbook.Worksheets.Add
        .Name = "Candidate2"
    End With
End Sub

This worked and didn't work at the same time. It added another sheet, but it didn't carry any of the set-up from page 3, Candidate 1 over to Candidate 2. Not only does another page need added, it needs to carry over the userform on page 3 to page 4 and so on so the user can enter the second candidate's information.

I'm not sure of the correct termonology. All of the labels and value fields from page 3 should duplicate to page 4, page 5, page 6, etc.

Kyle, thank you for that. I will give it a try. Will the name change as the number of additions are made? For instance, right now you have Candidate 2, but if the user does this a aga and again, will there be Candidate 3, Candidate 4, etc?

The reason for the worksheet, is there is currently what looks like a form on the worksheet. There is one worksheet for each person/candidate. If there is a second candidate added to the UserForm, shouldn't there be a second worksheet created as well for the second candidate? Else, where would the first and second, third, fourth, and so on, candidate's information go after the UserForm is completely filled out the data entered onto the worksheet?

My UserForm is not the customary tabulor format. This content will be placed onto the excel worksheet accordingly. It's designed in such a way to save the user time and make things much simplier for inputing information into a excel spreadsheet they already use.

Norie, Yes.

And, to recreate the worksheet at the same time. Previously, I had almost figured that part out as follows:

    Sheets("Candidate (1)").Select
    Sheets("Candidate").Copy After:=Sheets(Sheets.Count)
    Sheets("Values").Select
    Sheets("Values").Move After:=Sheets(Sheets.Count)

Yes. This is what I need.

Are you suggesting the combobox be on the UserForm? That would work. As long as,...

1. If the user needs to go back to another candidate, they can easily do so.
2. In the end, after everything is completely filled out and each candidate has been added with all of their information, the User will need to be able to print all of the worksheets at once for all candidates.

How would I go about setting this up? Here is the updated example:

Multi-Page Trial Run.xlsm

Yes, you did.... hehehe. I'm finally convinced. You must have been laying in wait for me to come around to it, huh!

I can't provide real data, only fake data. I wonder how much data you need - just candidate and team information? I will assume so….

Okay, job 123XYZ will use these interviewers:

IntA: Superman
IntB: Spiderman
IntC: Wonderwoman
Alt1: Bugs Bunny
Alt2: Elmer Fudd
Alt3: Wiley E. Coyote
Host1: Green Giant
Host2: The Hulk

They want to interview the following four candidates:

Candidate 1
John Smith
1234 Main Street
Hometown, USA
123-456-7890
JohnSmith@email.com
Employee
Step_Status = Yes
Nepotism = OK
Relo needed = No
Resume Saved = Yes
Full-time or Eligible = FT-Reg
Use Same Intvw Team = Yes
Employee Ext = 5-1234
Location = China
Shift = First
PSID = 123456

Candidate 2
Jack TheBeanstock
5678 Main Street
Hometown, USA
098-765-4321
TheBeanstock@email.com
Employee
Step_Status = Yes
Nepotism = OK
Relo needed = Yes
Resume Saved = Yes
Full-time or Eligible = FT-Temp
Use Same Intvw Team = Yes
Employee Ext = 6-7890
Location = England
Shift = First
PSID = 789012

Candidate 3
Suzy Q
9784 Main Street
Hometown, USA
456-789-1230
SuzyQ@email.com
External
Step_Status = Yes
Nepotism = OK
Relo needed = No
Resume Saved = Yes
Status in Taleo = OK
Status in PS = OK
Full-time or Eligible = Eligible
Status in EDPM = No
Use Same Intvw Team = No
For Interview Team instead use:
IntA: Wiley E. Coyote
IntB: Spiderman
IntC: Bugs Bunny
Alt1: He Man
Alt2: Elmer Fudd
Alt3: Porky Pig
Host1: Green Giant
Host2: The Hulk

Candidate 4
Mary Poppins
05678 Main Street
Hometown, USA
654-987-3210
PoppinsM@email.com
External
Step_Status = Yes
Nepotism = OK
Relo needed = Yes
Resume Saved = Yes
Status in Taleo = OK
Status in PS = OK
Full-time or Eligible = Eligible
Status in EDPM = No
Use Same Intvw Team = No
For Interview Team instead use:
IntA: Wiley E. Coyote
IntB: Spiderman
IntC: Bugs Bunny
Alt1: He Man
Alt2: Elmer Fudd
Alt3: Porky Pig
Host1: Green Giant
Host2: The Hulk

Thank you bunches!

Have you had a chance to look at this? I'm getting so excited that I'm nearing the finishing line for this UserForm! Just that last part and the print button is all that is left!

Because I've been playing around with the layout - putting the fields into Frames and correcting code, I thought I should post an updated version....

Multi-Page Trial Run.xlsm

Thank you again for your help.

A Combobox would be fine.... that's when they would click a drop-down arrow and see a list of names, right? Also, the name of the candidate is who they should see.

Wouldn't it be feasable for them to have two ways to add candidates....
1) A box where they say I need "10" candidates - so in the intial stages when they know they have 10 candidates they don't have to add them one-by-one
2) an option to add another candidate - so later if they find they need to add 1 more candidate for a total of 11, they can do that, too.

Finally, - just to look ahead, a Print button that prints each of the candidates individually. But, there should also be the option to print ALL candidates and another option to print just one or two candidates - just in case they are printing a candidate or two where corrections had been made or had been added.

I feel like I'm waiting for Christmas. I love this stuff.

1. Well, I don't know how the drop-down box you have proposed and are working on would add candidates.

2. I'm told it's probably a good idea to have a cap so the user doesn't add 100 candidates and slow the program down. A cap of 25 would be fine.

3. what do you mean by a seperate userform? Will that be a hidden userform or coded behind the 'print' button?

Oh, I see how you commented out the done button. How will the program know where to put the content on the underlying Excel Worksheet when it prints? Or, is this still in the testing stage?

Yes, that looks great! I see now how the Full Name has a drop down box, and I see the code you used for cboCandidate. so, you must have added the four candidates a different way since there's not an Add candidate button. Oh, I also see there is something under the Initialize, too. Would you be able to add comments to let me know what changes you make and what you do? Since I've even updated the form since I gave you the one you've been working on, later I'll need to combine the two and I don't want to miss something.

I was told somewhere along the line in my research that having too many pages in a Multi-page UserForm could bog Excel down... but, this isn't adding pages, this is a drop down box - so it may be a different story.

I like your idea for printing. That will work

I truly appreciate your help with this!

Alright. Great. I look forward to the next step!

I have been so swamped today that I haven't had a chance to touch base with you and see how this process was going. I know you are probably having to look deeper into how everything works. I will just look forward to talking with you on Monday. Does that work for you?

I appreciate your help!!

Hi Norie, how is it coming along? I know there is a lot of things to figure out, I just want to touch base with you to know how it's coming along. Do you know how long it will take (not rushing, just looking for an idea so I don't keep bothering you )

Oh, I think I understand what you are saying. So, when a user is done, the candidate's information is collected from "CandidateDate" to the 'CandidateTemp'

Should ALL information from the UserForm be directed to CandidateData instead of the form?
Should I recode the If Then statements to go to CandidateData, too, instead of straight to the form from the UserForm?
After figuring out how to allow the user to add more names from the UserForm, how will the user print each candidate at that point?

Okay. Great. There are a few If Then Statements.

One for Employee, Agency, External.
Use the same team or not
Relocation

Just off the top of my head. I may be adding a few more.

Is the print button supposed to work yet? When I tested it, something flashed quickly, as if it were going through the act of printing 4 times, but nothing came out of my printer, so I am assuming it's not going to work yet. If that's the case, what's missing?

This is so exciting. I love this!

Oh, the 'Print Candidates' dialog box. Yay! That's perfect. I would assume when the list becomes longer than the available box, a scroll bar will be revealed?

Yes, waiting until the basic part of the form is completed will be fine. I'm thinking ahead.

Here is an attachment that shows all of the information inputed.

Multi-Page Trial Run.xlsm

Ok, I have updated everything to the degree I know how, including the print button and the combo box. I have added worksheets to store the data and updated the fields in the cboCandidate.ListIndex.

There were a couple of things there I need to figure our regarding the If Then Statements, but that will be figured out later, so I commented them out.

I used the same list of candidate you had inputed and completed the information. So, there are four candidates. Though, when I test the combobox in the UserForm, it doesn't show a list of candidates. Where have I gone wrong? (Please see attached)

When I click the 'print' button on the UserForm, I get a 'Compile error: Variable not defined' and it then highlights the sectoin below. Where did I go wrong?

Multi-Page Trial Run.xlsm

Private Sub UserForm_Initialize()
    With Worksheets("CandidateData")
        cboCandidate.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    End With
End Sub

Oh, I forgot. I also added a Notes to Tab 2. I split the notes sectoin on the Excel form so the top sectoin is for the Job Information and the bottom section is for the candidate. Not sure if you were concerned about that kind of change.

Thank you

Norie, I tried simply updated the "Print Button UserForm: version you had sent me, but it's also coming up with an error. So, somewhere along the line I'm doing something wrong. I didn't even make it very far. I simply updated the CandidateData worksheet with all of the fields we needed and then made sure the code for the cbo had the correct columns associated with the correct value field. Perhaps if each piece of the code were explained to me I could find the problem. I don't know. I just need to get it figured out.

Thank you for your help!

Where should I start. First, I would think to know why it's not working... both the CBO and the Print button. Then, I throught that process I will probably be able to get answered those above questions. Next, would be the add new candidate. Finally, the If Then Statements. Then, that should be about it. So close to finishing this.... I'm anxious to finalize it.

The attachment from Wednesday is still not working. I can't seem to figure it out and I've been scouring the internet for direction. I thought I would look at the example you provided and copy and paste it into my Userform and update field names and placements, etc. But, it didn't work. You didn't have time to look at it that night to figure out where I had gone wrong. So, while I waited I was trying to use the version you had provided to update it to my needs, but my changes caused it not to work, too. So, I'm back to trying to figure out where I had gone wrong in the recreation of your version to mine. In the version you made, it works great (minus the add button not yet coded), but there were missing fields that I had provided you, so I added them to the worksheet, "CandidateData" in the order I wanted them in. Then, in the code, I added those fields and their associated column headings. I need to be able to make adjustments to it and understand how it works for the possibility of future customizations. But, after that was all done I would go to the UserForm and the ComboBox for Candidate Name didn't have any list of candidates in it. So, something I had done had messed with whatever code told the combobox to take from that list of candidates in the CandidateData worksheet. And, then, the print button no longer worked either. Did you have a chance to look at the document I had attached Wednesday to see where I had gone wrong?

As far as the questions, What causes the combobox to take from the names on that worksheet? What code causes the data inputed into the userform to go to that worksheet? What causes the combobox to connect to the rest of the selection on the combobox, as my research all I can find it how to add options to the combox independently of the rest of the form and this is set up as one - the combobox selection is tied to all of the rest of the inputed data on the candidate information. I'm not sure where in the code it tells it to do that.

Thank you for your help!

Originally Posted by Norie

Are wondering how the rest of the form is populated when a candidate is selected?

Yes, please

Have you had a chance to look at the above attachment to see where I went wrong?

Yes, that makes sense. Thank you for that. I'm going to go through this with a fine tooth comb. Any idea, though, why mine isn't working?

It is no pain at all because I appreciate your help. Here is the current version and I've renamed it so we know it's the combobox and the print button with the troubles.


ComboBoxPrintBox_Troubles.xlsm

Alright. I see what you were saying. I had cbo where lst should have been in the frmPrintCand module. I've changed that as well as added the initialize as you have above for the IntvwWorksheet module, but there is still something wrong and for both forms now. I'm attached v2 for your review.

ComboBoxPrintBox_TroublesV2.xlsm

Okay, I'm confused. I've fixed the typo under the frmPrintCand module, however did you say to put that other code in the IntvwWorksheet module? Isn't that what I did? Where did I misunderstand?

ComboBoxPrintBox_TroublesV3.xlsm

I know that's what you said, what is confusing me is how is that different from what I did?

IntvwWorksheet.jpg

Ugh! I can't believe I did that. I blame it on my cold. I've fixed that, but I'm still getting an error when I simply test to see if the form is working correctly. Any ideas?

ComboBoxPrintBox_TroublesV4.xlsm

Yay! It's finally working. My brain is just not working 100% today. Thank you for that!

What will need to change with the If Then Statements with the set up like this? Because, instead of using a 'done' button, the cboCandidate.List is coded to tell excel in what column of CandidateData to place the answers. How do I handle the If Then Statements, then?

For example, with relocation the code for an answer 'yes' is as follows:

Private Sub YesRelo_OptionButton_Click()
Dim score As String

    score = YesRelo_OptionButton.Value
    
    If score = True Then
        Range("I37").Value = "Travel/Hotel Authorization Made"
        Range("N52").Value = " "
        Range("N37").Value = " "
    Else
        Range("N37").Value = "*****"
        Range("N52").Value = "*****"
        Range("I37").Value = "No Travel Necessary"
    End If
            
End Sub

and if the answer is 'no', the code is as follows:

Private Sub NoRelo_OptionButton_Click()
Dim score As String

    score = NoRelo_OptionButton.Value
    
    If score = True Then
        Range("N37").Value = "*****"
        Range("N52").Value = "*****"
        Range("I37").Value = "No Travel Necessary"
          
    End If
End Sub

So, all of the If/Then's will still work just as is? I guess I had understood the code for the CboCandidate sent the data to the worksheet CandidateData and then the Print userform pulled the information from the worksheet CandidateData into the Form. That must not be correct, then? Because I have all of the If Then's coded to place certain answers within particular cells within the form.

If the If/Then's all work as is, then there are two more things to complete this project:

1. How a user adds a candidate.
2. Making the Print button actually print.

I have uncommented that line and tested the print button. I received a Run-Time error '9' Subscript out of range. Then, it pointed to this line:

 Worksheets("CandidateTemp").Copy After:=Worksheets(Worksheets.Count)

Where it belongs, and probably in a different form, is in the code that transfers the candidate data to a form.

Isn't the code that transfers the candidate data to a form currently stored under the worksheet called 'CandidateDate' see V5 attached. Previously, I had updated that worksheet with all of the columns necessary, to this point, for all data to be collected from the user for the Candidate. I renamed the other worksheet that collects the data for the job and interview information to 'JobItvwData'

So, under 'CandidateData' there is a column for 'Relo needed' and 'Travel authorization', perhaps there is also necessary for a column for the answers ***** as outlined in the above code for the If Then Statements for relocation or travel necessary?

Can you walk me through one of these? To place it exactly where it needs to go and how to set it up so I can duplicate that method for the rest of the If Then Statements?

ComboBoxPrintBox_TroublesV5.xlsm

Just to clarify, I don't want the user to have to go to a different form altogether to input these if then answers.

Yay, the print now works.

Thank you for pointing out you had uploaded something. I've looked at what you've uploaded. Testing the form, the user selects 'NewCandidate'? Is that how? When I tried that, it gave the new candidate information already entered into the UserForm. Also, when I tried to change the name for New Candidate and information, it didn't add my new candidate to the list. When I tried to click, Add/Update button, it didn't provide anything, either.

With regard to the columns for the ***** answers, I am confused by this, but will wait to see this in action to see what you mean. As long as the user can say Candidate Angie Johnson needs relocation and Candidate John Doe doesn't and the proper answers are placed in the worksheet along with the corresponding ***** in the correct cells for the appropriate candidate, I'm happy

Oh, I understand now, one adds a new candidate and uses the 'add/update' button to add him/her to the list. That makes sense. I would prefer there be no 'default' values, just simply an empty form for the user to input data. Since the form is a measure to insure all information has been researched and entered, if the user comes across content that defaulted, gets interrupted for any reason and returns to find that field already inputted, they may think they had already researched that piece and mistakenly move on.

I need some help understanding something. When I used the print button it printed the four forms for the four test candidates already there. The information from the UserForm is not in the correct place within the printed form. For instance, 'No Travel Necessary' is in the cell for something else. This is the case for most of the form. Also, none of the Job Information and Interview Information is on the form at all.

looking at the code, it appears this is the code telling the system where to place the Interview Information and Team Information:

Private Sub FillForm(wsForm As Worksheet, rngCand As Range)

    wsForm.Range("N46") = IntvwWorksheet.ReqRcvd
    wsForm.Range("K7") = IntvwWorksheet.ReqNo
    wsForm.Range("K1") = IntvwWorksheet.Recruiter
    wsForm.Range("K6") = IntvwWorksheet.HM
    wsForm.Range("K10") = IntvwWorksheet.Job_Title
    wsForm.Range("O8") = IntvwWorksheet.Relo_Avail
    wsForm.Range("K9") = IntvwWorksheet.JobLocation
    wsForm.Range("O7") = IntvwWorksheet.JC_SG
    wsForm.Range("K8") = IntvwWorksheet.Business_Unit
    wsForm.Range("O6") = IntvwWorksheet.HM_Called

    '    Intvw Info & Team Tab

    wsForm.Range("N49") = IntvwWorksheet.TeamBuilt
    wsForm.Range("L12") = IntvwWorksheet.Intvw_Type
    wsForm.Range("L13") = IntvwWorksheet.Conf_Rm
    wsForm.Range("M13") = IntvwWorksheet.Help_Rm
    wsForm.Range("L14") = IntvwWorksheet.Intvw_Loc
    wsForm.Range("O9") = IntvwWorksheet.Time_Zone
    wsForm.Range("L15") = IntvwWorksheet.Deadline
    wsForm.Range("J19") = IntvwWorksheet.IntA
    wsForm.Range("J20") = IntvwWorksheet.IntB
    wsForm.Range("J21") = IntvwWorksheet.IntC
    wsForm.Range("J22") = IntvwWorksheet.AltIntA
    wsForm.Range("J23") = IntvwWorksheet.AltIntB
    wsForm.Range("J24") = IntvwWorksheet.AltIntC
    wsForm.Range("J17") = IntvwWorksheet.Host1
    wsForm.Range("J18") = IntvwWorksheet.Host2
    wsForm.Range("B48") = IntvwWorksheet.ReqIntvw_Notes

I understand that as it's saying the field names within the UserForm named 'IntvwWorksheet' go into those fields on wsForm (I don't know what wsForm means, but I'm assuming it's the worksheet form?). But, why did none of the data go into those cells?


And, it appears this is the code telling the system where to place the candidate information:

wsForm.Range("C2") = rngCand.Value
    
    ' no data for ID was included
    'wsForm.Range("F3") = Me.Cand1_ID
    
    wsForm.Range("F4") = rngCand.Offset(, 6).Value
    wsForm.Range("N25") = rngCand.Offset(, 6).Value
    wsForm.Range("N27") = rngCand.Offset(, 7).Value
    wsForm.Range("N26") = rngCand.Offset(, 8).Value
    wsForm.Range("C3") = rngCand.Offset(, 3).Value
    wsForm.Range("F2") = rngCand.Offset(, 4).Value
    wsForm.Range("C4") = rngCand.Offset(, 5).Value
    wsForm.Range("F5") = rngCand.Offset(, 10).Value
    wsForm.Range("N29") = rngCand.Offset(, 17).Value    ' Me.Cand1_Taleo
    wsForm.Range("N28") = rngCand.Offset(, 18).Value
    wsForm.Range("N31") = rngCand.Offset(, 11).Value    'Me.Cand1_EligFT
    ' don't know what JC is
    'wsForm.Range("N30") = Me.Cand1_JC
    wsForm.Range("N39") = rngCand.Offset(, 19).Value
    wsForm.Range("J3") = rngCand.Offset(, 13).Value
    wsForm.Range("N3") = rngCand.Offset(, 14).Value
    wsForm.Range("J4") = rngCand.Offset(, 15).Value
    wsForm.Range("N4") = rngCand.Offset(, 16).Value
    wsForm.Range("B50") = rngCand.Offset(, 28).Value

    wsForm.Range("J19") = rngCand.Offset(, 20).Value    'Me.Cand1_IntA
    wsForm.Range("J20") = rngCand.Offset(, 21).Value    'Me.Cand1_IntB
    wsForm.Range("J21") = rngCand.Offset(, 22).Value    'Me.Cand1_IntC
    wsForm.Range("J22") = rngCand.Offset(, 23).Value    'Me.Cand1_Alt1
    wsForm.Range("J23") = rngCand.Offset(, 24).Value    'Me.Cand1_Alt2
    wsForm.Range("J24") = rngCand.Offset(, 25).Value    'Me.Cand1_Alt3
    wsForm.Range("J17") = rngCand.Offset(, 26).Value    'Me.Cand1_Host1
    wsForm.Range("J18") = rngCand.Offset(, 27).Value    'Me.Cand1_Host2

This is what looses me as I don't know how to identify what is what. I do know it is taking information from CandidateDate worksheet, I just don't know how it knows, for instance, J19 is Cand1_IntA. It must have something to do with the number '20' as it's the only difference between each line of code, but what is that number refering to?

Well, it seems I've made a mess of things again. First, to understand the above question. Second, where to put my If Then Statements and Third, how to properly add/update candidates.

I've tried researching online and to look into the books I have on VBA to understand the above question, however what I run into is a problem where everyone explains the difference between a list and a combobox. Got it. Or, how to create values for the combobox to have. Got it. But, nothing explains how to connect the combobox to multiple fields on the same page of a userform, like Page 3 does. Or, how to code what is coded above. We enter an answer into a field on the UserForm and it's stored on a worksheet in the Excel file. Then, later placed into the form on the CandidateForm when user 'prints'. But, I can't find anything that explains this process and the underlying code. I'm lost and don't know how to fix things.

Norie, progress is being made! With some help, I learned in the following code,

wsForm.Range("F3") = rngCand.Offset(, 1)

The 1 is referring to an offset of the first column of the CandidateDate worksheet, every column having it's own number offsetting from the first column. With that in mind, I was able to put together the rest of the code and test print it. The results - everything is in it's correct placement that came from the worksheet named CandidateData. And, I've been able to successfully add candidates!!!

There are still two things to address and then I am done!

1) How to get the data from the worksheet called JobIntvwData onto each worksheet? I see how they are noted above within the frmPrintCand, however, they don't transfer like the CandidateData does. Not sure why not.

2) How to fit in the IF THEN statements. They are very important. They are currently not working within the UserForm, either.

Can you help me with both of those question?

Here is the updated version of the project:

ComboBoxPrintBox_TroublesV6.xlsm

Yay, part one of your answre above worked splendidly. I'm so excited. So close to finishing!!

Originally Posted by Norie

2 The If Then's shouldn't be in the userform code, they belong in the code for filling out the form.

Here's an example which goes in the sub FillForm.

    wsForm.Range("N29") = IIf(rngCand.Offset(, 2).Value = "EMPLOYEE", "*****", rngCand.Offset(, 11).Value)

Where should the THEN statement be? I don't see the THEN in the above code. Just before the "*****"?

Is there a shortcut to code multiple places with ***** if the answer is Employee? The original If Then Statement was as follows:

Private Sub Cand1_EmpStatus_Change()

    arrCandCtrlsExt = Array("Cand1_Ext", "Cand1_Loc", "Cand1_Shift", "Cand1_PS_ID")
    arrCandCtrlsEmp = Array("Cand1_Taleo", "Cand1_StatusPS", "Cand1_Edpm")

    
    For I = LBound(arrCandCtrlsExt) To UBound(arrCandCtrlsExt)
        With Me.Controls(arrCandCtrlsExt(I))
            .Value = ""
            .Enabled = True
        End With
    Next I

    For I = LBound(arrCandCtrlsEmp) To UBound(arrCandCtrlsEmp)
        With Me.Controls(arrCandCtrlsEmp(I))
          .Value = ""
         .Enabled = True
    End With
    Next I
    
    Select Case Cand1_EmpStatus.Value
    Case "External"
        For I = LBound(arrCandCtrlsExt) To UBound(arrCandCtrlsExt)
            With Me.Controls(arrCandCtrlsExt(I))
                .Enabled = False
                .Value = "*****"
            End With
        Next I

    Case "Employee"
        For I = LBound(arrCandCtrlsEmp) To UBound(arrCandCtrlsEmp)
            With Me.Controls(arrCandCtrlsEmp(I))
                .Enabled = False
                .Value = "*****"
            End With
        Next I
        
    Case "Agency"
        For I = LBound(arrCandCtrlsEmp) To UBound(arrCandCtrlsEmp)
            With Me.Controls(arrCandCtrlsEmp(I))
                .Enabled = True
                .Value = " "
            End With
        Next I

    End Select

End Sub

Notice also that the fields within the Userform also became disabled based on the If Then statement. Where will that be done?

oops, one more question in addition to the above. As I was further testing the userform, starting with Tab 1 and entering information, how does that information go to the JobIntvwData worksheet? Since, the print button is coded to pull from that how does the user get the information there?

Another issue is when I entered a name into the candidate combobox that was similar to a name already there, for instance, Joseph ______ the previous Joseph's information came up. How do we prevent that from happening?

I didn't want to pick a Joseph. I was pretending to type in a new Joseph and it auto-populated with the already inputed Joseph before I could get to typing in a last name. After I did type in a last name different from what was there, the auto-population remained. What do you recommend? Then, I couldn't undo that action, so perhaps a clear button would be a good idea. What do you think? If so, how would a clear button be coded?

I'm still completely stumped by the If Then Statements for this new set up. If they are not to be in the new format, or if they are going to be in the new format, how do I address everything and where?

Alright, that makes sense. I have not yet removed the If Then statements from the UserForm and I noticed while using the userform, the If Then statements are not happening on the userform right now, but perhaps removing some of the other stuff will enable that. Let me give that a try and I will get back to you.

I will make that change to the property. Thank you !

What do you mean? To control which part?

How can I get the information from tab 2 and tab 3 to go to the JobIntvwData worksheet and then pulled from there when 'printed'?

Yes, the interview team belongs to the job, but quite frequently we are told that different interviewers will cover different candidates. So, I added a section within the candidate's area on Tab 3 asking if the same team would be used. If yes, then the section autfills with the team already placed on Tab 2. If not, then it's left blank for the user to enter the names of the team that will be used.

That's fine and would save time and was my initial plan - however, I had found that if it's coded to duplicate from tab 2, then it wouldn't allow the user to overwrite it. That was the topic I had asked about during another post in this forum. But, if you say it will work, I will trust you. Just let me know how to set it up that way.

I figured out how to code the information under the add/update tab so the job information and interivew/team information go onto the form at the push of the add/update button. Now, I just need to code it so it goes to the JobIntvwData worksheet instead. I think I can do that myself. What I need to know is how to keep the information from disappearing from the userform after the user x's out of it?

Ok, Let me attach my updated version- V7, please.

ComboBoxPrintBox_TroublesV7.xlsm

You tell me how to do it correctly, and that's how we'll do it. Just so long as if the team isn't the same, the user will see the team on the Candidate Page so they can make the appropriate changes.

I did create a worksheet called JobIntvwData where I want the Job and Interview information to be stored.

Oops, I forgot to clarify, I did code it to go onto the form, but that was just to see if it was even going to work. I do want the info to go to the JobIntvwData worksheet for storage.

I realized in order to have the If Then Statements place the answers into CandidateData worksheet, then I needed to put the If Then Statements under the 'Private Sub AddUpdateCand_CommandButton_Click()' with everything else.

Is this the correct way to convert this If Then Statement?


This....

Dim score As String

    score = YesRelo_OptionButton.Value
    
    If score = True Then
        Range("I37").Value = "Travel/Hotel Authorization Made"		'H
        Range("N52").Value = " "					'AF
        Range("N37").Value = " "					'AG
   End If
            
End Sub

to...

If YesRelo_OptionButton.Value = True, Then .Range("H" & idx) .Value = "Travel/Hotel Authorization Made", .Range(AF & idx) .value = " ", .Range(AG & idx) .value = " " End If

I did add those two columns for those two answers. I just added them to the end so I didn't have to redo everything else right now.

I had thought the IIf was a typo.

If an answer of TRUE is given, it's okay to have three different ifs resulting from the same value? Such as...

IIf YesRelo_OptionButton.Value = True, Then .Range("H" & idx) .Value = "Travel/Hotel Authorization Made", else "No Travel Necessary"
IIf YesRelo_OptionButton.Value = True, Then .Range("AF" & idx) .Value = " ", else "*****"
IIf YesRelo_OptionButton.Value = True, Then .Range("AG" & idx) .Value = " ", else "*****"

I will look for your help tomorrow on what you were going to do with the attachment.

Hi Norie, Did you have a chance to look at that last night? Did I get that If Then Statement correct in post #103? Thank you!

Oh, Ok. Will do. Thanks. I will proceed with working on this. After you have a look at the attachment, please let me know what changes you make so I can transfer them to my working document

Hmmm, I have added my If Then Statements to the "Add/Update" button and I am getting a "Compile Error: Expected End With" and then it hightlights the 'Private Sub AddUpdateCand_CommandButton_Click()'

'If Then Statements
            If YesRelo_OptionButton.Value = True Then
            .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
            .Range("AF" & idx).Value = " "
            .Range("AG" & idx).Value = " "
            Else
            .Range("H" & idx).Value = "No Travel Necessary"
            .Range("AF" & idx).Value = "*****"
            .Range("AG" & idx).Value = "*****"
            End If
            
            If NoRelo_OptionButton.Value = True Then
            .Range("H" & idx).Value = "No Travel Necessary"
            .Range("AF" & idx).Value = "*****"
            .Range("AG" & idx).Value = "*****"
            Else
            .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
            .Range("AF" & idx).Value = " "
            .Range("AG" & idx).Value = " "
            End If
            
            If Cand_EmpStatus.Value = "Employee" Then
            .Range("L" & idx).Value = "*****"
            .Range("M" & idx).Value = "*****"
            .Range("P" & idx).Value = "*****"
            .Range("Q" & idx).Value = "No Drug Necessary"
            End If
            
            If Cand_EmpStatus.Value = "External" Then
            .Range("R" & idx).Value = "*****"
            .Range("S" & idx).Value = "*****"
            .Range("T" & idx).Value = "*****"
            .Range("U" & idx).Value = "*****"
            .Range("Q" & idx).Value = "Drug Screening"
            End If
            
            If Cand_EmpStatus.Value = "Agency" Then
            .Range("R" & idx).Value = " "
            .Range("S" & idx).Value = " "
            .Range("T" & idx).Value = " "
            .Range("U" & idx).Value = " "
            .Range("Q" & idx).Value = "Drug Screening"
            End If

Hmmm, then maybe it has to do with where I put it. Here's the code in it's entirety:

Private Sub AddUpdateCand_CommandButton_Click()
Dim idx As Long

Me.Hide

With Worksheets("JobIntvwData")

'    Requistion Tab

    Sheet4.Range("A2") = Me.ReqRcvd
    Sheet4.Range("B2") = Me.ReqNo
    Sheet4.Range("C2") = Me.Recruiter
    Sheet4.Range("D2") = Me.HM
    Sheet4.Range("E2") = Me.Job_Title
    Sheet4.Range("F2") = Me.Relo_Avail
    Sheet4.Range("G2") = Me.JobLocation
    Sheet4.Range("H2") = Me.JC_SG
    Sheet4.Range("I2") = Me.Business_Unit
    Sheet4.Range("J2") = Me.HM_Called
    
'    Intvw Info & Team Tab
    
    Sheet4.Range("K2") = Me.TeamBuilt
    Sheet4.Range("L2") = Me.Intvw_Type
    Sheet4.Range("M2") = Me.Conf_Rm
    Sheet4.Range("N2") = Me.Help_Rm
    Sheet4.Range("O2") = Me.Intvw_Loc
    Sheet4.Range("P2") = Me.Time_Zone
    Sheet4.Range("Q2") = Me.Deadline
    'Sheet4.Range("J19") = Me.IntA
    'Sheet4.Range("J20") = Me.IntB
    'Sheet4.Range("J21") = Me.IntC
    'Sheet4.Range("J22") = Me.AltIntA
    'Sheet4.Range("J23") = Me.AltIntB
    'Sheet4.Range("J24") = Me.AltIntC
    'Sheet4.Range("J17") = Me.Host1
    'Sheet4.Range("J18") = Me.Host2


    idx = cboCandidate.ListIndex

    If idx = -1 Then
        idx = Worksheets("CandidateData").Range("A" & Rows.Count).End(xlUp).Row + 1
    Else
        idx = idx + 2
        End If

        With Worksheets("CandidateData")
            .Range("A" & idx).Value = cboCandidate.Value
            .Range("B" & idx).Value = Cand_ID.Value
            .Range("C" & idx).Value = Cand_EmpStatus.Value
            .Range("D" & idx).Value = Cand_Step.Value
            .Range("E" & idx).Value = Cand_Nep.Value
            .Range("F" & idx).Value = Cand_CityState.Value
            '.Range("G" & idx).Value = YesRelo_OptionButton.Value   'IF Then Statement - How to do this
            '.Range("G" & idx).Value = NoRelo_OptionButton.Value    'IF Then Statement - How to do this
            .Range("I" & idx).Value = Cand_PhoneNo.Value
            .Range("J" & idx).Value = Cand_Email.Value
            .Range("K" & idx).Value = Cand_Resume.Value
            .Range("L" & idx).Value = Cand_Taleo.Value
            .Range("M" & idx).Value = Cand_StatusPS.Value
            .Range("N" & idx).Value = Cand_EligFT.Value
            .Range("O" & idx).Value = Cand_JC.Value
            .Range("P" & idx).Value = Cand_Edpm.Value
            '.Range("Q" & idx).Value =      .Value      'IF Then Statement - How to do this
            .Range("R" & idx).Value = Cand_Ext.Value
            .Range("S" & idx).Value = Cand_Loc.Value
            .Range("T" & idx).Value = Cand_Shift.Value
            .Range("U" & idx).Value = Cand_PS_ID.Value
            .Range("V" & idx).Value = Cand_Notes.Value
            '.Range("W" & idx).Value = Cand_UseSameItvwTeam.Value
            .Range("U" & idx).Value = Cand_IntA.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("V" & idx).Value = Cand_IntB.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("W" & idx).Value = Cand_IntC.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("X" & idx).Value = Cand_Alt1.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("Y" & idx).Value = Cand_Alt2.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("Z" & idx).Value = Cand_Alt3.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("AA" & idx).Value = Cand_Host1.Value 'Only if answer to UseSameItvwTeam is FALSE
            .Range("AB" & idx).Value = Cand_Host2.Value 'Only if answer to UseSameItvwTeam is FALSE
            
            'If Then Statements
            If YesRelo_OptionButton.Value = True Then
            .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
            .Range("AF" & idx).Value = " "
            .Range("AG" & idx).Value = " "
            Else
            .Range("H" & idx).Value = "No Travel Necessary"
            .Range("AF" & idx).Value = "*****"
            .Range("AG" & idx).Value = "*****"
            End If
            
            If NoRelo_OptionButton.Value = True Then
            .Range("H" & idx).Value = "No Travel Necessary"
            .Range("AF" & idx).Value = "*****"
            .Range("AG" & idx).Value = "*****"
            Else
            .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
            .Range("AF" & idx).Value = " "
            .Range("AG" & idx).Value = " "
            End If
            
            If Cand_EmpStatus.Value = "Employee" Then
            .Range("L" & idx).Value = "*****"
            .Range("M" & idx).Value = "*****"
            .Range("P" & idx).Value = "*****"
            .Range("Q" & idx).Value = "No Drug Necessary"
            End If
            
            If Cand_EmpStatus.Value = "External" Then
            .Range("R" & idx).Value = "*****"
            .Range("S" & idx).Value = "*****"
            .Range("T" & idx).Value = "*****"
            .Range("U" & idx).Value = "*****"
            .Range("Q" & idx).Value = "Drug Screening"
            End If
            
            If Cand_EmpStatus.Value = "Agency" Then
            .Range("R" & idx).Value = " "
            .Range("S" & idx).Value = " "
            .Range("T" & idx).Value = " "
            .Range("U" & idx).Value = " "
            .Range("Q" & idx).Value = "Drug Screening"
            End If
                        
        End With
        
End Sub

I counted the With's and that worked. And, my If Then Statements are working. Yay!

The reason I added the code to add the job data when the candidate's are updated was because I didn't see another way to have that information added to JobIntvwData without having yet another button for the user to press. So, I figured when a user enters the job data and then a candidate it would update. Though, I admit that's not the best way. I probably should just create a seperate button on Tab 1 and 2 for that information to update. But, I won't want the UserForm to close. Anyway to code it so the Userform doesn't close upon clicking the button?

No, I will NOT be using this for more than one job at a time.

How is the other parts coming along? I know my concern was when the user adds a candidate with a similar name to a previously entered candidate, it automatically populates the rest of Tab 2 with that other candidate's data without reverting to a clear form when the user continues to add a name different from that similar name. So, if there is a Joseph Taylor already in the list and I type Joseph Smith, the moment I start typing Joseph, the data for Joseph Taylor comes up and it doesn't go away as I continue to type the last name of Smith.

Also, when a user is entering a new candidate to the list, they will want a completley cleared form always. I think there should be a button for 'clear' so the user can do this at any time they need to. Don't you?

Thank you for your help!! Almost finished

Oh, and we were still working on the INterview Team thing. Where we want the candidate to be able to make changes as needed on the Candidate page, but the interview team on Tab 2 be the primary unless those changes are made.

Well, I have been testing the userform and such and making necessary corrections. Now, just suddently, after having opened the UserForm by clicking the button on the excel worksheet before with no problems, it wants to give me an error. For some reason it doesn't like the following:

Private Sub OpenForm_Click()
    IntvwWorksheet.Show
End Sub

The funny thing is I was going to ask about this later as the form doesn't even open upon opening the excel worksheet anyway. But, it wasn't a priority. Now it has become one since it won't allow me to open my form without the following error: Runt-Timer Error '381': Could not set the List property. Invalid property array index.

What happened?

I'm fairly certain I didn't do anything while making my updates, but maybe I did... but, I don't think so.

Ok. I choose All Errors and it took me to the following code - pointing to the cboCandidate.list..... row.

Private Sub UserForm_Initialize()
    With Worksheets("CandidateData")
        cboCandidate.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    End With
End Sub

I hate to keep bothering you. We are just so close to being finished. I can feel it and I'm so excited to get this working. In addition to this new problem, have you had a chance to look at the attachment previously provided to determine the problem with entering names that are similar, how to prevent the form from closing when the user clicks 'Add/Update', and how to set up the interview team correctly?

When I create the button to enter the Job Information and Interview and Team Informatoin onto the spreadsheet where it will be stored, should I create a button for both tab 1 and tab 2, or just on one? I'm not sure the best way to handle.