Excel 365 VBA - Automatically putting in Hyphens/Dashes in Specific Column

[TheShyButterfly]

Hi there, Thank you for your time checking out my query.

I have searched the web and haven't been able to find a solution.

My objective:
When a user enters in data in a particular spreadsheet:
"Daily Log Sheet", Column BA, for example alphanumeric data (CW04VW), I would like it to automatically transform/format it into the CW-04-VW format.

I understand that there are excel function formulas, but this becomes a large file and already has massive amounts of formulas (Vlookups) which is slowing down the workbook considerably, so I wish to have it hard coded via VBA.

I did find some coding which was somewhat useful, however, it only 'formats' selected cells and requires manual initiation to run the code (macro) but I would like it to automatically format the cells in the column as/after the user enters the data.

Sub DashesIn()
    DoDashes ("In")
End Sub


Private Sub DoDashes(What As String)
' from website: https://excelribbon.tips.net/T009394_Using_a_Custom_Format_to_Add_Dashes.html

' Puts dashes after every 2nd character to a total of 6 characters long
'OUTPUT IS IN THIS FORMAT: **-**-**

    Dim c As Range
    Dim J As Integer

    For Each c In Selection.Cells
If c.Value <> "" Then
J = InStr(c.Value, "-")
Select Case What
Case "Out"
While J > 0
c.Value = Left(c.Value, J - 1) & _
Mid(c.Value, J + 1, Len(c.Value))
J = InStr(c.Value, "-")
Wend
Case "In"
If J = 0 Then
c.Value = _
Left(c.Value, 2) & "-" & _
Mid(c.Value, 3, 2) & "-" & _
Right(c.Value, 2)
End If
End Select
End If
Next c
End Sub

I adapted the code from Using a Custom Format to Add Dashes

Any assistance would be immensely appreciated.

Kind regards,
TheShyButterfly

 

[Joe4]

Is it always going to be exactly a six character entry, and should the format always be: XX-XX-XX?

 

[Joe4]

If my assumptions above are correct, then right-click on the sheet tab name that appears at the bottom of the screen, select View Code, and paste this VBA code in the blank VBA code module that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any cells updated in column BA
    Set rng = Intersect(Target, Columns("BA:BA"))
    
'   Exit if no cells updated in column BA
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells just updated
    Application.EnableEvents = False
    For Each cell In rng
'       See if length of entry is not 6
        If Len(cell) <> 6 Then
            cell.ClearContents
            MsgBox "Entry in cell " & Target.Address & " must be exactly 6 characters", _
                vbOKOnly, "ENTRY ERROR!"
        Else
'           If entry is 6, then format entry
            cell = Left(cell, 2) & "-" & Mid(cell, 3, 2) & "-" & Right(cell, 2)
        End If
        Application.EnableEvents = True
    Next cell
    
End Sub

This is "event procedure" VBA code that when placed in the sheet module, will fire automatically as users make entries in column BA.