How to enter dates in Excel cells using InputBox

[dilettante]

Hello,

I have a worksheet, which must remain protected out of the two cells where
the user has to enter two dates, with 5 sheets containing prices I compare
using a StartDate and a End Date.

There should be a button “Compareâ€
- starting a macro which opens a InputBox
- for the input of StartDate in the form as 25/05/1997
- and EndDate in the form as 25/09/1997,
- putting the dates respectively in the cells G3 and G4 of the sheet “Rates1â€

Should the date be written in wrong way a MsgBox should show the message
"Please use the form DD/MM/1997" (the year must be always this!!!)

I use follwing listing:
Sub InserimentoPeriodo()
Dim DataInizio As Date
Dim DataFine As Date

DataInizio = InputBox("Inserisci la data di INIZIO periodo nel formato
gg-mmm-aaaa", "Confronto Variazioni Prezzi", "1-mag-1997")

If Year(DataInizio) <> 1997 Then
MsgBox "Usare solo date dell'anno 1997", vbOKOnly
Exit Sub
End If
Range("E1").Value = DataInizio

DataFine = InputBox("Inserisci la data di FINE periodo nel formato
gg-mmm-aaaa", "Confronto Variazioni Prezzi", "10-set-1997")
If Year(DataFine) <> 1997 Then
MsgBox "Usare solo date dell'anno 1997", vbOKOnly
Exit Sub
End If
Range("E2").Value = DataFine

End Sub

But.... it does not work if the user opens the wrong sheet and I am not able
to put a button in the sheet to let the macro working.

Thank you for helping

 

[moon]

Hello,

I have a worksheet, which must remain protected out of the two cells where
the user has to enter two dates, with 5 sheets containing prices I compare
using a StartDate and a End Date.

There should be a button "Compare"
- starting a macro which opens a InputBox
- for the input of StartDate in the form as 25/05/1997
- and EndDate in the form as 25/09/1997,
- putting the dates respectively in the cells G3 and G4 of the sheet
"Rates1"

Should the date be written in wrong way a MsgBox should show the message
"Please use the form DD/MM/1997" (the year must be always this!!!)

I use follwing listing:

Sub InserimentoPeriodo()
Dim DataInizio As Date
Dim DataFine As Date

'E adesso:
If ActiveSheet.Name <> "Rates1" Then Worksheets("Rates1").Activate

 

[dilettante]

Grazie