How to monitor contents of a cell?

[qjd2004]

Hiya,

I'm trying to monitor the contents of a cell. If the cell gets a value of "Y" or 1 then I want to execute a Sub. I've got some code which I've added to the worksheet I want to monitor, but it's not doing anything. Can somebody tell me why and how to make it execute?

At Worksheet level:

Private Sub Worksheet_Calculate()
   Worksheet_Change Range("D5")
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'   Level 1:  Set up the event to watch a single cell.
    If Target.Address = "D5" Then
        
'   Level 2: Perform some action based on the value of the watched cell.
        
        Select Case Target.Value
            Case 1
                'Insert some code to do something here!
            Case 2
                'etc etc
        End Select

    End If

End Sub

Q

 

[Loomah]

Hi
Firstly you'll need to change the address to absolute as this is the default format for address

ie

If Target.Address = "$D$5" Then

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[qjd2004]

Hiya,

thanks for your reply. I've put in the absolute, but it doesn't solve the problem.

I don't get what's wrong with it.

Can you help?

 

[Loomah]

This is a re-work of what you posted originally. Ignore your calculate procedure and add the change event code to the Sheet1 module. Add the other 2 Subs to an ordinary module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'   Level 1:  Set up the event to watch a single cell.
    If Target.Address = "$D$5" Then
        
'   Level 2: Perform some action based on the value of the watched cell.
        Select Case Target
            Case 1
                'Insert some code to do something here!
                Call standard
            Case "y", "Y"
                Call mit
                'etc etc
            Case Else
                MsgBox "Neither 1 nor Y were entered"
        End Select
    End If
End Sub


Sub standard()
MsgBox "This sub is called by entering 1 into cell D5 on sheet1"
End Sub

Sub mit()
MsgBox "This sub is called by entering Y or y into cell D5 on sheet1"
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[qjd2004]

Thanks Loomah,

that works nicely. Now I just need to get the 'send email' subroutine it calls working! lol

Email thread707-797951

Thanks for your help.

Have a nice wkend!

Q

 

[KenWright]

Take a look here for all the example code to do with Sendmail you could possibly want:-

http://www.rondebruin.nl/

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------