extending the (VBA code) functionality to a data-form

[buffalo]

Hi,

So far I have been entering data manually in the excel worksheet. Now this data is "user-friendly"

So for instance, in Column 5: I enter b or buy or Buy..the VBA code reads
that and replaces the cell's value to Buy.

THis, I do for quite a few columns ...with different "final values" in the cells based on the VBA code.

for instance the code looks like

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    
    If Target.Row = 1 Then Exit Sub
    
    If Target.Column = 2 Then
   ' anything starting with H or h goes to Hamer, etc...
      
        If Left(UCase(Target.Value), 1) = "H" Then
            Target.Value = "Hamer"
        End If
        
        If Left(UCase(Target.Value), 1) = "M" Then
            Target.Value = "M"
        End If
    End If
    
    If Target.Column = 5 Then 
    ' For anything with B,b, S,s
    
       If Left(UCase(Target.Value), 1) = "B" Then
            Target.Value = "Buy"
        End If
        
        If Left(UCase(Target.Value), 1) = "S" Then
            Target.Value = "Sell"
        End If
    End If
    
    If Target.Column = 6 Then ' For vehicles
    ' another type of user-friendly thing

       Target.Value = UCase(Target.Value)
    End If

Application.EnableEvents = True
End Sub

Now how do I extend this stuff in Data-forms?
When I enter the values, the data is not modified by the VBA code anymore

..any suggestions?

Would using user-forms solve this problem in a better way?

thanks in advance

 

[nisht]

for first time why you need that:

have you unchecked:

tools -> options -> edit -> enabled autocomplete for cell values. ?

and yes useform will be do a better jobs atleast for intermediate users like us..or else see some guru can suggest you to work on data forms of excel.