problem setting the value from a function

[Excel-General]

Hi:
I wanted this macro to accept the date the user gives in the dialog
box. In all the sheets on the range v6 it should put that date but it
should only be on sheets2 and onward not on sheet1. I wanted it to
only ask for the date once. This macro asks for the date on every
iteration and I don't know how to set the value for the variable
inputDate? It doesn't change anything if I move the variable on the
right or left side of the function.

Thanks again.

Sub months1()
Dim inputDate As Date
Dim i As Long

inputDate = InputBox("Enter a date:", "Date", Date)
For i = 2 To Worksheets.Count
Sheets(i).Range("v6") = inputDate

Next i

End Sub

 

[Rick Rothstein \(MVP - VB\)]

If I understand what you want to do correctly, try this. Add a Module to
your project (Insert/Module from VBA's menu) and paste the following into
it's code window...

Public GotDate As Boolean
Public InputDate As Date

What this has done is create two global variables. Unlike variables declared
within a procedure (such as your Sub), variable declared in Modules live
until Excel is closed; so, once set, InputDate will hold its value every
time you call up your Sub. However, we need a way to track that the date has
been stored. That is what the global GotDate Boolean variable is for. In
order to use these, we have to modify your Sub. Here is one way to do it...

Sub months1()
Dim i As Long
If GotDate = False Then
InputDate = InputBox("Enter a date:", "Date", Date)
GotDate = True
For i = 2 To Worksheets.Count
Sheets(i).Range("v6") = InputDate
Next
End If
'
' <<Rest of your code goes here>>
'
End Sub

The first time the Sub is run, GotDate is False (the default value for a
Boolean variable), so the If-Then block runs. The InputBox get the date from
the user and stores it in the global variable InputDate; we set GotDate to
True to indicate the date has been set.... then the rest of your code runs.
The next time the Sub is called, GotDate is True (it will stay True until it
is changed) and, likewise, InputDate is still holding the date when your Sub
first ran. When the If-Then test is performed, GotDate is not False, so the
If-Then block code is skipped; however, the rest of your code will run.

Rick