Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 27th, 2004, 02:12 PM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Change Event Help

I need to have a worksheet change event identify if a particular cell was changed and then reset the value in another cell if the changed cell ="yes". i.e. if d12 is changed, and its value is "yes", change d11 to zero. Can anyone help?

Bryan A. Brassell
 
Old July 27th, 2004, 04:15 PM
Authorized User
 
Join Date: Jul 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,


try this :

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("yoursheetname").Range("d12") )
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("yoursheetname")
If Target.Value <> "" Then
If Target.Value = "yes" Then .Range("d11").Value = 0
End If
End With
End Sub

Succes

 
Old July 27th, 2004, 04:24 PM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Tried:

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "YES" Then .Range("d11").Value = 0
End If
End With
End Sub

and no luck. Did not get any error messages either. I do not understand what the first routine is doing?

Bryan A. Brassell
 
Old July 27th, 2004, 04:39 PM
Authorized User
 
Join Date: Jul 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bryan,

when you put/change something in a worksheet then
normally excell recalculates all the sheets in the workbook.
So this happens automatically.

That is a good trigger/event then to do your own routines.

(In the quick response a forgot a line of code)
Don't forget to put the code in your sheet "Input".

So again:

Private Sub Worksheet_Calculate()
'this routine is called every time there changes something on one of the sheets
Worksheet_Change (ThisWorkbook.Sheets("Input").Range("d12"))
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'then we look for a particular change at cell d12
With ThisWorkbook.Sheets("Input")
If Target.Value <> "" Then
If Target.Value = "yes" Then .Range("d11").Value = 0
End If
End With
End Sub

Succes again.

 
Old July 29th, 2004, 03:42 PM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is getting closer - it now changes,however, it changes d11 to zero regardless of where the value YES is put (not just if it is put in cell d12. How do I get it to only change when cell d12 is "YES"?

Thanks for the help...

Bryan A. Brassell





Similar Threads
Thread Thread Starter Forum Replies Last Post
Change of event using 2 combobox yogeshyl Excel VBA 0 July 31st, 2007 05:02 AM
Event on record change needelp Access VBA 5 October 18th, 2006 07:13 PM
Datagrid row change event? djam C# 0 March 9th, 2005 04:22 AM
Change Event BryanBrassell Excel VBA 0 December 18th, 2003 01:27 PM
Help with Worksheet Change Event BryanBrassell Excel VBA 1 June 24th, 2003 09:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.