Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 April 3rd, 2015, 04:36 PM
Registered User
 
Join Date: Jan 2015
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Default Using Worksheet_Change Event in Excel

I want to manipulate the value of a cell following a Worksheet_Change event in a private subroutine. I call another subroutine to do the actual change but every time I change the value of a cell, VB kicks me out of my subroutine back to the calling private subroutine. I am not done with processing in my subroutine. I've tried several ways to access the cell without getting kicked back. What can I do to continue processing after changing the value?
(I left my various tries commented out below)

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then
Exit Sub
End If

'set target cell to J9000 on All Prices tab, for value change
If Target.Address = "All Prices!$J$9000" Then
'check to see if cell is numeric
If IsNumeric(Target) Then
Call OPIS_Change
End If
End If
End Sub

Sub OPIS_Change()

If Cells(5, "AA").Value = 2 Then
Exit Sub
Else
If Cells(5, "AA").Value <> 1 Then
Exit Sub
End If
End If

'move value of AA4 to AA6
'Cells(6, AA) = Cells(4, AA)
Range("AA6").Value = Range("AA4").Value
'Cells(6, "AA").Value = Cells(4, "AA").Value
'CODE ALWAYS JUMPS BACK TO VERY TOP AFTER MOVE
'*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

'Mail a copy of the ActiveWorkbook with another file name
Dim wb1 As Workbook
Dim TempFilePath As String
... (etc)
 
Old April 9th, 2015, 03:59 PM
Registered User
 
Join Date: Jan 2015
Posts: 7
Thanks: 1
Thanked 0 Times in 0 Posts
Default Fix when using Worksheet_Change Event

figured out my own problem.
You have to turn off the event so when a change is made to a cell, it doesn't throw you back into the Worksheet_Change event.

'turn off all Events so we don't put code in loop when we change it
Application.EnableEvents = False
'move value of AA4 to AA6
Range("AA6").Value = Range("AA4").Value
'turn Events back on
Application.EnableEvents = True





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 WorkSheet_Change Armand Excel VBA 2 October 26th, 2010 07:14 PM
Please help!!!!!Private Sub Worksheet_Change VicM Excel VBA 1 March 6th, 2008 10:35 PM
Worksheet_Change executing selectively lfrench Excel VBA 3 April 3rd, 2007 03:59 PM
Excel Worksheet_Change option mtowle Excel VBA 1 October 21st, 2005 09:27 AM
How to step into Worksheet_Change Piblon Excel VBA 2 December 17th, 2003 04:11 AM





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