Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

I would like to add the following conditions to the code below. Value entered must…

  1. 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 If
    

    End Sub

  2. 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 Sub
    

    This 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.

  3. 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

  4. 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 Sub
    

    This 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.