how to run a subroutine automatically depending upon the value in a cell

[sumitbansal]

i am in a process of making a school project and i am stuck in a situation where I am suppose to run a subroutine depending upon the value in the cell of a spreadsheet. For more details...


Say i have an empty cell then no subroutine should run

if the cell has value say "teacher" then the subroutine for calculating the salary of teacher should run

if the cell has value say "Student", then the subroutine for calculating his/her school fees should run


and so on...


The link for the sample file is : http://www.mediafire.com/?w1ue2cydgn9z3tk


Please help, i am in dire need for resolving this issue in a very short time...


Thanks in advance...

 

[Hui]

sumitbansal


Firstly, Welcome to the Chandoo.org Forums


Try the following,

It must be copied to the code module of the Worksheet you want it to apply to in the VBE

Change D3 to suit

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3")) Is Nothing Then Exit Sub
If UCase(Range("D3").Value) = "TEACHER" Then
MsgBox "Put my Teacher code here"
ElseIf UCase(Range("D3").Value) = "STUDENT" Then
MsgBox "Put my Student code here"
End If
End Sub

[/pre]

 

[Hui]

Sumitbansal


Firstly, Welcome to the Chandoo.org Forums


I'm Confused as your current routine "Private Sub Worksheet_Change(ByVal Target As Range)"

works fine ?

 

[sumitbansal]

Hi,

The current subroutine works fine only when the data is entered by the user manually. However, if the data is being populated as a result of vlookup formula, then this subroutine considers the contents of the cell as a formula and not as the result of the formula and hence does not run as it is supposed to.


I hope, I am able to present the correct picture.


Thanks,

Sumit.

 

[Hui]

Sumit

Try the following code instead

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B3:B10")) Is Nothing Then
Application.EnableEvents = False
Select Case UCase(Target.Offset(, 3).Value)
Case Is = "TEACHER"
Call teachers_Salary
Case Is = "STUDENT"
Call Tuition_Fees
End Select

Application.EnableEvents = True
End If
End Sub

[/pre]

Note the change in target Range and use of Offset to retrieve the Classification

 

[SirJB7]

Hi, sumitbansal!


Sorry if I misunderstood you but the Worksheet_Change events is triggered each time you changes the content of a cell, either a constant value or a formula. And this is independent of what does that cell displays.


How's this? Suppose you have cell A1 that has the value 10, then you edit the cell and changes the value from 10 to any other the event is triggered for that cell. But if you have in A1 the formula =B1*2 and you have 25 in B1 then A1 shows 50: if you now change B1 to 20, A2 will show 40... but no event will be triggered, since you didn't change A1 cell contents (the formula =B1*2). In this case you should alter the formula =B1*2 to -for example- =B1*3 in order to be triggered the event for cell A1.


Briefly, Worksheet_Change events fires when value or formula stored in a cell changes, not when value displayed changes as consequence or another cell change.


Hope it aided you.


Regards!

 

[sumitbansal]

Hui,

You are great. Just Awesome.

The code that you provided solved my quest and now I am able to complete the project. Thanks a lot for that. You are a not just excel ninja but a ninja master.


Sumit. :D

 

[sumitbansal]

Hi SirJB7,

You said that:

"Briefly, Worksheet_Change events fires when value or formula stored in a cell changes, not when value displayed changes as consequence or another cell change."


This is exactly what was my quest to solve and Hui helped me solve it. The selection of range was to be done only on the value that the user changes and in my case it was the column B and then look for the value in another specific column - Column E. This was what I was lacking. But, thanks to Hui, now I am on the right track with my destination well in sight and reach.


Thanks,

Sumit

 

[SirJB7]

Hi, sumitbansal!

Thanks for your kind comments. Just one thing: if you change your Sample_Database sheet, let's say for S.No. 22 change Student for Teacher, the event would not be triggered. If you don't make such updates everything will be fine, if you do... actual code will be reflecting calculation for previous status.

Regards!

 

[sumitbansal]

Hi SirJB7,

I tried changing the values in the sample_database and the change was reflecting in the sample_report sheet but yes, the subroutine did not work again. But, my report is to be generated anew everyday. So, if there are any changes in the database, the subroutine will correct it when the report would be generated again next time. The previous report gets discarded once the day is over. So, this does not concern me much.


Thanks for the insight though. it is very much appreciated.


Sumit.

 

[SirJB7]

Hi, sumitbansal!

Glad to see you now got the widest scope about Worksheet_Change event. Now you can fully exploit it as you need.

Welcome back anytime.

Regards!