change of data
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
change of data
i have a formula in column L(it consist formula and the result can be nothing or true or false)
example
column L
true
false
false
now what i want is if the data in column L has true then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
EXAMPLE
Raj true(notification)
or if the data in column L has false then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
rani false(notification)
i want notification of the same
the vba should run continously and notifying me the same until i close the file
if i got notification of one student and meanwhile another student met the condition then i want the notification of the same
example
column L
true
false
false
now what i want is if the data in column L has true then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
EXAMPLE
Raj true(notification)
or if the data in column L has false then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
rani false(notification)
i want notification of the same
the vba should run continously and notifying me the same until i close the file
if i got notification of one student and meanwhile another student met the condition then i want the notification of the same
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
it depend on column C and F
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
but we have to concentrate on column L
in my file there are lots of formula so plz concentrate on column L and our target is column L
in my file there are lots of formula so plz concentrate on column L and our target is column L
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
and plz let me know that if we know our target is column L then y we require column L depend on which cell
Assume Column L depends on xyz cell but we have to concentrate on column L only
Assume Column L depends on xyz cell but we have to concentrate on column L only
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
and one more thing if possible then i want the notification along with student name and column L data and with current pc time
example
RAJ TRUE 10:30pm or am depend on pc time
example
RAJ TRUE 10:30pm or am depend on pc time
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: change of data
Excel only tells us which cells are changed by the user. It doesn't tell us which cells that contain a formula are changed. So there is no way for us to react to a change in column L, we can only react to a change in the cells that the formula in column L depends on.
In other words, what you ask is impossible (again).
In other words, what you ask is impossible (again).
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
fine no problem
I can do one more thing with this problem
My column L contains the formula and the result we got in column L will be blank or TRUE or False
First My column L will be blank the formula will return to the blank result
and column L depends on B or C or xyz when B C or xyz met the condition then column L will return to TRUE OR FALSE
So now what i want that when columb L turns from Blank result to true or false then notify me
here blank means no data in L columns(the formula result is nothing)
I can do one more thing with this problem
My column L contains the formula and the result we got in column L will be blank or TRUE or False
First My column L will be blank the formula will return to the blank result
and column L depends on B or C or xyz when B C or xyz met the condition then column L will return to TRUE OR FALSE
So now what i want that when columb L turns from Blank result to true or false then notify me
here blank means no data in L columns(the formula result is nothing)
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: change of data
Here is code for the worksheet module. You will notice that it has a strange side effect: when you change a cell in column B or C causing column L to change from blank to not-blank and press Enter or Tab, the active cell won't change.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurVal As Variant
Dim OldVal As Variant
Dim NewVal As Variant
Dim r As Long
If Target.Count > 1 Then Exit Sub
' The formula in column L depends on column B and C in the same row
If Not Intersect(Range("B:B,C:C"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
CurVal = Target.Value
r = Target.Row
NewVal = Range("L" & r).Value
Application.Undo
OldVal = Range("L" & r).Value
Target.Value = CurVal
If OldVal = "" And NewVal <> "" Then
MsgBox "Column L changed to TRUE/FALSE in row " & r, vbInformation
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
i will not change column B and C
Column B and C consist a formula
so the result of the formula will change automatically bcoz column B and column C depend on another column
So i think this code will work
what u think hansv Sir now this code will work according to me or still there will be strange side effect plz guide sir
Column B and C consist a formula
so the result of the formula will change automatically bcoz column B and column C depend on another column
So i think this code will work
what u think hansv Sir now this code will work according to me or still there will be strange side effect plz guide sir
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: change of data
You have to change Range("B:B,C:C") to the range of cells that YOU (the user) will edit/modify/change and that will directly or indirectly determine the result of the formulas in column L.
It won't work with Range("B:B,C:C") if columns B and C contain formulas.
It won't work with Range("B:B,C:C") if columns B and C contain formulas.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
Plz have a look in the sample file i have mentioned all details in it
if any doubts plz let me know
if any doubts plz let me know
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: change of data
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
This code is perfect, this is what i wanted
but i have an issue with this vba code
if it happens one by one then this code is working but if it happens with many it is not notifying the same
if column K is changing with more than one cells then this vba code is not working
if column k is changing with only one cells then this vba code is working so plz have a look and do needful Sir
but i have an issue with this vba code
if it happens one by one then this code is working but if it happens with many it is not notifying the same
if column K is changing with more than one cells then this vba code is not working
if column k is changing with only one cells then this vba code is working so plz have a look and do needful Sir
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: change of data
Here you go:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 84
- Joined: 23 Apr 2019, 19:00
Re: change of data
This code is perfect, this is what i wanted
but i have a doubt
it shows the notification perfect but meanwhile while showing the notification if the data of column K changes (another cells new one )then that notification it will show how
example
K5 AND K6 data changed it is showing notification(perfect) but meanwhile after 1 second K7 data changes then notification for K7 will be after pressing ok of the k5 and k6 notification am i right?
but i have a doubt
it shows the notification perfect but meanwhile while showing the notification if the data of column K changes (another cells new one )then that notification it will show how
example
K5 AND K6 data changed it is showing notification(perfect) but meanwhile after 1 second K7 data changes then notification for K7 will be after pressing ok of the k5 and k6 notification am i right?
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands