Macro Not Working

[robertguy]

Hi can any please advise me why my Macro is not working

The Macro allows users to Input dates via a message box which shoul
then be input in cells B1, B2, B3 & B4 - but this does not happen - I
addition when I try to change the sheet name in the macro from Sheet2 t
another name i.e. mydata it displays an error



Sub Input_Dates1()
'
'
MyName = InputBox("Please Enter Date for Q1")
If MyName = "" Then
Exit Sub
End If
Sheet2.Range("$B$1").Value = MyName

MyName = InputBox("Please Enter Date for Q2")
If MyName = "" Then
Exit Sub
End If
Sheet2.Range("$B$2").Value = MyName

MyName = InputBox("Please Enter Date for Q3")
If MyName = "" Then
Exit Sub
End If
Sheet2.Range("$B$3").Value = MyName

MyName = InputBox("Please Enter Date for Q3")
If MyName = "" Then
Exit Sub
End If
Sheet2.Range("$B$4").Value = MyName


End Sub



Any help would be appreciated


Excel version 2000



Many thanks



Ro

 

[LB79]

Are you sure you have a sheet 2 in your workbook

 

[Guest]

hi,
try this
Dim myname As Date

myname = InputBox("Please Enter Date for Q1")
If IsNull(myname) Then
Exit Sub
End If
Sheet2.Range("$B$1").Value = myname

but if they don't input a date there will be a type
mismatch error.

 

[icestationzbra]

when you say it is not working, what is the error you are getting?

the only thing that i can see is that you have not defined a variabl
'myname'

 

[Alan]

It works OK for me, do you have in a standard module? Theres nothing wrong
with the code but
Sheet2 is Sheet2 regardless of what you may rename it,
If you use Sheets("MyData") it will respond to the name you have given the
sheet,
Regards,

 

[LB79]

When i ran this code it worked ok - all i did was change the sheet nam

 

[LB79]

If you use the code you pasted you should either change the sheet name
in the code to match the sheet name in your workbook, or change your
sheet name in your workbook to "Sheet2" - the sheet names must match or
it will not work.

 

[Alan]

Sorry but that's not quite right, if you use Sheet2.Range("A1") as opposed
to Sheets("Sheet2").Range("A1") then Sheet2 remains as Sheet2 regardless of
what you rename it. In the VB Editor one can see Sheet2 (Sheet2) which is
how VB sees Sheet2, the Sheet2 in brackets is what you choose to rename it,
Regards