Good afternoon all. I hope you are doing well. This is my first post here on Mrexcel so please excuse any lapses in formality that I may cause. Thank you all for the valuable information that is provided to this VBA newbie, it has been a great help.
I have been working on a complicated userform for my HR department that will complete many different functions based on dropdown choices that they make.
On the userform, I have a dropdown called "cboPrevTitle" that loads a list of positions from another excel spreadsheet when the userform initializes. This works very well.
Based on the position title that I choose, a dropdown called cboPrevStep is populated from a different spreadsheet which provides me with the pay steps that are available to that title. Here is the code that completes that function:
The above code does exactly what I want in that it loads the cboPrevStep dropdown with the available codes for the chosen title. The SalaryClass is the class that is used to point to the correct list of salary scales and give me the right steps for that job title. I have about 12 different Salary Classes but only gave you one example because I didn't want to make this post too long.
Now once I have chosen a title, and the steps are loaded, I now need to choose a step. Once I pick the step it pulls the information with no problem until I decide to choose another title from the cboPrevTitle list and then it give me a runtime error 91 and errors out on the "Set Annual Salary =" line of code.
This is supposed to combine a column address with the row address so that I can pull the data from that cell but it will not work after the first time through.
I have figured out that my problem lies in that when the code runs the second time through, the stepvalue variable is empty but I cannot figure out why. It should contain the step that I have chosen in the dropdown box.
Any help at would be greatly appreciated. If you have any questions or are confused by my code, please let me know and I will be glad to try to clarify.
I have been working on a complicated userform for my HR department that will complete many different functions based on dropdown choices that they make.
On the userform, I have a dropdown called "cboPrevTitle" that loads a list of positions from another excel spreadsheet when the userform initializes. This works very well.
Based on the position title that I choose, a dropdown called cboPrevStep is populated from a different spreadsheet which provides me with the pay steps that are available to that title. Here is the code that completes that function:
Code:
Private Sub cboPrevTitle_Change()
Dim ChosenTitle As String
Dim FindRowNumber As Long
Dim SalaryClass
Dim TitleComments As String
ChosenTitle = cboPrevTitle.Value
Set SourceWB = Workbooks.Open("S:\Hr Share\Par Main\ParResource.xlsx", _
False, True)
'
With SourceWB.Sheets(1)
Set FindRow = .Range("A2:A175").Find(What:=ChosenTitle, LookIn:=xlValues)
FindRowNumber = FindRow.Row
SalaryClass = .Range("P" & FindRowNumber)
TitleComments = .Range("Q" & FindRowNumber)
lblTitleComments.Caption = TitleComments
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
End With
'====================================================================================================
'Populate cboPrevStep
If SalaryClass = "Admin-11 Month" Then
With Me.cboPrevStep
.Clear ' remove existing entries from the listbox
' turn screen updating off,
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("S:\Hr Share\Par Main\SalaryScales.xlsx", _
False, True)
ListItems = SourceWB.Worksheets(4).Range("A3:A23").Value
' get the values you want
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End With
ElseIf cboPrevTitle.ListIndex = -1 Then
With Me.cboPrevStep
.Clear
End With
End If
End SubThe above code does exactly what I want in that it loads the cboPrevStep dropdown with the available codes for the chosen title. The SalaryClass is the class that is used to point to the correct list of salary scales and give me the right steps for that job title. I have about 12 different Salary Classes but only gave you one example because I didn't want to make this post too long.
Now once I have chosen a title, and the steps are loaded, I now need to choose a step. Once I pick the step it pulls the information with no problem until I decide to choose another title from the cboPrevTitle list and then it give me a runtime error 91 and errors out on the "Set Annual Salary =" line of code.
This is supposed to combine a column address with the row address so that I can pull the data from that cell but it will not work after the first time through.
I have figured out that my problem lies in that when the code runs the second time through, the stepvalue variable is empty but I cannot figure out why. It should contain the step that I have chosen in the dropdown box.
Any help at would be greatly appreciated. If you have any questions or are confused by my code, please let me know and I will be glad to try to clarify.
Code:
Private Sub cboPrevStep_Change()
Dim ChosenTitle As String
Dim Grade As String
ChosenTitle = cboPrevTitle.Value
Dim FindColumn As Long
Dim FindColumnLetter
Dim GradeColumnLetter
Dim TitleRowNumber
Dim SalaryClass
Dim AnnualSalary
Dim TotalSalary
Dim HoursPerYear
Dim ContractDays
Dim HoursPerDay
Dim PPBAChoice
lblPrevAuxPayAmt1.Caption = 0
lblPrevAuxPayAmt2.Caption = 0
txtPrevAuxPayAmt1.Value = 0
txtPrevAuxPayAmt2.Value = 0
'-----------------------------------------------------------------------------------------
'Find Salary Class and Grade for the chosen position
Set SourceWB = Workbooks.Open("S:\Hr Share\Par Main\ParResource.xlsx", _
False, True)
With SourceWB.Sheets(1)
Set FindRow = .Range("A2:A175").Find(What:=ChosenTitle, LookIn:=xlValues)
TitleRowNumber = FindRow.Row
SalaryClass = .Range("P" & TitleRowNumber)
Grade = .Range("O" & TitleRowNumber)
ContractDays = .Range("L" & TitleRowNumber)
HoursPerDay = .Range("M" & TitleRowNumber)
HoursPerYear = ContractDays * HoursPerDay
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
End With
'-------------------------------------------------------------------------------------------
If SalaryClass = "Admin-11 Month" Then
' turn screen updating off,
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("S:\Hr Share\Par Main\SalaryScales.xlsx", _
False, True)
With SourceWB.Sheets(4)
Set FindColumnLetter = .Range("B2:R2").Find(What:=Grade, LookIn:=xlValues)
StepValue = cboPrevStep.Value
MsgBox StepValue
Set Stepvaluerow = .Range("A3:A23").Find(What:=StepValue, LookIn:=xlValues)
FindColumn = FindColumnLetter.Column
GradeColumnLetter = Col_Letter(FindColumn)
Set AnnualSalary = .Range(GradeColumnLetter & Stepvaluerow.Row) 'Runtime error 91 starts here.
lblAnnualSalary.Caption = "$" & AnnualSalary
End With
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
End If
