I have put together some vba to change a specific columns value to proper capitalization after data has been entered. I initially had no issues with the code that I put together, however, I needed to add a dropdown menu (through data validation rules) of possible selections to one of the columns (column 8). After I added the validation rule, whenever data is manually typed in the column or selected from the dropdown I get a runtime error (Method '_Default' of object 'Range' failed). If I turn the validation rule off, I no longer get the error. After a day and a half I still cannot figure out what is causing the issue. Any insight would be gratefully appreciated.
Here is the worksheet code:
Private Sub worksheet_change(ByVal Target As Range) '-- no need to apply the function to the headings' If Target.Row < 5 Then Exit Sub '-- if you delete something we will get an error for having nothing to put in Proper' If IsEmpty(Target()) Then Exit Sub If Target.Column = 3 Then Target.Offset(0, 0) = StrConv(Target, vbProperCase) End If If Target.Column = 5 Then Target.Offset(0, 0) = StrConv(Target, vbProperCase) End If If Target.Column = 7 Then Target.Offset(0, 0) = StrConv(Target, vbProperCase) End If If Target.Column = 8 Then Target = StrConv(Target, vbProperCase) '-- check to see if you are finished with your data entry' If Target.Offset(0, 5) > 0 Then MsgBox "You still have " & Target.Offset(0, 5) & " field(s) left to fill out. Please finish entering data for your record.", vbCritical, "Unfinished record!" '-- so we don't get an infinate loop of msgboxes' Target.Offset(0, 5) = 0 Target.Offset(0, 5).ClearContents End If End If End Sub
I don't see any obvious reason you would get that error and you didn't tell me which command is highlighted when you get the error and go into debug mode.
Best I can offer is to take a look at the workbook and see if I can find the cause.
if you decide to send the workbook to me, tell me what version ofyou are using and if the error only happens when you do a specific sequence or if you enter data in a specific location, then tell me that. (how do I reproduce the error).
you can send it to