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.

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


"Robert Christie" wrote:

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.

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


"Robert Christie"
wrote:

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.


.

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



In article ,
"Robert Christie" wrote:

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.

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.


-----Original Message-----
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



In article ,
"Robert Christie"
wrote:

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.
.

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.


-----Original Message-----
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



In article ,
"Robert Christie"
wrote:

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.
.

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


"Robert Christie" wrote in
message ...
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.


-----Original Message-----
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



In article ,
"Robert Christie"
wrote:

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.
.