Macro to add 1 or more days to a date

R

Robert Christie

Hi everyone

using Xl2002:
I'm trying to create a macro to select cell B2 which
contains a dd-mm-yyy date format and add or delete a
number of days depending on a number enter into a second
cell E2( I am assumming that by entering a minus -2 in
this cell the macro would take 2 days off).
i.e. 1-May-2004 would become 29-April-2004.
The macro wiil be attached to a form button.

TIA

Bob C.
 
K

keepitcool

Robert,

this code example would go into the sheet's code module,
entering a negative value in b1 would(ofcourse) lower a1's date while a
positive number would increase it.

note: \ 1 is equivalent to cint([a1]) (makes it an integer)
note: [a1] is short notation for cell A1 on the activesheet


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

'Doubleclicking A1 will add B1 to it's value
If Target.Address <> "$A$1" Then
Exit Sub
ElseIf [b1].Value = 0 Then
Exit Sub
Else
If IsEmpty([a1]) Then [a1] = Date
'note: all rounded to integers
[a1] = [a1] \ 1 + [b1] \ 1
End If

End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
R

Robert Christie

Hi keepitcool

Thank you for your quick reply and the code is working A
OK.

I added a Range("??").Select line of code before the End
Sub last line to move curser to a 'neutral corner' as a
safety measure and to be able to doubleclick the Cell A1
again. As you know Doubleclicking a cell allows you to
edit in the cell.

Thank You

Regards Bob C.

-----Original Message-----
Robert,

this code example would go into the sheet's code module,
entering a negative value in b1 would(ofcourse) lower a1's date while a
positive number would increase it.

note: \ 1 is equivalent to cint([a1]) (makes it an integer)
note: [a1] is short notation for cell A1 on the activesheet


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

'Doubleclicking A1 will add B1 to it's value
If Target.Address <> "$A$1" Then
Exit Sub
ElseIf [b1].Value = 0 Then
Exit Sub
Else
If IsEmpty([a1]) Then [a1] = Date
'note: all rounded to integers
[a1] = [a1] \ 1 + [b1] \ 1
End If

End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Hi everyone

using Xl2002:
I'm trying to create a macro to select cell B2 which
contains a dd-mm-yyy date format and add or delete a
number of days depending on a number enter into a second
cell E2( I am assumming that by entering a minus -2 in
this cell the macro would take 2 days off).
i.e. 1-May-2004 would become 29-April-2004.
The macro wiil be attached to a form button.

TIA

Bob C.
Click to expand...

.
Click to expand...
 
J

JE McGimpsey

You can set the Cancel property to True instead of "moving the cursor":

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
With Target
If .Address(False, False) = "B2" Then
Cancel = True
If IsNumeric(Range("E2").Value) Then _
.Value = .Value + Int(Range("E2").Value)
End If
End With
End Sub
 
R

Robert Christie

Hi JE
Thank you for your post, it works well.
But why oh why are there so many ways to achieve the same
thing in Excel.
It certainly makes mastering Excel very hard.

Thank you

Regards Bob C.
 
R

Robert Christie

Hi Again JE

Is it possible to have more than 2

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)

in the same sheet.
I tried adding '_two' after DoubleClick and changing cell
references but it did not work.

TIA

Regards Bob C.
 
C

Chip Pearson

Robert,

No, you cannot have more than one event procedure for an event.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

Robert Christie

Thanks Chip

I got around it by using the 'BeforeRightClick' for a
second date on sheet. Bit daggy but it's for personal use
only.

Regards Bob C.
 
K

keepitcool

You could have 1 eventhandler to handle several target cells.
I'm typing this.. code shows the idea etc..

sub Before_RightClick(byval target as range)
select case target.address
case "$A$1", $C$3: call Dateclicker1(target)
case "$e$7", $f$8: call Dateclicker2(target)
Case "$D$3" : call NameClicker(target)
end select
end sub

sub Dataclicker1(tgt as range)
target=target+3
end sub

etc



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
R

Robert Christie

Hi keepitcool

You guys amaze me.
Once more a positive answer to a question.
I believe every reasonable question I have ever asked
this group has been replied to in the positive.
Is the rumour true Excel can even float on water. ( :)
(v vbg).

I thank each of you once again.

keepitcool
JE McGimpsey
Chip Pearson

Thankyou

Regards Bob C.
 
Top