Excel Macro Help

[spike250]

Hi all,

I am new to VBA in excel and have written the following code =
Private Sub CommandButton1_Click()
Dim strtDate As Date, fnshDate As Date
Dim n As Integer
strtDate = DateValue(Now)
fnshDate = DateValue("Mar 22,2010")
n = DateDiff("d", strtDate, fnshDate)
MsgBox n
Msg = MsgBox("Do you want to Transfer the Answer?", vbYesNo + vbQuestion, "SumMsgBox")
End Sub

I was hoping that someone could help me?

I am trying to get the answer from the Message Box transfered into a cell in Excel say A15 and everytime the button is pressed it moves down a line

also

I am trying to get it to add the date that the button was pressed in say B15.

Hope someone can help?

If this is not possible could someone sugest another way?

Thanks

 

[losmac]

Private Sub CommandButton1_Click()
Dim strtDate As Date, fnshDate As Date
Dim n As Integer

strtDate = DateValue(Now)
fnshDate = DateValue("Mar 22,2010")
n = DateDiff("d", strtDate, fnshDate)

'if user choose YES then enter date in cell in column A and row, which is actually selected
If MsgBox(n & vbcr & vbcr & "Do you want to Transfer the Answer?", vbYesNo + vbQuestion, "SumMsgBox") = vbYes Then Thisworkbook.Worksheets("Sheet1").Range("A" & ActiveCell.Row) = n

End Sub

 

[spike250]

Re: Macro Help

Hi,

Thanks for your help, but by any chance do you know of a way to get it to transfer the date the button was pressed into column B.

Thanks again

Spike

 

[losmac]

The better way is to write own function.

1. Open VBE window -> ALT+F11
2. Insert Module (menu Insert -> module)
3. Copy function:
Option Explicit 'declare variants

Function GetDateDiff(dStartDate As Date, dEndDate As Date) As Integer
GetDateDiff = DateDiff("d", dStartDate, dEndDate)
End Function
4. In Sheet1 enter the fallowing values/formulas (you will recognize formulas by "=")
A1 -> =TODAY()
B1 -> date, for example: Mar 22,2010
C1 -> =GetDateDiff(A1;B1)
5. Save you work