Data Validation in Userform to not allow / \ : * ? " < > | for textbox

Question

I have a userform that upon opening the 'Master' file, it will rename the file once you fill out the userform with a name that comes from the Event name (Userform textbox). The issue that I will have is that if someone uses 1 of the 9 characters that you cannot use, I have an error handler that does not allow you to save.... I would rather them not be able to continue on the userform until they have correctly named their event.

Below is the button click for submitting the data. I think that the validation should be included with "Me.TextBoxE_EventName.Value"... any ideas of what I could put in there?

Private Sub CommandButton_ECancelREV_Click()

'----------------------------------------------------
'Check Validation of Completed form
'----------------------------------------------------
If Trim(Me.TextBoxE_RequestBy.Value) = "" Then
    Me.TextBoxE_RequestBy.SetFocus
    MsgBox "Please fill in 'Request By' before canceling form", vbCritical
        Exit Sub
End If

If Trim(Me.TextBoxE_OnSiteContact.Value) = "" Then
    Me.TextBoxE_OnSiteContact.SetFocus
    MsgBox "Please fill in 'On Site Contact' before canceling form", vbCritical
        Exit Sub
End If

If Trim(Me.TextBoxE_OnSiteNumber.Value) = "" Then
    Me.TextBoxE_OnSiteNumber.SetFocus
    MsgBox "Please fill in 'On Site Phone Number' before canceling form"
    Exit Sub
End If

If Trim(Me.TextBoxE_EventName.Value) = "" Then
    Me.TextBoxE_EventName.SetFocus
    MsgBox "Please fill in 'Event Name' before canceling form"
    Exit Sub
End If

If Trim(Me.ComboBoxE_LocationNumber.ListIndex) = -1 Then
  Me.ComboBoxE_LocationNumber.SetFocus
  MsgBox "Please fill in 'Location Number' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_OffSiteDelivery.ListIndex) = -1 Then
  Me.ListBoxE_OffSiteDelivery.SetFocus
  MsgBox "Please fill in 'Off Site Delivery?' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_RequestStatus.ListIndex) = -1 Then
  Me.ListBoxE_RequestStatus.SetFocus
  MsgBox "Please fill in 'Request Status' before canceling form"
  Exit Sub
End If

If Trim(Me.TextBoxE_DeliverDate.Value) = "" Then
  Me.TextBoxE_DeliverDate.SetFocus
  MsgBox "Please fill in 'Delivery Date' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_DeliverTime.ListIndex) = -1 Then
  Me.ListBoxE_DeliverTime.SetFocus
  MsgBox "Please fill in 'Delivery Time' before canceling form"
  Exit Sub
End If

If Trim(Me.TextBoxE_SSDate.Value) = "" Then
  Me.TextBoxE_SSDate.SetFocus
  MsgBox "Please fill in 'Show Start Date' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_SSTime.ListIndex) = -1 Then
  Me.ListBoxE_SSTime.SetFocus
  MsgBox "Please fill in 'Show Start Time' before canceling form"
  Exit Sub
End If

If Trim(Me.TextBoxE_SEDate.Value) = "" Then
  Me.TextBoxE_SEDate.SetFocus
  MsgBox "Please fill in 'Show End Date' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_SETime.ListIndex) = -1 Then
  Me.ListBoxE_SETime.SetFocus
  MsgBox "Please fill in 'Show End Time' before canceling form"
  Exit Sub
End If

If Trim(Me.TextBoxE_PickupDate.Value) = "" Then
  Me.TextBoxE_PickupDate.SetFocus
  MsgBox "Please fill in 'Pickup Date' before canceling form"
  Exit Sub
End If

If Trim(Me.ListBoxE_PickupTime.ListIndex) = -1 Then
  Me.ListBoxE_PickupTime.SetFocus
  MsgBox "Please fill in 'Pickup Time' before canceling form"
  Exit Sub
End If

    Me.Hide
    ThisWorkbook.Sheets("Equipment Request").Visible = True
    ThisWorkbook.Sheets("Equipment Request").Select

End Sub

Private Sub E_EnterInformation_Click()

'----------------------------------------------------
'Check Validation of Completed form
'----------------------------------------------------
If Trim(Me.TextBoxE_RequestBy.Value) = "" Then
    Me.TextBoxE_RequestBy.SetFocus
    MsgBox "Please fill in 'Request By' on form", vbCritical
        Exit Sub
End If

If Trim(Me.TextBoxE_OnSiteContact.Value) = "" Then
    Me.TextBoxE_OnSiteContact.SetFocus
    MsgBox "Please fill in 'On Site Contact' on form", vbCritical
        Exit Sub
End If

If Trim(Me.TextBoxE_OnSiteNumber.Value) = "" Then
    Me.TextBoxE_OnSiteNumber.SetFocus
    MsgBox "Please fill in 'On Site Phone Number' on form"
    Exit Sub
End If

If Trim(Me.TextBoxE_EventName.Value) = "" Then
    Me.TextBoxE_EventName.SetFocus
    MsgBox "Please fill in 'Event Name' on form"
    Exit Sub
End If

If Trim(Me.ComboBoxE_LocationNumber.ListIndex) = -1 Then
  Me.ComboBoxE_LocationNumber.SetFocus
  MsgBox "Please fill in 'Location Number' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_OffSiteDelivery.ListIndex) = -1 Then
  Me.ListBoxE_OffSiteDelivery.SetFocus
  MsgBox "Please fill in 'Off Site Delivery?' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_RequestStatus.ListIndex) = -1 Then
  Me.ListBoxE_RequestStatus.SetFocus
  MsgBox "Please fill in 'Request Status' on form"
  Exit Sub
End If

If Trim(Me.TextBoxE_DeliverDate.Value) = "" Then
  Me.TextBoxE_DeliverDate.SetFocus
  MsgBox "Please fill in 'Delivery Date' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_DeliverTime.ListIndex) = -1 Then
  Me.ListBoxE_DeliverTime.SetFocus
  MsgBox "Please fill in 'Delivery Time' on form"
  Exit Sub
End If

If Trim(Me.TextBoxE_SSDate.Value) = "" Then
  Me.TextBoxE_SSDate.SetFocus
  MsgBox "Please fill in 'Show Start Date' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_SSTime.ListIndex) = -1 Then
  Me.ListBoxE_SSTime.SetFocus
  MsgBox "Please fill in 'Show Start Time' on form"
  Exit Sub
End If

If Trim(Me.TextBoxE_SEDate.Value) = "" Then
  Me.TextBoxE_SEDate.SetFocus
  MsgBox "Please fill in 'Show End Date' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_SETime.ListIndex) = -1 Then
  Me.ListBoxE_SETime.SetFocus
  MsgBox "Please fill in 'Show End Time' on form"
  Exit Sub
End If

If Trim(Me.TextBoxE_PickupDate.Value) = "" Then
  Me.TextBoxE_PickupDate.SetFocus
  MsgBox "Please fill in 'Pickup Date' on form"
  Exit Sub
End If

If Trim(Me.ListBoxE_PickupTime.ListIndex) = -1 Then
  Me.ListBoxE_PickupTime.SetFocus
  MsgBox "Please fill in 'Pickup Time' on form"
  Exit Sub
End If

'Hide or show offsite and order number boxes

If Me.ListBoxE_OffSiteDelivery.Value = "Yes" Then
    Me.LabelE_OffSiteAdd.Visible = True
    Me.TextBoxE_OffSiteAdd.Visible = True
    Else
    EquipmentRequest.LabelE_OffSiteAdd.Visible = False
    EquipmentRequest.TextBoxE_OffSiteAdd.Visible = False
    End If

If Me.ListBoxE_OffSiteDelivery.Value = "Yes" And Me.TextBoxE_OffSiteAdd.Value = "" Then
    Me.TextBoxE_OffSiteAdd.SetFocus
    MsgBox "Please fill in 'Enter Off Site Location Name and Address' on form"
  Exit Sub
End If

If Me.ListBoxE_RequestStatus.Value <> "New" Then
    EquipmentRequest.LabelE_OrderNum.Visible = True
    EquipmentRequest.TextBoxE_OrderNum.Visible = True
    Else
    EquipmentRequest.LabelE_OrderNum.Visible = False
    EquipmentRequest.TextBoxE_OrderNum.Visible = False
    End If

If Me.ListBoxE_RequestStatus.Value <> "New" And Me.TextBoxE_OrderNum.Value = "" Then
    Me.TextBoxE_OrderNum.SetFocus
    MsgBox "Please fill in 'Enter Order/Job #' on form"
  Exit Sub
End If


'--------------------------------------------
'Enter Data in Form
'--------------------------------------------
Call UnProtectAllWorksheets

Sheets("Equipment Request").Range("C6") = Me.TextBoxE_RequestBy.Value       'Name of Requester
Sheets("Equipment Request").Range("C7") = Me.TextBoxE_OnSiteContact.Value   'Name of Contact
Sheets("Equipment Request").Range("C8") = Me.TextBoxE_OnSiteNumber.Value    'Phone Number format
Sheets("Equipment Request").Range("F11") = Me.TextBoxE_Comments.Value       'Comments (not required)

Sheets("Equipment Request").Range("I6") = Me.TextBoxE_EventName.Value       'Name of Event
Sheets("Equipment Request").Range("P24") = Me.ComboBoxE_LocationNumber.Value 'Location Number
Sheets("Equipment Request").Range("I8") = Me.ListBoxE_OffSiteDelivery.Value 'Yes or No Selection
Sheets("Equipment Request").Range("I9") = Me.ListBoxE_RequestStatus.Value   'New or revision or cancel selection

Sheets("Equipment Request").Range("C9") = Me.TextBoxE_PWDate.Value          'Short Date Format
Sheets("Equipment Request").Range("D9") = Me.ListBoxE_PWTime.Value          'Time Format
Sheets("Equipment Request").Range("C10") = Me.TextBoxE_DeliverDate.Value    'Short Date Format
Sheets("Equipment Request").Range("D10") = Me.ListBoxE_DeliverTime.Value    'Time Format
Sheets("Equipment Request").Range("C11") = Me.TextBoxE_SSDate.Value         'Short Date Format
Sheets("Equipment Request").Range("D11") = Me.ListBoxE_SSTime.Value         'Time Format
Sheets("Equipment Request").Range("C12") = Me.TextBoxE_SEDate.Value         'Short Date Format
Sheets("Equipment Request").Range("D12") = Me.ListBoxE_SETime.Value         'Time Format
Sheets("Equipment Request").Range("C13") = Me.TextBoxE_PickupDate.Value     'Short Date Format
Sheets("Equipment Request").Range("D13") = Me.ListBoxE_PickupTime.Value     'Time Format

Sheets("Equipment Request").Range("K8") = Me.TextBoxE_OffSiteAdd.Value     'Address of Offsite
Sheets("Equipment Request").Range("M9") = Me.TextBoxE_OrderNum.Value     'Order/Job # if revision
Sheets("Equipment Request").Range("D5") = Me.TextBoxE_CCEmails.Value

Call ProtectAllWorksheets

Me.Hide

Call ESaveBook

If Sheets("Equipment Request").Range("I9") <> "New" And Sheets("Equipment Request").Range("I9") <> "Dates Revision" And Sheets("Equipment Request").Range("I9") <> "Cancellation Revision" Then

ThisWorkbook.Sheets("Revised Equipment Request").Visible = True
ThisWorkbook.Sheets("Revised Equipment Request").Select

Else
ThisWorkbook.Sheets("Equipment Request").Visible = True
ThisWorkbook.Sheets("Equipment Request").Select
End If

End Sub

I think this should be a nested if statement, I just need some help on where and maybe a good way to do it. Any help would be appreciated.

Below is the "ESaveBook" macro that has the error handler:

Sub ESaveBook()
'----------------------------------------------------
'Save File to Hard Drive For First Time
'----------------------------------------------------

    'Call UnProtectAllWorksheets

    Application.DisplayAlerts = False

    Dim sFile As String
    Dim sPath As String
    Dim sPS As String

    sPS = Application.PathSeparator
    sPath = Environ("UserProfile") & sPS & "Documents" & sPS & "!ERF!" & sPS & Format(Sheets("Equipment Request").Range("C10").Value, "mm.dd.yy") & " - " & Format(Sheets("Equipment Request").Range("C13").Value, "mm.dd.yy") & " " & Sheets("Equipment Request").Range("I6").Value & sPS
    CreateDirectory sPath
    If Len(Dir(sPath, vbDirectory)) = 0 Then Exit Sub   'Couldn't create the path due to invalid or inaccessible location
    sFile = Sheets("Equipment Request").Range("I6").Value & " ERF SAVED " & " " & Format(Date, "mm.dd.yy") & " " & Sheets("Equipment Request").Range("I9").Value & ".xlsm"

    ActiveWorkbook.SaveAs Filename:=sPath & sFile, FileFormat:=52

    MsgBox ("This file has been saved at 'Documents\!ERF!\") & Format(Sheets("Equipment Request").Range("C10").Value, "mm.dd.yy") & " - " & Format(Sheets("Equipment Request").Range("C13").Value, "mm.dd.yy") & " " & Sheets("Equipment Request").Range("I6").Value & ("'.  The file name is '") & sFile & ("'.  Please do not move target location of file.")

    Application.DisplayAlerts = True

    'Call ProtectAllWorksheets

End Sub

Sub CreateDirectory(ByVal arg_sFolderpath As String)

    If Len(Dir(arg_sFolderpath, vbDirectory)) = 0 Then
        Dim sPS As String
        sPS = Application.PathSeparator

        Dim sBuildPath As String
        Dim vFolder As Variant
        For Each vFolder In Split(arg_sFolderpath, sPS)
            If Len(vFolder) > 0 Then
                If Len(sBuildPath) = 0 Then sBuildPath = vFolder Else sBuildPath = sBuildPath & sPS & vFolder
                If Len(Dir(sBuildPath, vbDirectory)) = 0 Then
                    On Error Resume Next
                    MkDir sBuildPath
                    On Error GoTo 0
                    If Len(Dir(sBuildPath, vbDirectory)) = 0 Then
                        MsgBox "[" & sBuildPath & "] is either invalid or unreachable.", , "Create Directory Error"
                        Exit Sub
                    End If
                End If
            End If
        Next vFolder
    End If

End Sub

Answer 1

TL;DR:

You need a function that is able to check whether a given string contains any one of several forbidden characters. One way to do this is to define a string that contains each of these characters once, then iterate each character in that string and verify whether the input contains that character - and bail out as soon as we know what the answer is:

Private Function IsValidPathPartString(ByVal value As String) As Boolean
'a string is valid if it contains no characters illegal in a path/file name
    Const illegalChars = "/\:*?""<>|"
    Dim i As Long
    For i = 1 To Len(illegalChars)
        If InStr(value, Mid$(illegalChars, i, 1)) > 0 Then
            Exit Function 'implicit: false
        End If
    Next
    IsValidPathPartString = True
End Function

---

Model-View-Presenter

Any non-trivial dialog without structure, quickly turns into a big pile of spaghetti mess - whether it's written by VBA rookies with MSForms, or by C# professionals with WinForms or WPF (modern UI frameworks). The problem is inherent to the very nature of UI programming, and VBA prorgammers unfamiliar with Object-Oriented Programming are the most vulnerable to the pitfalls of "Smart UI", where the form is running the show and everything that ever needs to happen, happens through the form: it's never too late to unlearn this.

The form's job is to collect user inputs and present data. Period, end of it. Where the data it's presenting is coming from, is none of its business. What happens with the data it collected after it's collected, it none of its business either.

"Smart UI" isn't the only way to go about coding UI. When things get anywhere beyond trivial - and your form is definitely in that non-trivial category, we need much more robust structure in place, lest things quickly spiral out of control.

Behold, the Model-View-Presenter UI pattern, which plays very nicely with MSForms (and WinForms, if you dive into .NET-land).

Model

Have a "model" class, whose responsibility is to define the data the form will be manipulating. This model class can also be responsible for knowing whether or not the data it's encapsulating is valid; in more elaborate scenarios you could splice validation into its own set of objets, but let's keep things simple. The class might look something like this (note the dedicated function validating whether a string contains characters illegal in path/file names):

'EquipmentRequestModel.cls
Option Explicit
Private ValidationErrors As Collection

Public RequestedBy As String
Public OnSiteContact As String
Public OnSiteNumber As String
Public EventName As String
Public LocationNumber As String
Public OffSiteDelivery As String
'...

Public Property Get IsValid() As Boolean
    Validate
    IsValid = ValidationErrors.Count = 0
End Property

Public Property Get ModelValidationErrors As Variant
    If ValidationErrors.Count = 0 Then Exit Property 'implicit vbEmpty
    ReDim errors(0 To ValidationErrors.Count - 1)
    Dim e As Long
    For e = 0 To ValidationErrors.Count - 1
        errors(e) = ValidationErrors(e + 1) 'collection indexing is 1-based
    Next
    ModelValidationErrors = errors
End Property

Private Sub Validate()
    Set ValidationErrors = New Collection
    If Not IsValidRequiredString(RequestedBy) Then OnMissingRequiredFieldError "RequestedBy"
    If Not IsValidRequiredString(OnSiteContact) Then OnMissingRequiredFieldError "OnSiteContact"
    If Not IsValidRequiredString(OnSiteNumber) Then OnMissingRequiredFieldError "OnSiteNumber"
    If Not IsValidRequiredString(EventName) Then OnMissingRequiredFieldError "EventName"
    If Not IsValidPathPartString(EventName) Then OnValidationError "Field [EventName] cannot contain characters: [/\:*?""<>|]."
    '...
End Sub

Private Function IsValidRequiredString(ByVal value As String) As Boolean
'a required string is valid if it's non-empty after stripping leading/trailing spaces
    IsValidRequiredString = Trim(value) <> vbNullString
End Function

Private Function IsValidPathPartString(ByVal value As String) As Boolean
'a string is valid if it contains no characters illegal in a path/file name
    Const illegalChars = "/\:*?""<>|"
    Dim i As Long
    For i = 1 To Len(illegalChars)
        If InStr(value, Mid$(illegalChars, i, 1)) > 0 Then
            Exit Function 'implicit: false
        End If
    Next
    IsValidPathPartString = True
End Function

Private Sub OnMissingRequiredFieldError(ByVal propertyName As String)
    OnValidationError "Required field [" & propertyName & "] is empty."
End Sub

Private Sub OnValidationError(ByVal message As String)
    ValidationErrors.Add message
End Sub

More complex validations conditionally dependent on the value of such or such property can easily be implemented there, and if the view needs extra metadata to control whether such or such field needs to be visible, the model can expose Boolean properties for that, too.

Now you can have code that reads the worksheet and populates the properties of an instance of this class with cell values, or you can have code that reads the class properties and populates worksheet cells with them - such code doensn't belong in the model though.

But how does such a class affect the form's code-behind?

View

The form needs a reference to the model, early on. What's nice about MSForms is that you get a VB_PredeclaredId attribute for free (it's easily abused, but that's another discussion), so it's very easy to add a Create factory method that does exactly that:

'EquipmentRequestView.frm
Option Explicit
Private model As EquipmentRequestModel

Public Property Get EquipmentRequestModel() As EquipmentRequestModel
    Set EquipmentRequestModel = model
End Property

Public Property Set EquipmentRequestModel(ByVal value As EquipmentRequestModel)
    Set model = value
    LoadModelData
End Property

Public Function Create(ByVal viewModel As EquipmentRequestModel) As EquipmentRequestView
    Dim result As EquipmentRequestView
    Set result = New EquipmentRequestView
    Set result.EquipmentRequestModel = viewModel
    Set create = result
End Function

Private Sub LoadModelData()
'synchronize control values as per model
    Me.TextBoxE_RequestBy.Value = model.RequestBy
    Me.TextBoxE_OnSiteContact.Value = model.OnSiteContact
    '...
    ValidateForm
End Sub

The form contains a number of controls, and these controls can all respond to events. So what we do is, we handle these controls' Change event, and update the model accordingly:

Private Sub TextBoxE_RequestBy_Change()
    model.RequestBy = Me.TextBoxE_RequestBy.Value
    ValidateForm
End Sub

Private Sub TextBoxE_OnSiteContact_Change()
    model.OnSiteContact = Me.TextBoxE_OnSiteContact.Value
    ValidateForm
End Sub

'...

The ValidateForm procedure being invoked from every control's respective handler, every user input causes the model to be validated:

Private Sub ValidateForm()
    Dim isValidForm As Boolean
    isValidForm = model.IsValid

    'command buttons are only enabled if form is valid
    Me.E_EnterInformation.Enabled = isValidForm
    Me.CommandButton_ECancelREV.Enabled =isValidForm

    'validation errors label is only visible with invalid data
    Me.ValidationErrorsLabel.Visible = Not isValidForm
    Me.ValidationErrorsLabel.Caption = Join(model.ModelValidationErrors, vbNewLine)
End Sub

You could have finer-grained validation error metadata with a more thought-out data validation mechanism, too. For example instead of just plain strings, a model validation error could be an object in its own right, with ErrorMessage, ViewControlName, and ModelPropertyName properties that make it easier to attach a specific validation error to a specific control on the form, for example if you wanted to red-highlight the field in question, put it into focus, or toggle the visibility of a cute little red "X" icon with the vaildation message in the icon's ControlToolTip property - sky's the limit here.

As far as form/view responsibilities go, that's all of it. This would be the Click handler for your E_EnterInformation button:

Private Sub E_EnterInformation_Click()
    Me.Hide
End Sub

The only thing that's missing, is handling the QueryClose event, so that we can track that the user means to just bail out of the form and pretend they never wanted to bring it up in the first place.

So how does the form data end up on the worksheet then?

Presenter

Another class needs to be responsible for connecting the dots: something somewhere needs to create the model, initialize it (if needed), create the form / pass it the model, and then show the form and determine what to do with the now-valid model data.

'EquipmentRequestPresenter.cls
Option Explicit

Public Sub Run()
    Dim model As EquipmentRequestModel
    Set model = InitializeModel
    With EquipmentRequestView.Create(model)
        .Show
        'todo: handle a user-cancelled form?
        UpdateWorksheet model
    End With
End Sub

Private Function InitializeModel() As EquipmentRequestModel
    Dim model As EquipmentRequestModel
    Set model = New EquipmentRequestModel
    'note: should probably be "With EquipmentRequestSheet"
    With ActiveWorkbook.Worksheets("Equipment Request")
        model.RequestBy = .Range("C6").Value 'todo: name these ranges...
        model.OnSiteContact = .Range("C7").Value '...urgently...
        model.OnSiteNumber = .Range("C8").Value '...before someone inserts a row/column
        '...
    End With
    Set InitializeModel = model
End Function

Private Sub UpdateWorksheet(ByVal model As EquipmentRequestModel)
    'note: should probably be "With EquipmentRequestSheet"
    With ActiveWorkbook.Worksheets("Equipment Request")
        .Unprotect
        .Range("C6").Value = model.RequestBy
        .Range("C7").Value = model.OnSiteContact
        .Range("C8").Value = model.OnSiteNumber
        '...
        .Protect
    End With
End Sub

Note that this means the ESaveBook procedure can also take a model parameter now, and use model.DeliveryDate instead of Range("C10") - making it one less place to worry about when the worksheet template needs to change and a row is added at the top, or a column offsets all these cell coordinates and royally messes up everything. Going with .Range("DeliveryDate") would already protect your code against that: a named range becomes a layer of abstraction between your code and the actual worksheet cells, such that the actual coordinates are abstracted away from the code, which starts no longer needing all kinds of "what's this cell for again?" comments... that may or may not be accurate.

Also note, PascalCase casing in module and procedure plain-English, pronounceable names, no underscores anywhere, no funky prefixes. Not sure what's the deal with the E everywhere.

Anyway so with that setup, the macro that's currently calling .Show on that form, should now look like this:

Public Sub MyMacro()
    With New EquipmentRequestPresenter
       .Run
    End With
End Sub

Final note: all of the above is air-code provided for illustrating concepts; none of it was tested in any way.

Answer 2

While @MathieuGuindon has provided one long-term strategy, I have an alternative approach for the validation.

Basically, rather than validate after the fact, prevent the user from making the mistake in the first place - "an ounce of prevention is usually better than a pound of cure".

Within your form, you can access many event handlers. A useful one is KeyPress. For a very simple and messy example:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 '/ \ : * ? " < > | 
    If KeyAscii = Asc("/") or KeyAscii = Asc("\") Then
        KeyAscii = 0 ' This tells the form to ignore that input
    End If
End Sub

Of course, we can make this smarter:

Private Function IsBadCharacter(keyToCheck as MSForms.ReturnInteger) as Boolean
     '/ \ : * ? " < > |
    Select Case keyToCheck
        Case Asc("/"), Asc("\"), Asc(":"), Asc("*"), Asc("?"), Asc(""""), Asc("<"), Asc(">"), Asc("|")
            IsBadCharacter = True
        Case Else
            IsBadCharacter = False
    End Select
End Function

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 '/ \ : * ? " < > | 
    If IsBadCharacter(KeyAscii) Then
        KeyAscii = 0 ' This tells the form to ignore that input
    End If
End Sub

An alternative approach here is just to return 0 or correct KeyAscii value from IsBadCharacter, but the Boolean approach allows you to implement other error handling methods if you so desire.

This means that some of your data validation checking is in the View and any similar checking you have in the Model is redundant in this example (but would still be required if you were to re-use the pattern). However, a simpler user experience is always good!

Answer 3

the INSTR function should be useful for you along with SELECT CASE TRUE

 SELECT CASE True
      CASE InStr(1,yourStringtoSearchIn, StringYouwantTofind)>0
 END SELECT

tested with

Private Sub dero()
    Dim this$, that$
    this = "der|p"
    that = "|"
     Select Case True
          Case InStr(1, this, that) > 0
        Debug.Print ; "foudn it"
     End Select
End Sub