Replacing invalid characters that have been entered into a cell

[DrummerAndy68]

Hello,

I have created a spreadsheet for my team to keep track of batch programs that are being ran. There are 100 cells (C2:C101) on Sheet1 where they can enter their batches. Multiple batches look something like this:
BI-EBTDM-DLY, BI-SNDDM-DLY or IN-PRSDX-MLY, IN-RCSDX-MLY, IN-MEDF-DLY

There are specific rules I want the team to follow, but sometimes they don't, so I would like to use VBA to correct the most common mistakes which include:

• Not putting a space after a comma (BI-EBTDM-DLY,BI-SNDDM-DLY)
• Putting a space before a comma (BI-EBTDM-DLY , BI-SNDDM-DLY)
• Putting a space before and not after a comma (BI-EBTDM-DLY ,BI-SNDDM-DLY)
• Using underscores instead of hyphens (BI-EBTDM_DLY, BI_SNDDM-DLY)
• Putting a space at the beginning ( BI-EBTDM-DLY, BI-SNDDM-DLY)
• Putting a space at the end (BI-EBTDM-DLY, BI-SNDDM-DLY )
• Using the word "and" (IN-PRSDX-MLY, IN-RCSDX-MLY, and IN-MEDF-DLY)

Unfortunately, there is an unlimited number of batch combinations that people can enter and new ones being created all of the time so I am not able to create a drop list to force the issue.

I know how to use Replace and Trim to make the corrections. However, the main thing I need help with is, as soon as they finish typing or copying in their batches into a cell then leave the cell, I want to evaluate what they just entered and update that specific cell if they made any of the previously listed mistakes.

Batch Request Queue - Andy.xlsm
	A	B	C	D
1	NUM	TESTER	BATCHES	ASOF DATE
2	1	Revathi	BI-EBTDM-DLY , BI-SNDDM-DLY	10/03/2022
3	2	Andy	MU-BEDMQ-DLY, MU-BEDBC-DLY	10/31/2022
4	3	Patty	IN-PRSDX-MLY ,IN-RCSDX-MLY,IN-MEDF-DLY	01/01/2023
INT04

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Expression	=AND($F2<>"",$K1<>"",$B2<>"",$K2="")	text	NO
A2:L2	Expression	=AND($H$2<>"",$J$2="")	text	NO
A2:L101	Expression	=$K2<>""	text	NO
B3:L101	Expression	=AND($F3<>"",$K2<>"",$B3<>"",$K3="")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	List	=Testers

 

[Micron]

If there is a non-vba solution for this, it will certainly escape me. I might approach this as a one-time fix unless, as you seem to be saying, you have that covered. After that, if the input doesn't pass the same tests, cancel the input and maybe even present some sort of message appropriate to the test that failed. You should be able to use the Target property in the Worksheet Change event to see if the changes to the sheet fall into the range that these values will be entered. I'd try using a Select Case block to test for True (because it's the only way I know of to perform multiple tests such as in your case) and evaluate each of your conditions. If any of them return True, cancel the input and msgbox the unrepentant user.

 

[DrummerAndy68]

I am limited in my knowledge, but I did create this simple vba solution that "works", but requires the user to click a button to execute the macro each time (which I don't think people will do) and checks all 100 cells each time. (I have a separate macro that resets and clears out the sheet once it's full.)

Sub Fix_Batches()
    Dim myDataRng As Range
    Dim cell As Range

Application.ScreenUpdating = False
    Set myDataRng = Range("C2:C101")
    For Each cell In myDataRng
        If InStr(1, cell.Value, ", and ") > 0 Then
            cell.Value = Replace(cell.Value, ", and ", ", ")
        End If
        If InStr(1, cell.Value, " and ") > 0 Then
            cell.Value = Replace(cell.Value, " and ", ", ")
        End If
        If InStr(1, cell.Value, "_") > 0 Then
            cell.Value = Replace(cell.Value, "_", "-")
        End If
        If InStr(1, cell.Value, " ,") > 0 Then
            cell.Value = Replace(cell.Value, " ,", ",")
        End If
        If InStr(1, cell.Value, ",") > 0 Then
            cell.Value = Replace(cell.Value, ",", ", ")
        End If
        cell.Value = WorksheetFunction.Trim(cell)
    Next cell
Application.ScreenUpdating = True
End Sub

But, if I try to use this code in any way using worksheet change, it creates an endless loop of updating the entire range.
I was hoping there is a way to identify the cell that was just updated and make those corrections ONLY to that cell once they change it.

 

[Micron]

AFAIK, you turn off application updating to prevent such loops. Knowing the range that was updated comes from the Target property.
maybe better than a bunch of If's:

For Each cell In myDataRng
   Select Case True
      Case InStr(1, cell.Value, ", and ") > 0
         cell.Value = Replace(cell.Value, ", and ", ", ")
      Case InStr(1, cell.Value, " and ") > 0
         cell.Value = Replace(cell.Value, " and ", ", ")
      Case ...
    End Select
Next