eemiller1997
New Member
- Joined
- Nov 14, 2012
- Messages
- 14
I need some help understanding how this works. I created a Multi-Page UserForm and had everything just the way I wanted it, including If Then Statements and such. Then, after that, someone helped me do what I needed to do, which was to make it possible for users to add candidates. It’s wonderful how its set up and exactly what I need, I just don’t understand it; therefore, I’ve messed it up trying to customize and clean it up.
I’m not even sure how to research the problem as I can’t seem to find anything to explain what is happening here. Somehow the Combo Box is controlling the rest of that page for the UserForm whereas everything online demonstrates Comboboxes as an individual fields rather than one that is connected and controlling the rest of the page of the userform. So, correct terminology would also be a big help.
Can someone help me understand how this works so I can customize it? I still need to also move my If Then Statements to accommodate this new setup and I have no idea how to do it.
There are four tabs within the excel worksheet - CandidateForm, JobIntvwData, DandidateData, and Values.
The CandidateForm is the final form the user will print after the data is inputed through the UserForm. I believe JobIntvwData is where the content from Pages 2 and 3 of the UserForm will be stored. CandidateData is where the data from the Third page of the UserForm will be stored, per candidate. Values is just the values for the drop down boxes within the UserForm.
On the Third page of the MultiPage UserForm called ‘IntvwWorksheet’ there is a combo box. Currently, there are four candidates in the box (this version does not yet have the ability to actually add the candidates.)
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:
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:
This is also what loses 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 referring to?
Because I cannot seem to find how to attach the file, I will provide the code for both the two forms, 'frmPrintCand' and 'IntvwWorksheet'
First: UserForm
frmPrintCand
I’m not even sure how to research the problem as I can’t seem to find anything to explain what is happening here. Somehow the Combo Box is controlling the rest of that page for the UserForm whereas everything online demonstrates Comboboxes as an individual fields rather than one that is connected and controlling the rest of the page of the userform. So, correct terminology would also be a big help.
Can someone help me understand how this works so I can customize it? I still need to also move my If Then Statements to accommodate this new setup and I have no idea how to do it.
There are four tabs within the excel worksheet - CandidateForm, JobIntvwData, DandidateData, and Values.
The CandidateForm is the final form the user will print after the data is inputed through the UserForm. I believe JobIntvwData is where the content from Pages 2 and 3 of the UserForm will be stored. CandidateData is where the data from the Third page of the UserForm will be stored, per candidate. Values is just the values for the drop down boxes within the UserForm.
On the Third page of the MultiPage UserForm called ‘IntvwWorksheet’ there is a combo box. Currently, there are four candidates in the box (this version does not yet have the ability to actually add the candidates.)
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:
Code:
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:
Code:
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 also what loses 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 referring to?
Because I cannot seem to find how to attach the file, I will provide the code for both the two forms, 'frmPrintCand' and 'IntvwWorksheet'
First: UserForm
Code:
Private Sub UserForm_Initialize()
With Worksheets("CandidateData")
cboCandidate.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With
End Sub
Private Sub AddCand_CommandButton_Click()
Dim m As Object
Set m = MultiPage1.Pages.Add("Candidate2", "Candidate 2")
With Sheets("Candidate (1)").Select
Sheets("Candidate").Copy After:=Sheets(Sheets.Count)
Sheets("Values").Select
Sheets("Values").Move After:=Sheets(Sheets.Count)
End With
End Sub
Private Sub ButtonDone_Click()
Me.Hide
' Requistion Tab
'Sheet1.Range("N46") = Me.ReqRcvd
'Sheet1.Range("K7") = Me.ReqNo
'Sheet1.Range("K1") = Me.Recruiter
'Sheet1.Range("K6") = Me.HM
'Sheet1.Range("K10") = Me.Job_Title
'Sheet1.Range("O8") = Me.Relo_Avail
'Sheet1.Range("K9") = Me.JobLocation
'Sheet1.Range("O7") = Me.JC_SG
'Sheet1.Range("K8") = Me.Business_Unit
'Sheet1.Range("O6") = Me.HM_Called
' Intvw Info & Team Tab
'Sheet1.Range("N49") = Me.TeamBuilt
'Sheet1.Range("L12") = Me.Intvw_Type
'Sheet1.Range("L13") = Me.Conf_Rm
'Sheet1.Range("M13") = Me.Help_Rm
'Sheet1.Range("L14") = Me.Intvw_Loc
'Sheet1.Range("O9") = Me.Time_Zone
'Sheet1.Range("L15") = Me.Deadline
'Sheet1.Range("J19") = Me.IntA
'Sheet1.Range("J20") = Me.IntB
'Sheet1.Range("J21") = Me.IntC
'Sheet1.Range("J22") = Me.AltIntA
'Sheet1.Range("J23") = Me.AltIntB
'Sheet1.Range("J24") = Me.AltIntC
'Sheet1.Range("J17") = Me.Host1
'Sheet1.Range("J18") = Me.Host2
' Candidate1 Tab
'Sheet1.Range("C2") = Me.Cand1_FullName
'Sheet1.Range("F3") = Me.Cand1_ID
'Sheet1.Range("F4") = Me.Cand1_EmpStatus
'Sheet1.Range("N25") = Me.Cand1_EmpStatus
'Sheet1.Range("N27") = Me.Cand1_Step
'Sheet1.Range("N26") = Me.Cand1_Nep
'Sheet1.Range("C3") = Me.Cand1_CityState
'Sheet1.Range("F2") = Me.Cand1_PhoneNo
'Sheet1.Range("C4") = Me.Cand1_Email
'Sheet1.Range("F5") = Me.Cand1_Resume
'Sheet1.Range("N29") = Me.Cand1_Taleo
'Sheet1.Range("N28") = Me.Cand1_StatusPS
'Sheet1.Range("N31") = Me.Cand1_EligFT
'Sheet1.Range("N30") = Me.Cand1_JC
'Sheet1.Range("N39") = Me.Cand1_Edpm
'Sheet1.Range("J3") = Me.Cand1_Ext
'Sheet1.Range("N3") = Me.Cand1_Loc
'Sheet1.Range("J4") = Me.Cand1_Shift
'Sheet1.Range("N4") = Me.Cand1_PS_ID
'Sheet1.Range("B48") = Me.Cand1_Notes
'Sheet1.Range("J19") = Me.Cand1_IntA
'Sheet1.Range("J20") = Me.Cand1_IntB
'Sheet1.Range("J21") = Me.Cand1_IntC
'Sheet1.Range("J22") = Me.Cand1_Alt1
'Sheet1.Range("J23") = Me.Cand1_Alt2
'Sheet1.Range("J24") = Me.Cand1_Alt3
'Sheet1.Range("J17") = Me.Cand1_Host1
'Sheet1.Range("J18") = Me.Cand1_Host2
End Sub
Private Sub ButtonNext_Click()
Dim I As Long
I = MultiPage1.Value + 1
If I < MultiPage1.Pages.Count Then
MultiPage1.Value = I
End If
End Sub
Private Sub ButtonPrevious_Click()
Dim I As Long
I = MultiPage1.Value - 1
If I >= 0 Then
MultiPage1.Value = I
End If
End Sub
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
Private Sub Cand1_FullName_Change()
End Sub
Private Sub ButtonPrint_Click()
frmPrintCand.Show
End Sub
Private Sub Cand_Edpm_Change()
End Sub
Private Sub cboCandidate_Change()
Dim idx As Long
idx = cboCandidate.ListIndex
If idx <> -1 Then
idx = idx + 2
With Worksheets("CandidateData")
Cand_ID.Value = .Range("B" & idx).Value
Cand_EmpStatus.Value = .Range("C" & idx).Value
Cand_Step.Value = .Range("D" & idx).Value
Cand_Nep.Value = .Range("E" & idx).Value
Cand_CityState.Value = .Range("F" & idx).Value
YesRelo_OptionButton.Value = .Range("G" & idx).Value = "Yes"
'Travel Authorization answers ("H")
Cand_PhoneNo.Value = .Range("I" & idx).Value
Cand_Email.Value = .Range("J" & idx).Value
Cand_Resume.Value = .Range("K" & idx).Value
Cand_Taleo.Value = .Range("L" & idx).Value
Cand_StatusPS.Value = .Range("M" & idx).Value
Cand_EligFT.Value = .Range("N" & idx).Value
Cand_JC.Value = .Range("O" & idx).Value
Cand_Edpm.Value = .Range("P" & idx).Value
'Drug Screen Needed Answers ("Q")
Cand_Ext.Value = .Range("R" & idx).Value
Cand_Loc.Value = .Range("S" & idx).Value
Cand_Shift.Value = .Range("T" & idx).Value
Cand_PS_ID.Value = .Range("U" & idx).Value
Cand_Notes.Value = .Range("V" & idx).Value
Cand_IntA.Value = .Range("X" & idx).Value
Cand_IntB.Value = .Range("Y" & idx).Value
Cand_IntC.Value = .Range("Z" & idx).Value
Cand_Alt1.Value = .Range("AA" & idx).Value
Cand_Alt2.Value = .Range("AB" & idx).Value
Cand_Alt3.Value = .Range("AC" & idx).Value
Cand_Host1.Value = .Range("AD" & idx).Value
Cand_Host2.Value = .Range("AE" & idx).Value
End With
End If
End Sub
Private Sub JobLocation_Label2_Click()
End Sub
Private Sub MultiPage1_Change()
JobLocation_Label2.Caption = JobLocation.Value
Req_Label2.Caption = ReqNo.Value
BusinessUnit_Label2.Caption = Business_Unit.Value
JobCode_Label2.Caption = JC_SG.Value
End Sub
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
Private Sub NoSameTeam_OptionButton_Click()
Dim score As String
score = NoSameTeam_OptionButton.Value
If score = True Then
Cand1_IntA.Value = ""
Cand1_IntB.Value = ""
Cand1_IntC.Value = ""
Cand1_Alt1.Value = ""
Cand1_Alt2.Value = ""
Cand1_Alt3.Value = ""
Cand1_Host1.Value = ""
Cand1_Host2.Value = ""
End If
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub YesRelo_CheckBox_Click()
Dim score As String
score = YesRelo_CheckBox.Value
If score = True Then
Range("I37").Value = "Travel/Hotel Authorization Made"
Range("N52").Value = " "
Range("N37").Value = " "
End If
End Sub
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
Private Sub YesSameTeam_OptionButton_Click()
Dim score As String
score = YesSameTeam_OptionButton.Value
If score = True Then
Cand1_IntA.Value = IntA.Value
Cand1_IntB.Value = IntB.Value
Cand1_IntC.Value = IntC.Value
Cand1_Alt1.Value = AltIntA.Value
Cand1_Alt2.Value = AltIntB.Value
Cand1_Alt3.Value = AltIntC.Value
Cand1_Host1.Value = Host1.Value
Cand1_Host2.Value = Host2.Value
End If
End Sub
frmPrintCand
Code:
Option Explicit
Private Sub cmdAll_Click()
Dim wsCandidate As Worksheet
Dim I As Long
For I = 0 To lstCandidates.ListCount - 1
Worksheets("CandidateForm").Copy After:=Worksheets(Worksheets.Count)
Set wsCandidate = Worksheets(Worksheets.Count)
wsCandidate.OLEObjects(1).Delete
FillForm wsCandidate, Worksheets("CandidateData").Range("A" & I + 2)
wsCandidate.PrintOut
Next I
Unload Me
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSelected_Click()
Dim wsCandidate As Worksheet
Dim I As Long
For I = 0 To lstCandidates.ListCount - 1
If lstCandidates.Selected(I) = True Then
Worksheets("CandidateTemp").Copy After:=Worksheets(Worksheets.Count)
Set wsCandidate = Worksheets(Worksheets.Count)
wsCandidate.OLEObjects(1).Delete
FillForm wsCandidate, Worksheets("CandidateData").Range("A" & I + 2)
wsCandidate.PrintOut
End If
Next I
Unload Me
End Sub
Private Sub lstCandidates_Click()
End Sub
Private Sub UserForm_Initialize()
With Worksheets("CandidateData")
lstCandidates.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With
End Sub
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
' Candidate1 Tab
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
'
End Sub