That was a good point. I changed to arithmetic. I was someway used to Datedif. THANK YOU DAVE!
A question on VBA UserForms (I am posting it in here because it's related to the same workbook).
I have a UserForm with all sorts of TextBoxes, ComboBoxes, CommandButtons in it.
The question now is:
I have a ComboBox for the Profit&Loss (PL) account - cboPL
I have a ComboBox for the Department (~Profit Center) - cboDept
I have a ComboBox for the Product Code - cboPCode
I would like these ComboBoxes to interact with each other.
I am pulling the data from the same workbook, different sheet("AccountRules").
In column A in this Sheet "Account Rules" there all the P&L accounts, in column B the Departments and so on.
The issue now is, that some P&L account allow only 1 Department or 1 Product Code, whereas other P&L accounts allow many (up to 100)
So in column A i have MANY DUPLICATES.
Example:
A2: Operating Leasing ; B2: Dept 10 ; C2: PCode 10
A3: Operating Leasing ; B3: Dept 20 ; C3: PCode 10
A3: Operating Leasing ; B3: Dept 30 ; C3: PCode 10
What I would need is the ComboBoxes to show the following:
P&L account -> ingore Duplicates
Only allow Departments, as per the list ("AccountRules") and based on the Selected P&L account.
Only allow PCode 10, as per the list ("AccountRules") and based on the Selected P&L account.
So if the user adds an entry in the UserForm, she/he selects cboPL and would then only be able to select certain Departments and certain Product Codes based on the look up Sheet.
I tried with this code here: (This one is able to ignore Duplicates, but nothing else really)
Private Sub UserForm_Initialize()
Dim LastRow As Integer
Dim i As Integer
Dim check As Boolean
Dim k As Integer
LastRow = ThisWorkbook.Sheets("AccountRules").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
check = True
For k = 0 To Me.cboPL.ListCount - 1
If ThisWorkbook.Sheets("AccountRules").Cells(i, 1).Text = Me.cboPL.List(k) Then
check = False
Exit For
End If
Next k
If check Then
Me.cboPL.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 1).Text
End If
Next i
End sub
and I also tried this one: (This is supposed to add items based on the P&L account selection (cboPL)
Private Sub cboPL_Change()
Call checkForData
cboPL.Style = fmStyleDropDownList 'Only values from Dropdown list allowed
Dim myPLAccount As String
myPLAccount = Me.cboPL.Text
LastRow = ThisWorkbook.Sheets("AccountRules").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If myPLAccount = ThisWorkbook.Sheets("AccountRules").Cells(i, 1) Then
'add to cboDept, cboPCode
Me.cboDept.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 2)
Me.cboPCode.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 3)
End If
Next i
End Sub
I might need to combine these two together.
But as i'm pretty new to VBA I have no clue where the problem is.
I got the above codes together from Internet and my own input.
Note: I left the RowSource in Properties empty
Hope this makes sense.
Thanks
BR,
Roger
