I would like to add the following conditions to the code below. Value entered must…
- User
I would like to add the following conditions to the code below. Value entered must always be exactly three letters. If the value is less than three, a pop up warns that value must be three letters without numbers or symbols, and if the value is less than three letters the cell is cleared. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range
’ Specify the range of column B where the data validation is applied Set rng = Range(“B2:B” & Me.Cells(Me.Rows.Count, “B”).End(xlUp).Row) ’ Check if the changes were made in the target range If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False ’ Disable events to prevent recursive triggering For Each cell In Intersect(Target, rng) ’ Convert entered value to uppercase cell.Value = UCase(cell.Value) ’ Remove numbers and symbols from the value cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “0”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “1”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “2”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “3”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “4”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “5”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “6”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “7”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “8”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “9”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “!”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “@”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “#”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “$”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “%”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “^”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “&”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "“, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “(”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “)”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “-”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “_”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “=”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “+”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “[”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “{”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “]”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “}”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “|”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “;”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “:”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “'”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “””", “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “,”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “<”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “.”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “>”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “/”, “”) cell.Value = Application.WorksheetFunction.Substitute(cell.Value, “?”, “”) ’ Check if the modified value already exists in column B If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then cell.ClearContents MsgBox “Duplicates are not allowed in column B.”, vbCritical, “Duplicate Value” cell.Select End If Next cell Application.EnableEvents = True ’ Re-enable events End IfEnd Sub
- Assistant
To add the conditions that the value entered must always be exactly three letters, you can modify the existing code as follows:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range ' Specify the range of column B where the data validation is applied Set rng = Range("B2:B" & Me.Cells(Me.Rows.Count, "B").End(xlUp).Row) ' Check if the changes were made in the target range If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False ' Disable events to prevent recursive triggering For Each cell In Intersect(Target, rng) ' Convert entered value to uppercase cell.Value = UCase(cell.Value) ' Remove numbers and symbols from the value cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "0", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "1", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "2", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "3", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "4", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "5", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "6", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "7", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "8", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "9", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "!", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "@", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "#", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "$", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "%", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "^", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "&", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, """", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "(", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ")", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "-", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "_", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "=", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "+", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "[", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "{", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "]", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "}", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "\", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "|", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ";", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ":", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "'", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, """", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ",", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "<", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ".", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, ">", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "/", "") cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "?", "") ' Check if the modified value already exists in column B If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then cell.ClearContents MsgBox "Duplicates are not allowed in column B.", vbCritical, "Duplicate Value" cell.Select End If ' Check if the modified value has exactly three letters If Len(cell.Value) <> 3 Then MsgBox "Value must have exactly three letters without numbers or symbols.", vbExclamation, "Invalid Value" cell.ClearContents End If Next cell Application.EnableEvents = True ' Re-enable events End If End SubThis modified code will display a message box warning if the value entered has less than three letters or contains numbers/symbols, and it will clear the cell in that case.
- User
The code bellow works as expected. I would like to add another condition to it. Before the new sheet is created, I would like a new Folder created in 'G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS' with the Name of the Target.Value
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False Dim contractorName As String Dim newSheet As Worksheet ' Check if the double-clicked cell is in column C If Target.Column = 3 Then contractorName = Target.Value ' Check if a contractor name is entered If contractorName = "" Then MsgBox "To create a New Sheet, Contractor NAME has to be entered" Exit Sub End If If Target.Offset(0, 1).Value = "" Then MsgBox "To create a New Sheet, Contractor CODE has to be entered" Exit Sub End If If Target.Offset(0, 6).Value = "CREATED" Then MsgBox "Contractor Sheet has already been created" Exit Sub End If If contractorName <> "" And Target.Offset(0, 1).Value <> "" Then Target.Offset(0, 6).Value = "CREATED" ' Duplicate NEWcont sheet ThisWorkbook.Sheets("NEWcont").Copy After:=Sheets(Sheets.count) ' Rename the duplicated sheet to the value found in column D Set newSheet = ActiveSheet newSheet.Name = Target.Offset(0, 1).Value End If End If Application.EnableEvents = True ActiveSheet.Calculate End Sub
- Assistant
To add the condition of creating a new folder in 'G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS' with the name of the Target.Value before the new sheet is created, you can modify the existing code as follows:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EnableEvents = False Dim contractorName As String Dim newSheet As Worksheet ' Check if the double-clicked cell is in column C If Target.Column = 3 Then contractorName = Target.Value ' Check if a contractor name is entered If contractorName = "" Then MsgBox "To create a New Sheet, Contractor NAME has to be entered" Exit Sub End If If Target.Offset(0, 1).Value = "" Then MsgBox "To create a New Sheet, Contractor CODE has to be entered" Exit Sub End If If Target.Offset(0, 6).Value = "CREATED" Then MsgBox "Contractor Sheet has already been created" Exit Sub End If If contractorName <> "" And Target.Offset(0, 1).Value <> "" Then Target.Offset(0, 6).Value = "CREATED" ' Create a new folder with the contractor name in the specified directory Dim path As String path = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\" & contractorName If Dir(path, vbDirectory) = "" Then MkDir path Else MsgBox "Folder already exists for this contractor", vbInformation, "Folder Exists" End If ' Duplicate NEWcont sheet ThisWorkbook.Sheets("NEWcont").Copy After:=Sheets(Sheets.Count) ' Rename the duplicated sheet to the value found in column D Set newSheet = ActiveSheet newSheet.Name = Target.Offset(0, 1).Value End If End If Application.EnableEvents = True ActiveSheet.Calculate End SubThis modified code will check if a folder with the contractor name already exists in the specified directory. If it doesn't exist, it will create a new folder with the contractor name before duplicating the "NEWcont" sheet and renaming it to the value found in column D. If a folder already exists for the contractor, it will display a message box informing the user.