User form and multiple list box validation questions

Saps

New Member
Joined
Jul 30, 2006
Messages
13
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
Rich (BB 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
Rich (BB 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
Rich (BB code):
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,136,348
Messages
5,675,238
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top