VBA code to check cell contents agains a list of valid values

[Spartan300]

Hi,

I have a report which automatically finds the month of the year and then uses various VLOOKUPS to pull the relevant data into the report.

I have a button within the workbook so that the user can change the month displayed in cell C6 via an input box.

What I am hoping to do by way of VBA code is check that the month entered is valid, to avoid a tbale full of #NA errors.
I have tried putting data validation on the cell, but when it is entered from the input box it seems to overwrite it?

I was thinking of a VLOOKUP but I think I had some terrible code as it just crashed excel!

Thanks very much.

 

[Tom Urtis]

Just to get the ball rolling on this, your description of the problem does not make sense. Data Validation would be a much better way to mandate which month name is selected for cell C6 instead of using an input box where you never know what the user will try to enter. Yes you can programatically validate the inputbox for a proper month name but why go through all that if you can just use DV in the cell and stop using the inputbox altogether.

 

[Spartan300]

Thanks for the reply.

I wanted to have an input box as it is very clear for anyone using the report how to change the month, even if they are unsure of the basics of excel. I also wanted to build in a check so that a month that is yet to occur cannot be entered into the cell.

Thanks

 

[Spartan300]

Hello,

I have managed to accomplish what I wanted. This is probably far from the most elegant way but here it is...

Private Sub EventProc2(ByVal Target As Range)
Dim Lookup As String
On Error GoTo BadTimes
Lookup = Application.WorksheetFunction.VLookup(Range("C6"), Range("K4:K23"), 1, False)
Exit Sub
BadTimes:
MsgBox "Invalid Month", vbOKOnly + vbCritical
If vbOK Then
Range("I1").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub

Thanks for the help.