Hello everyone.
I have been getting a VBA error message in an Excel file that has been in use for a few months with no problems until now. There have been no changes to the formulas or coding and the cells which are impacted by the VBA code is locked so no one can enter incorrect info or change the data list.
I have an excel file where the user makes a selection from a drop-down list in Cell F. The options are a string with a 5 or 6 character number, followed by a hyphen then text (e.g., "00890-Concrete"). The below code trimmed everything from the hyphen to the right of the selection so only the number appears in the cell ("00890"). This is important because there are other cells in the spreadsheet that populate data via a VLOOKUP formula based on the number only.
This formula has been working fine until yesterday. We now receive a "run time error 13" message when the user goes into any of the cells with this drop-down field and when I debug, the "strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))" line is highlighted in the VBA coding.
I am unsure if my company did a Windows update and that is why this is happening? I tried replacing the hyphen with an equal sign or blank space and still have the run time error. Anyone have a clue why this would happen? Is the TRIM function now obsolete?
Any guidance would be greatly appreciated.
Kat
I have been getting a VBA error message in an Excel file that has been in use for a few months with no problems until now. There have been no changes to the formulas or coding and the cells which are impacted by the VBA code is locked so no one can enter incorrect info or change the data list.
I have an excel file where the user makes a selection from a drop-down list in Cell F. The options are a string with a 5 or 6 character number, followed by a hyphen then text (e.g., "00890-Concrete"). The below code trimmed everything from the hyphen to the right of the selection so only the number appears in the cell ("00890"). This is important because there are other cells in the spreadsheet that populate data via a VLOOKUP formula based on the number only.
This formula has been working fine until yesterday. We now receive a "run time error 13" message when the user goes into any of the cells with this drop-down field and when I debug, the "strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))" line is highlighted in the VBA coding.
I am unsure if my company did a Windows update and that is why this is happening? I tried replacing the hyphen with an equal sign or blank space and still have the run time error. Anyone have a clue why this would happen? Is the TRIM function now obsolete?
Any guidance would be greatly appreciated.
Kat
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strInput As String
'column F
If Target.Column <> 6 Then Exit Sub
If Target.Value = "" Then Exit Sub
'find the hyphen
strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))
'disable events before writing to worksheet
On Error Resume Next
Application.EnableEvents = False
Target.Value = strInput
Application.EnableEvents = True
End Sub