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 October 26th, 2010, 01:01 PM
Registered User
 
Join Date: Oct 2010
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
Default check for change in Excel VBA

I have a worksheet that we use for status reporting for some 200 activities. I would like to insert the current date in one cell when the manager updates status on another cell in the same row.

I'm able to put in the date when the two cell references are hard-coded. However, I can't figure out how to use a single formula that will work as the reporters go row to row. I tried combining the column and ActiveCell.Row then using that variable in the "...Not Intersect(Target, Me.row)..." IF statement, but got an error.

Anyone have an answer for this? I could just be blowing syntax - I'm no VBA programmer.

One other thing - I've seen some examples using "row - 1" and such, but frankly, they could jump anywhere after they update the status.
 
Old October 26th, 2010, 01:51 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default the answer you're looking for?

it sounds like you're using the worksheet change event, which is an event that is raised anytime the worksheet is changed. Here's code that will enter the date one column to the left of the cell that has been changed, but only if the cell that has been changed is in column "B".
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
    Application.EnableEvents = False
    Target.Offset(0, -1) = Date
    Application.EnableEvents = True
End If
End Sub
you have to turn off events or else it gets caught in a loop and keeps putting the date one cell to the left

Last edited by mtranchi; October 26th, 2010 at 01:55 PM..
The Following User Says Thank You to mtranchi For This Useful Post:
Poco12 (October 26th, 2010)
 
Old October 26th, 2010, 05:29 PM
Registered User
 
Join Date: Oct 2010
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
Default thank you

Your post got me started on the path to what I needed. Much simpler, too than some other things I was reading. What I ended up coding was -
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCol2
    MyCol2 = Left(ActiveCell.Address, 2)
    Application.EnableEvents = False
    If MyCol2 = "$M" Then
        Target.Offset(0, 10) = Date
        Target.Offset(0, 11) = Time
    End If
    Application.EnableEvents = True
End Sub
Works every time!
 
Old October 26th, 2010, 06:33 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default Glad i could help

it probably doesn't matter because it's such a small block of code, but for future reference there's no need to declare the MyCol2 variable as you can use Target directly. Also for future reference, when you declare variables don't forget to declare the type. If you end up making a big application with lots of code that will speed things up.

So if you did need to declare MyCol2 you would do it like so:

Code:
Dim MyCol2 as string
Anyway, here's the fastest most efficient way to do what you're doing:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then 'it's the 13th column, in other words, "M"
    Application.EnableEvents = False
    Target.Offset(0, 10) = Date
    Target.Offset(0, 11) = Time
    Application.EnableEvents = True
End If
End Sub
Take care
The Following User Says Thank You to mtranchi For This Useful Post:
Poco12 (October 26th, 2010)
 
Old October 26th, 2010, 07:37 PM
Registered User
 
Join Date: Oct 2010
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
Default

even simpler. thank you.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Check When Cell Has Changed tonyrosen Excel VBA 13 March 24th, 2015 05:15 PM
how to read data form a file using vba to change cells in excel fuze Excel VBA 2 April 6th, 2009 11:25 AM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
VBA - Check box KennethMungwira Access VBA 2 May 22nd, 2006 10:43 AM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM





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