New error with TRIM function

[KatKitKat]

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

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

 

[Joe4]

You will get that error if the entry does not contain a hyphen.

Here is a little better way to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
 
   Dim arr() As String
 
   'column F
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
   If Target.Value = "" Then Exit Sub
 
   'check for hyphen the hyphen
   If InStr(1, Target, "-") > 0 Then
      arr = Split(Target, "-")
      'disable events before writing to worksheet
      Application.EnableEvents = False
      Target.Value = Trim(arr(1))
      Application.EnableEvents = True
   End If
 
End Sub

If it does not find a hyphen, it will do nothing.

 

[KatKitKat]

Thank you for your quick reply, Joe.

This almost worked. There is no longer a run time error, but the trim worked the wrong way. For example:

If the option selected is "00890-Concrete", I need "00890" to appear in the cell. As is, "Concrete" appears in the cell. I am unsure how to change it so I get the number and not the text to populate. This coding is new to me. Could you provide some feedback as to what to change?

Thanks
Kat

 

[Joe4]

Whoops, arr(1) returns the part after, arr(0) would return the part before.
But we also need to pre-format the cell to text to not lose the leading zeroes.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
   Dim arr() As String
 
   'column F
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
   If Target.Value = "" Then Exit Sub
 
   'check for hyphen the hyphen
   If InStr(1, Target, "-") > 0 Then
      arr = Split(Target, "-")
      'disable events before writing to worksheet
      Application.EnableEvents = False
      Target.NumberFormat = "@"
      Target.Value = Trim(arr(0))
      Application.EnableEvents = True
   End If
 
End Sub