check for user entered year value not working 3

[chilly442]

In an user form I have a text box for month and a text box for year. I need to check and see if there is a value in each of these before going on with the rest of the code.

My if statement for the MyMonth works, but not the MyYear check. I get the "error Type mismatch '13'."

Here is the first part of my code.
Any ideas?!?

Private Sub cmdAdd_Click()
Dim ws As Worksheet, MyYear As Variant, MyMonth As String
Dim MyRow As Long, MyCol As Integer
Dim response As Integer
Set ws = Worksheets("Data")

MyMonth = Me.MyMonth
If MyMonth = "" Then
MsgBox "Please enter a Month!", vbYes, "Month"
GoTo Last2
Else

MyYear = Me.MyYear
If IsNull(MyYear) Then
MsgBox "Please enter a Year", vbYes, "Month"
Else
My.Year = CInt(Me.MyYear)

 

[SoCalAccessPro]

Dim MyYear as a String

Then check for empty string ("") like you do MyMonth



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[AnotherHiggins]

Why is MyRow a Long but MyCol an Integer.

You might want to consider using a calendar pop-up to allow users to pick a date. You don't say what application you're using, but you should be able to find multiple examples online.

Or if you'd prefer, since you seem to only be using months and years (not days), you could just offer up a list of all 12 months and the last X years.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[chilly442]

Thank you for your post.

I need to verify that there is no value in the box, or that the value in the box is an integer. If I Dim the MyYear as a string and they put a "x" in the box, then the code fails down the line. I need the year and month so that I can convert it to a cell range for data to go into.
Any suggestions on what I need to change to get this to wotk?

Thanks in advance for any help. It is greatly appreciated.

Chilly442

 

[chilly442]

Sorry. I am using Excel. My form takes historical data and puts it into the year and month specified by the user. I need to make sure that the month is a month, and the year is a year. I have the month working, but the year is not. Sorry for the confusion. I should have been more clear.

Chilly442

 

[AnotherHiggins]

I'd still suggest that you look for a clickable interface. Users tend to like it and it might be easier to control input. If you're interested in pursuing that, let us know.

But if you'd rather stick with what you've got, then have the form instruct the user to input the four-digit year. Then,

If Not myYear Like "####" Then
...

08 will fail
x will fail
20081 will fail

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[chilly442]

OK. I have dates going all the way back to 1980. How would I go about making a combo/dropdown list that came from a range in a sheet? That way if they find data for years not on the list they can just add the year in and be done.
Sorry if I am being a pain in the you know what.

Chilly442

 

[SoCalAccessPro]

Oh, that's easy.

Just right click the combo box on the form, and go down to the RowSource property. Then, type in an exsisting named range, or your range in Row Column format. (I.E., A1:A31)



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com