VBA doesn't allow entry to be deleted

I'm attempting to use VBA that converts a month name from a data validation list to the month number value in the cell. It does so but if I attempt to delete the contents of the cell, I get an error code and message that the VBA needs to be debugged. I've tried to modify the code to allow the clearing of contents of the selected cell but nothing I've tried works. Can someone please offer a solution? Here's the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
selectedNa = Target.Value
If Target.Column = 50 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("Month"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

Thank you in advance.

Further to post #2, maybe you don't need the vlookup?

Please Login or Register  to view this content.

Thank you, TMS and ByteMarks. I tried the suggested modifications and receive a run-time type mismatch error '13' for both. The VBA is using vlookup referencing the named range "Month" (AZ52:BA64) to provide a data validation list of month names (AZ53:AZ64) and instead enter the month number value (BA53:BA64) in the cell where the data validation dropdown is. I've made sure that data validation is allowing manual entry. If it helps to know, I'm using Excel 2007.

Thank you TMS. I created an example in another workbook and it worked just fine leading to the discovery of what is causing the problem. Now I know specifically what the problem is that I need help to solve whish follows below. I followed your instructions to attach the example workbook file but only show a file code(?) in the body of the post. I also found this on the Go Advanced page:
You may not post new threads BB code is On
You may not post replies Smilies are On
You may not post attachments [IMG] code is Off
You may not edit your posts HTML code is Off
Does this refer to me?

That said, the Type Mismatch error is caused because the data validation dropdowns in which a month is selected are in merged-column cells while the VBA only references column 50 (AX). How can I replace the column reference in the VBA to the specific range, $AX$4:$AY$15, to allow merged AX4/AY4 (etc.) to be deleted together?

Thank you again.

I think everyone sees that, at least, I do so I assume that they do.

For the record, it's the same for me.

I remain unconvinced that you need the vlookup to get the month number.

Month to Number example.xlsm

Thank you again, TMS. I hope the attachment worked. If so:

As you will see in the example, I've set up data validation in merged cells as they are in my original file. My VBA references only column E. How can I modify the code instead to reference the range E2:F13 and will that allow the substitution of the month number for the name and also then permit entries from the data validation to be deleted?

I'm grateful for your input, ByteMarks. If I don't use vlookup, is there a good alternative to select the month name from the data validation dropdown but enter the month's corresponding number instead in the cell?

An example of the problem is now included in my previous post.

Thank you, ByteMarks. Your code works beautifully as an alternative to my original code. Because of the column width formatting of the original file, providing the best user-friendly placement of month selection dropdowns requires merging cells. Is it possible to allow for merged cells in the code or must I find an alternative placement of the dropdowns to avoid merging cells?

It should already allow for the merged cells by assessing the top left cell of the target

Please Login or Register  to view this content.