Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2021, 09:20 PM
Peterson Peterson is offline Change active cell after running macro in active cell via selection change Windows 10 Change active cell after running macro in active cell via selection change Office 2019
Competent Performer
Change active cell after running macro in active cell via selection change
 
Join Date: Jan 2017
Posts: 130
Peterson is on a distinguished road
Default Change active cell after running macro in active cell via selection change

I have a macro for a particular worksheet that adds today's date to Column A when the active cell is blank and the cell above it is also today's date. After the date is added, I'd like the active cell to be offset to another column.

I've been unable to activate the cell -- what am I doing wrong here?



Code:
Private Sub Worksheet_SelectionChange2(ByVal Target As Range) ' 03/06/2021
' This macro adds today's date in Column A if the cell is blank and the date
' in the cell above it is today.

    If Target.Column = 1 Then
        If ActiveCell = "" And ActiveCell.Offset(rowOffset:=-1, columnOffset:=0) = Date Then
            ActiveCell = Date
        End If
        
        ' This doesn't work, nor do a number of other approaches I've tried...
        ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate
    End If
End Sub
Reply With Quote
  #2  
Old 03-06-2021, 11:07 PM
Purfleet Purfleet is offline Change active cell after running macro in active cell via selection change Windows 10 Change active cell after running macro in active cell via selection change Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Please add example workbooks to save us wasting time recreating them

The below should work as a stand alone Macro, not done it as selection change

Code:
Sub CheckDate()

    Dim TheDate As Date
    
    TheDate = Date
    
    If ActiveCell.Value = "" And ActiveCell.Offset(-1, 0) = TheDate Then
        Range("A" & ActiveCell.Row) = TheDate
        
    End If

End Sub
Reply With Quote
  #3  
Old 03-07-2021, 11:55 AM
Peterson Peterson is offline Change active cell after running macro in active cell via selection change Windows 10 Change active cell after running macro in active cell via selection change Office 2019
Competent Performer
Change active cell after running macro in active cell via selection change
 
Join Date: Jan 2017
Posts: 130
Peterson is on a distinguished road
Default

Thank you for taking the time to respond to my post and upload some code. I apologize for not providing a document to work with. In addition, re-reading my post, I see I wasn't terribly clear about the issue.

This lack of clarity hints at the problem: I was tired. I miscounted the columns, the offset value was therefore incorrect, and the macro instead tried to activate a cell with a formula in it, which wouldn't activate, for some reason. I've adjusted the offset and the macro now works.

Thanks again for your reply. The added lesson here for me is, always upload a file.
Reply With Quote
  #4  
Old 03-07-2021, 12:19 PM
Purfleet Purfleet is offline Change active cell after running macro in active cell via selection change Windows 10 Change active cell after running macro in active cell via selection change Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Glad it helped
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell equal value new value of active cell which is a drop down hassanmoukadem Excel Programming 1 04-13-2020 07:54 PM
Active cell not the one I select with curser. hayesfam3 Excel 0 08-15-2019 05:14 AM
Change active cell after running macro in active cell via selection change Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow FUGMAN Excel Programming 7 02-05-2017 08:37 AM
Change active cell after running macro in active cell via selection change Change Macro so it does not refer to "Active Document" trent.bourne Word VBA 2 10-30-2016 01:49 AM
Change active cell after running macro in active cell via selection change Change cell color when selection is made from a drop down list fedcco Excel 12 08-28-2012 10:43 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft