VBA Code - Trim function

[KatKitKat]

Hi there, I need some help with the trim function in a code.

I have a drop-down validation list in Excel with a 5 or 6 character number, hyphen, then text (see sample list below). I added the following code to the worksheet tab because I want to trim anything from the hyphen to the right. Basically, I only want the number to show. Note that number codes are 5 or 6 characters long.

Using the VBA code that I have shown below, when making the selection (using the first item in the list for example), instead of "00890" appearing in the cell, I get "890". Any ideas on how to correct the code or change my data to make this work? Thanks, Kat


Sample Listing for drop down

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

 

[Anthony47]

For example:

Target.Value = chr(39) & strInput

Or format the cell as Number /Customized, type=0000

 

[Fluff]

How about

   Application.EnableEvents = False
      Target.NumberFormat = "@"
      Target.Value = strInput
   Application.EnableEvents = True

 

[KatKitKat]

How about

   Application.EnableEvents = False
      Target.NumberFormat = "@"
      Target.Value = strInput
   Application.EnableEvents = True

This worked but created another issue: I have vlookup formulas in other cells based on this cell (which is also why I needed the number format to be correct). The vlookups work when I select an item from the list. However, the first option in the selection list is a blank (empty, no data) and when the blank is selected, the fields with the vlookup formulas are producing a #N/A. The blank or empty option is necessary for the blank rows.

 

[Fluff]

As that is a different question, it needs a new thread. Thanks

 

[KatKitKat]

As that is a different question, it needs a new thread. Thanks

got it. thanks

 

[Fluff]

You're welcome.