Sheet VBA to Auto-change Certain Values

[hajiali]

Hello Everyone.

This might be simple but can not figure out. I need a VBA to Automatically change certain Values.

Ex. in Range ("A1:A1000, D1,D1000, F1,F1000)

to automatically change the following values after its type.

1 to 01
1A to 01A
1B to 01B
1C to 01C

same for all Single Digit Numbers 2-9 add a 0 in front as shown above.

 

[Jeffrey Mahoney]

This code needs to be placed in the SHEET module level of VBA; for the sheet you want to watch. I created a named range on my sheet called MyWatchRange; you can name it anything. This adds a leading zero if the first character of the cell value is a number other than zero.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim Cel As Range
  Dim Astr As String
  
  Set i = Intersect(Target, Range("MyWatchRange"))
  If Not i Is Nothing Then
    Application.EnableEvents = False
    For Each Cel In i
      Astr = Cel.Value
      If InStr("123456789", Left(Astr, 1)) > 0 Then
        
        Cel.Value = "0" & Astr
      End If
    Next Cel
    Application.EnableEvents = True
  End If
  
End Sub

 

[hajiali]

Thanks Jeffrey auto change value part works great. However when I attempt to delete a value in the range it puts double zeros "00" in the cells that the value is being deleted.

 

[Jeffrey Mahoney]

I would not have expected that. The red text below was changed

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim Cel As Range
  Dim Astr As String
 
  Set i = Intersect(Target, Range("MyWatchRange"))
  If Not i Is Nothing Then
    Application.EnableEvents = False
    For Each Cel In i
      Astr = Cel.Value
      If Astr <> "" And InStr("123456789", Left(Astr, 1)) > 0 Then
       
        Cel.Value = "0" & Astr
      End If
    Next Cel
    Application.EnableEvents = True
  End If
 
End Sub

 

[hajiali]

Thank you so much that works like magic. I really appreciate the quick response and replies.