User form and multiple list box validation questions

[Saps]

Excel 2003

I have a user form with list boxes that allow the user to select the month and year. It is a production report and is usually started a few days after the previous month is completed.

I want to validate that the user is choosing the correct month. Which in most instances would be the previous month Format(DateSerial(2000, Month(Date) - 1, 1), "mmmm") . The validation needs to be a vbyesno message box which will either allow the user to continue or re-enter in the month by taking it back to the user form.

I also want to validate the year which should always be the current year unless this is being done for December(the macro will actually be ran in Jan). If it is being done for december then i want the code to take the user back to the user form to re-select until the previous year has been selected. If it is being ran for any other month than december then I want the code to take the user back to the user form if the current year has not been selected.

Thanks for your help!!


Command Button Code

Private Sub CommandButton8_Click()
Dim ret As VbMsgBoxResult

ret = MsgBox("If you are not in admin you should not be pushing this button.  Do you wish to continue?", vbYesNo + vbQuestion, "A question ...")

If ret = vbYes Then


'adds the months to the list box.  Starts the list with the previous month name

With UFMonth.LBMonth
    .RowSource = ""
    .AddItem Format(DateSerial(2000, Month(Date) - 1, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date), 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 1, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 2, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 3, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 4, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 5, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 6, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 7, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 8, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 90, 1), "mmmm")
    .AddItem Format(DateSerial(2000, Month(Date) + 10, 1), "mmmm")
'Defaults the previous month
    UFMonth.LBMonth = Format(DateSerial(2000, Month(Date) - 1, 1), "mmmm")
End With

'adds the years to the list box.  Starts the list with the previous year
With UFMonth.LBYear
    .RowSource = ""
    .AddItem Year(Date) - 1
    .AddItem Year(Date)
    
'Defaults the current year

    UFMonth.LBYear = Year(Date)
    
End With

Application.ScreenUpdating = False
UFMonth.Show

 
MsgBox "You selected " & userselection & " " & UserSelection2

Else

End If
End Sub

[/color]

User form code

Private Sub OK_Click()

'Validates that selections have been made for both month and year
If LBMonth.ListIndex = -1 Then
    MsgBox ("You Need to select a month")
    Exit Sub
End If
If LBYear.ListIndex = -1 Then
    MsgBox ("You Need to select a Year")
    
    Exit Sub
End If

    userselection = LBMonth.Value
    UserSelection2 = LBYear.Value
     
    Unload UFMonth

    Sheets("Data Entry").Range("C10") = userselection
    Sheets("Data Entry").Range("D10") = UserSelection2

End Sub