Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Beginning VB 6
|
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 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 October 24th, 2005, 04:31 AM
Registered User
 
Join Date: Oct 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default stop macro from running on EVERY cell change!!

hello everyone

i am running the following macro,

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("FE11") = 1 Then
MsgBox "VEHICLE MAY BE DUE FOR A SERVICE !!", vbBEEP + vbOKOnly
End If

End Sub

the value in cell "FE11" is derived from a formula, which either returns a value of "1" or "0". if the value of cell "FE11"="1" i would like the macro to display a message and alarm sound at the same time alerting the user to take a different course of action.


i am a newbie to programming and can't figure out how to stop it from running everytime any cell value is changed in the worksheet other than "FE11" !

any help would be appreciated !!!

thanks in advance

forkliftpete

 
Old October 26th, 2005, 07:36 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

Hi

You cannot stop the Subroutine to excecute but what you could do is to check which cell the change happened in a specific column and then run the rest of the macro. What you probably also would want to do is cater for the values changing when more than one changes also, like when a user pastes or delete a range of data.

You could do something like this:
Public Sub Worksheet_Change(ByVal Target As Range)
Dim row As Range
   r = Target.row
For Each row In Target.Rows 'If more than one cell was pasted/deleted step through
    c = Target.Column
    If c = 4 Then 'The culumn you would want to check. Macro will exit here if the change was not in column "4"
    '****You code here to check the value of the formula in this specific row
End If
    r = r + 1 'Increment the row value
Next
End Sub

Hope this helps

Regards
Marnus





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP Apps keep running after stop Ron Howerton Visual Studio 2005 1 April 11th, 2008 12:15 AM
Cell computation using vba/Macro zone Excel VBA 6 August 2nd, 2007 10:38 AM
Probs while running macro anukagni Excel VBA 2 February 2nd, 2006 03:29 AM
Cell returns its own formula after a macro! timoma Excel VBA 2 December 19th, 2004 03:48 PM
DTS Running Until Non Stop Jane SQL Server DTS 2 February 29th, 2004 05:21 AM





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