An InputBox prob

[Francis Hookham]

An InputBox prob

The ³Insert a Row using a Macro to maintain formulas² from
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
was great but now I am in another muddle!

I should like to follow that with an Input box which carries the date from
the cell in column 3 in the original row as the Input box Default so it can
be used again or over written if I choose. Bearing in mind that most (but
not all) dates are this year then I would normally only enter ³d/m² rather
than ³d/m/y² when last year

(I should prefer a ³UserForm² containing other entries - Item, Code as a
dropdown box, amount to be placed in other cells, but that is a different
ball-park and I have not ventured into UserForms since the days of Dialog
Boxes - was that what they were called?

I am not at all clear about the format of the date - as text, as a number of
days entered into a formatted cell - there is no need to calculate anything
using dates so text will be ok

This is as far as I have got and it does not work - can you help please?

If DatePaid = False Then
DatePaid = Application.InputBox(prompt:= _
"What was the payment date?", Title:="DatePaid", _
Default:="Input date")
DatePaid = Format(DatePaid, "d/m/yy")
Else
DatePaid = Application.InputBox(prompt:= _
"What was the payment date?", Title:="DatePaid", _
Default:=DatePaid)
End If
Cells(RowNumber, 3).FormulaR1C1 = DatePaid

I put in the If DatePaid thinking that, at start-up DatePaid (Dim DatePaid
As String or Dim DatePaid As Date) would be empty whereas, once the
programme was open and the macro run for the first time the previous date
would be stored in the DatePaid

Thanks again for this wonderful service

Francis Hookham

 

[J.E. McGimpsey]

An InputBox prob

The ³Insert a Row using a Macro to maintain formulas² from
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
was great but now I am in another muddle!

I should like to follow that with an Input box which carries the date from
the cell in column 3 in the original row as the Input box Default so it can
be used again or over written if I choose. Bearing in mind that most (but
not all) dates are this year then I would normally only enter ³d/m² rather
than ³d/m/y² when last year

(I should prefer a ³UserForm² containing other entries - Item, Code as a
dropdown box, amount to be placed in other cells, but that is a different
ball-park and I have not ventured into UserForms since the days of Dialog
Boxes - was that what they were called?

I am not at all clear about the format of the date - as text, as a number of
days entered into a formatted cell - there is no need to calculate anything
using dates so text will be ok

This is as far as I have got and it does not work - can you help please?

If DatePaid = False Then
DatePaid = Application.InputBox(prompt:= _
"What was the payment date?", Title:="DatePaid", _
Default:="Input date")
DatePaid = Format(DatePaid, "d/m/yy")
Else
DatePaid = Application.InputBox(prompt:= _
"What was the payment date?", Title:="DatePaid", _
Default:=DatePaid)
End If
Cells(RowNumber, 3).FormulaR1C1 = DatePaid

I put in the If DatePaid thinking that, at start-up DatePaid (Dim DatePaid
As String or Dim DatePaid As Date) would be empty whereas, once the
programme was open and the macro run for the first time the previous date
would be stored in the DatePaid

Couple of things first:

1) assigning a cell value using Format() doesn't work - the value is
passed to XL's parser which interprets the string and places the
resulting value in the cell, which has its own format. (The
exception is for a cell formatted as Text - which XL doesn't parse.)
So passing Format(date,"d/m/y") would be the same as entering the
date manually - XL will parse it, and turn it into a real date if
possible, then display the date in whatever format the cell is set
up for. To control the display format, use the cell's .NumberFormat
property.

2) VBA only deals with the 1900 date system - if you enter 8/1/2003
with the 1904 system active, VBA is passed 7/31/1999. You have to
explicitly make adjustments if you use the 1904 (Mac default)
system. Fortunately VBA's True value is interpreted as -1 in math
functions (unlike XL in which the value is interpreted as +1). So
the adjustment is just

datePaid = datePaid - 1462 * ActiveWorkbook.Date1904

-if the 1900 system is in use, ActiveWorkbook.Date1904 is false and
the product is 0 so no adjustment is made.
-if the 1904 system is in use, ActiveWorkbook.Date1904 is True and
the adjustment is

- 1462 * -1 ==> +1462

3) If you're going to want your variables to persist after your
macro finishes, you have to declare them as Static. That way they'll
maintain their value until either XL is closed or you edit the
project.

4) Only Variants can be empty. Dates and Strings are initialized (to
0 and "", respectively) when they are created. Empty is not the same
as False.


I rearranged things a bit to take out one of the conditions:

Const DATEFORMAT As String = "d/m/yy"
Static datePaid As Date
Static defaultStr As String

If defaultStr = "" Then defaultStr = "Input date"
datePaid = Application.InputBox( _
prompt:="What was the payment date?", _
Title:="DatePaid", _
Default:=defaultStr, _
Type:=1)
datePaid = datePaid - 1462 * ActiveWorkbook.Date1904
defaultStr = Format(datePaid, DATEFORMAT)
With Cells(RowNumber, 3)
.Value = datePaid
.NumberFormat = DATEFORMAT
End With