Excel 2013 - Increment Date by 1 year using a Macro/Button

I have a spreadsheet that I've created with conditional formatting based on how close a domain is to it's expiry date.

Here it is:

Domain Renewal Spreadsheet
I've cracked the conditional formatting, but as it stands I still have to modify the date by hand once the renewal has been processed. I want this automated with a button via a macro if possible. I've attached a screenshot of the button and explained the functionality required. I need each button to increment the date by 1 year in that row.

The thing I'm most stumped on, is how to replicate each button rather than do each one by hand! :-(

Thanks in advance!
LVL 1
UncleVirusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Good morning.

Just a thought... You can use one button that will add a year to column D in the row that is selected with the following code.

Private Sub CommandButton1_Click()
    Cells(Selection.Row, 4).Value = DateAdd("yyyy", 1, Cells(Selection.Row, 4).Value)
End Sub

Open in new window


If you really want to have a button for each row, you can capture the bottom right cell of the button with this code

CommandButton1.BottomRightCell.Row

I dont really recommend this though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UncleVirusAuthor Commented:
Brilliant, thank you! I've implemented it as follows: I've pruned all of our data from the spreadsheet and will upload here. It may be potentially useful for someone else.

The spreadsheet will warn you of expiring domains within 3 months and turn them orange :-)

(Please note, it contains Macro's for the buttons!)

Domain-Renewals--Blank-.xlsm
0
UncleVirusAuthor Commented:
Brilliant, bang on the money! :-)
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

FamousMortimerCommented:
Thanks, glad to help!

Also, in my post, i made it seem like you have to select the whole row, but as long as any cell in the row you wish to change is selected, it will work.
0
UncleVirusAuthor Commented:
Yeah I worked that one out, no sweat :o)

The only slightly annoying thing is that Excel doesn't allow you to Alt-Z/Undo the actions of a Macro! Not a major biggie but this has helped us out no-end. Embarassingly, I sysadmin hundreds of systems but profess to being a complete Excel beginner!
0
FamousMortimerCommented:
Yeah, that is a peeve of mine too.  In some of my programs I've actually saved a copy of the file before the macro runs if it has the potential to cause problems, to overcome the inability to Undo macro operations.  Usually though, I suppose the macro should be fool proof, but we are all human.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.