VB Change by val not working as expected
I want to take some action if any of three changes occur in an excel spreadsheet. I have written some vb code, examples attached. If the first condition which is a change to staff number is made, the code works fine. If the 3rd condition where the absence type changes, it also works fine. No matter what I do with the 2nd condition it does not work. I have tried recoding with what elseif's, I thought maybe because the column is a calculated field it didn't like it, so tried it instead on one of the date columns. I have even moved the conditions around. But no matter what I do only the absence type or the staff no changes will behave as I expect. I have attached the workbook and the troublesome code. In the workbook what should happen is that when a new absence is entered on the absence sheet, then the number of sick days for that employee should be recalculated on the PR sheet. If the row has been entered a with an incorrect date or an incorrect absence type the change should prompt a recount of the total number of sick days for the employee.
sample-for-EE.xlsm
code-snippet.JPG
sample-for-EE.xlsm
code-snippet.JPG
Last Comment
ASKER
It is obviously happier with the recalc but my syntax is not working. Getting error that target is not defined
Private Sub Worksheet_Calculate()
If Not Intersect(Target, rSDurationTypeColumn) Is Nothing Then 'duration changes
iStaffNo = ActiveCell.Offset(0, -6)
Debug.Print "sdate change"
Call RecalculateStaffAbsence(iS
End If
End Sub
Private Sub Worksheet_Calculate()
If Not Intersect(Target, rSDurationTypeColumn) Is Nothing Then 'duration changes
iStaffNo = ActiveCell.Offset(0, -6)
Debug.Print "sdate change"
Call RecalculateStaffAbsence(iS
End If
End Sub
Duration columns contains a formula and any change in the cell content caused by a formula will not trigger the change event code.
Since the formula in Duration column is dependent on the Start Date and End Date columns i.e. columns D and E respectively which in turn can be changed either by a date picker or a manual date input, you can add a condition in the change event code to check if any change in the column D and E is made or a date in one of these columns is changed with the help of date picker.
To tweak the change event code, replace the second condition in the change event code
First declare the following variable in the declaration section
and then replace the second condition with this one
Also place a separate code on the same module for the date picker change event.
Since the formula in Duration column is dependent on the Start Date and End Date columns i.e. columns D and E respectively which in turn can be changed either by a date picker or a manual date input, you can add a condition in the change event code to check if any change in the column D and E is made or a date in one of these columns is changed with the help of date picker.
To tweak the change event code, replace the second condition in the change event code
If Not Intersect(target, rSDurationTypeColumn) Is Nothing Then 'duration changes
iStaffNo = ActiveCell.Offset(0, -6)
Debug.Print "sdate change"
Call RecalculateStaffAbsence(iStaffNo)
End If
First declare the following variable in the declaration section
Dim rngDates As Range
Set rngDates = Range("A1").CurrentRegion.Columns("D:E")
and then replace the second condition with this one
If Not Intersect(target, rngDates) Is Nothing Then
iStaffNo = Cells(target.Row, "A").Value
Debug.Print "sdate change"
Call RecalculateStaffAbsence(iStaffNo)
End If
Also place a separate code on the same module for the date picker change event.
Private Sub DTPicker1_Change()
Dim Cel As Range
Set Cel = Selection
iStaffNo = Cells(Cel.Row, "A").Value
Debug.Print "sdate change"
Call RecalculateStaffAbsence(iStaffNo)
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Seems to be working perfectly now with your changes thank you
ASKER
Hi Subodh
Just found a glitch, If i enter a new absence record at the end of the absence table it throws an error now .
I don't want to bother you any further so I will try and sort myself. Many Thanks
Just found a glitch, If i enter a new absence record at the end of the absence table it throws an error now .
I don't want to bother you any further so I will try and sort myself. Many Thanks
No problem! If you are not able to sort it out yourself, you may open another question with the issue you reported.
ASKER
Thanks, will do
VB Script
VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.
39K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER