CODEWITHSUNDEEP
excelvba
arin
arin

Reputation: 600

A button to modify a cell in excel

I need to place a button in excel sheet to alter a cell content.

for example , I want to fill cell A2 with a date structured as dd/mm/yyyy and once I select the cell and press the button , the cell content (the date) will move forward 1 day.

"1/1/2013

click

2/1/2013"

is such thing possible to be implemented ? can you help ?

Thanx in advance

Upvotes: 0

Views: 87

Answers (2)

Makah
Makah

Reputation: 4523

You can try something like this

sub myClick()
  Selection.Value = Selection.value + 1
end sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149325

You need to use the DateAdd(). Put this code in the button click event. Also change Sheet1 to the relevant sheetname.

With ThisWorkbook.Sheets("Sheet1")
    .Range("A2").Value = DateAdd("d", 1, .Range("A2").Value)
End With

Syntax

DateAdd(interval, number, date)

The interval argument has these settings: 

yyyy : Year 
q : Quarter 
m : Month 
y : Day of year 
d : Day 
w : Weekday 
ww : Week 
h : Hour 
n : Minute 
s : Second

FOLLOWUP FROM COMMENTS

I want to keep the selected cell dynamically , in other words I don't want to be restricted to cell A2 I need to select any cell in the sheet or a complete row or column and apply the action on it.

Try this

Dim aCell As Range

If TypeName(Selection) = "Range" Then
    For Each aCell In Selection
        With ThisWorkbook.Sheets("Sheet1")
            aCell.Value = DateAdd("d", 1, aCell.Value)
        End With
    Next
End If

Upvotes: 3

Related Questions