User Forms, Data Validation And Error Handling - A Bit Of Help!

[jonsharman]

Hi,

I currently have a workbook that is used to derive the value of a vehicle based on a certain set of criteria. It was planned that the cells on the workbook would be updated by the user on the basis that only the cells that required an input were unlocked for editing and that where a certain input was required (for example year of manufacture, plate of car etc) this was set up using data validation and named lists (=YEAR, =PLATE, =VARIABLE and so on). I then added a TAB order macro to the workbook so the user always started of a cell E2 and on pressing of the TAB key took them to the next cell they needed to input information into.

It has since come to pass that some users still struggle to fill out all the required fields and get confused when they input the wrong information into a cell causing the calculation to fail. I have decided to create a UserForm to help with data entry and therefore if a user wants to use the 'easy' mode they click a button on the workbook and the UserForm opens and prompts them for data input. Once they submit the form it copies the entered data to the correct cells on the worksheet and the caculation occurs.

Using the UserForm I have set up some text boxes as free entry, some requiring the input from a list box with and some that are limited to a certain number of digits or characters. However this is about where my Excel knowledge falls away!

If the user does not fill in a mandatory field or indeed types a value into a list box that is not in the predefined named ranges when the submit button is clicked Excel generates an error and goes back to the workbook and thus the calculation fails. What I would like to do is that when the Submit button is pressed the macro checks that all mandatory fields are filled in and in the allowable range; if something is missing it prompts the user to go back to the UserForm and either fill it in or correct the incorrect information.

Can anyone offer any guidance on how to do this? Below is the code for the UserForm I am using and I have attached a screen shot of the form so hopefully it makes a bit more sense!

Many many thanks in advance ...

Jon

Private Sub SubmitButton_Click()
Dim CD As String        'Free Text Value                                        (MANDATORY)
Dim CPr As Currency     'Price Without £ Marker EG 7995 (No Text Allowed)       (MANDATORY)
Dim CMo As String       'Free Text Value                                        (MANDATORY)
Dim CY As Long          'List Box Entry (Name Defined In Workbook As =YEAR)     (MANDATORY)
Dim CPl As String       'List Box Entry (Name Defined In Workbook As =PLATE)    (MANDATORY)
Dim CMi As Long         'Number Entry With No Decimal Points EG '39542'         (MANDATORY)
Dim CS As String        'Free Text Entry (MANDATORY)
Dim CMA As Currency     'List Box Entry (Name Defined In Wookbook As =VARIABLE) (OPTIONAL)
Dim CCA As Currency     'List Box Entry (Name Defined In Wookbook As =VARIABLE) (OPTIONAL)
Dim CSA As Currency     'List Box Entry (Name Defined In Wookbook As =VARIABLE) (OPTIONAL)
Dim OY As Long          'List Box Entry (Name Defined In Workbook As =YEAR)     (MANDATORY)
Dim OPl As String       'List Box Entry (Name Defined In Wookbook As =PLATE)    (MANDATORY)
Dim OMi As Long         'Number Entry With No Decimal Points EG '39542'         (MANDATORY)
Dim OS As String        'Free Text Entry                                        (MANDATORY)
Dim STK As String       'Stock Number Entry That Must Being With A 'U' And No More Than 6 Digits Long EG 'U12345' (OPTIONAL)
Dim REG As String       'Free Text Entry For Max 9 Characters EG 'NU61 REG'     (OPTIONAL)
    'Copy VehicleInput User Form Data To DIM
    
    CD = CompDealer.Value
    CPr = CompPrice.Value
    CMo = CompVehicle.Value
    CY = CompYear.Value
    CPl = CompPlate.Value
    CMi = CompMiles.Value
    CS = CompSpec.Value
    CMA = CompModelAdjust.Value
    CCA = CompColourAdjust.Value
    CSA = CompSpecAdjust.Value
    OY = OurYear.Value
    OPl = OurPlate.Value
    OMi = OurMiles.Value
    OS = OurSpec.Value
    STK = StockNumber.Value
    REG = RegNumber.Value
    
    'Write Data To UCM Pricing Sheet
    
    Application.ScreenUpdating = False
    Sheets("UCMPRICING").Range("C13") = CD
    Sheets("UCMPRICING").Range("C15") = CPr
    Sheets("UCMPRICING").Range("C17") = CMo
    Sheets("UCMPRICING").Range("C19") = CY
    Sheets("UCMPRICING").Range("C21") = CPl
    Sheets("UCMPRICING").Range("C23") = CMi
    Sheets("UCMPRICING").Range("C25") = CS
    Sheets("UCMPRICING").Range("C43") = CMA
    Sheets("UCMPRICING").Range("C51") = CCA
    Sheets("UCMPRICING").Range("C53") = CSA
    Sheets("UCMPRICING").Range("J19") = OY
    Sheets("UCMPRICING").Range("J21") = OPl
    Sheets("UCMPRICING").Range("J23") = OMi
    Sheets("UCMPRICING").Range("J25") = OS
        
    'Return To UCM Pricing Workbook
    
    Unload Me
End Sub
Private Sub Cancel_Click()
    'Cancel VehicleInput User Form & Maximise Window
    
    Application.WindowState = xlMaximized
    Unload Me
    
End Sub
Private Sub ClearButton_Click()
    'Reset VehicleInput User Form
    
    Call UserForm_Activate
End Sub
Private Sub UserForm_Activate()
    'Clear VehicleInput User Form Ready For Input
    
    CompDealer.Value = ""
    CompPrice.Value = ""
    CompVehicle.Value = ""
    CompYear.Value = ""
    CompPlate.Value = ""
    CompMiles.Value = ""
    CompSpec.Value = ""
    CompModelAdjust.Value = ""
    CompColourAdjust.Value = ""
    CompSpecAdjust.Value = ""
    OurYear.Value = ""
    OurPlate.Value = ""
    OurMiles.Value = ""
    OurSpec.Value = ""
    StockNumber.Value = ""
    RegNumber.Value = ""
    
    'Clear UCM Pricing Sheet Boxes Ready To Accept Input
    
    Application.ScreenUpdating = False
    Sheets("UCMPRICING").Activate
    Range("C13").Select
    Selection.ClearContents
    Range("C15").Select
    Selection.ClearContents
    Range("C17").Select
    Selection.ClearContents
    Range("C19").Select
    Selection.ClearContents
    Range("C21").Select
    Selection.ClearContents
    Range("C23").Select
    Selection.ClearContents
    Range("C25").Select
    Selection.ClearContents
    Range("J19").Select
    Selection.ClearContents
    Range("J21").Select
    Selection.ClearContents
    Range("J23").Select
    Selection.ClearContents
    Range("J25").Select
    Selection.ClearContents
    Range("C43").Select
    Selection.ClearContents
    Range("C51").Select
    Selection.ClearContents
    Range("C53").Select
    Selection.ClearContents
    Range("K2").Select
    Selection.ClearContents
    
    'Return User To Starting Point
    
    Range("E2").Select
    Selection.ClearContents
    Application.ScreenUpdating = True
    
End Sub

Private Sub UserForm_Initialize()
    'Minimize Excel On VehicleInput User Form Run
    
    Application.WindowState = xlMinimized
    
End Sub

 

[dmt32]

I was reading this noting that you asked for bit of help when turns out you probably need rather more than a bit.</SPAN>

To do what you want, you need to validate each textbox for correct data type entry & if error found report it to user & exit procedure.</SPAN>

I have had a play & made changes to your code but unable to test so have no idea if what I have will work for you – so must see this more as a development idea for you to work with rather than a working solution.</SPAN>

Because you have so many user input fields, I dumped all the named variables in favour of couple of simple arrays. Also, needed a function to test for valid stock number input using alpha numeric format with “A” or “U” + numbers which I have provided but it's not an original idea & does have some limitations.</SPAN>

You can set user input text length on each textbox at design time in the textbox properties.</SPAN>

As usual, ensure you make a backup copy before testing new code.</SPAN>

Place all code below behind your userform:</SPAN>

</SPAN>
Dim sControl As Variant
Dim RangeArr As Variant
Private Sub SubmitButton_Click()
    Dim Data() As Variant
    Dim i As Integer
    'size data array
    ReDim Data(UBound(sControl))
    'validate user input
    'note array index starts at 0
    'you will need to check I have the indexing correct
    'for each control data type
    For i = LBound(sControl) To UBound(sControl)
        Select Case i
        Case 1, 7, 8, 9
            'currency values
            If Not IsNumeric(Me.Controls(sControl(i)).Value) Then
                MsgBox "Numeric Value Only Must Be Entered", 16, "Error"
                Me.Controls(sControl(i)).SetFocus
                Exit Sub
            Else
                Data(i) = CCur(Me.Controls(sControl(i)).Value)
            End If
        Case 3, 5, 10, 12
            'long values
            If Not IsNumeric(Me.Controls(sControl(i)).Value) Then
                MsgBox "Numeric Value Only Must Be Entered", 16, "Error"
                Me.Controls(sControl(i)).SetFocus
                Exit Sub
            Else
                Data(i) = CLng(Me.Controls(sControl(i)).Value)
            End If
        Case 6
            'mandatory
            If Len(Me.Controls(sControl(i)).Value) = 0 Then
                MsgBox "Value Must Be Entered", 16, "Error"
                Me.Controls(sControl(i)).SetFocus
                Exit Sub
            Else
                Data(i) = CStr(Me.Controls(sControl(i)).Value)
            End If
        Case 14
            'Alpha Numeric stock no entry
            'confirm prefix A or U
            If AlphaNumeric(UCase(Me.Controls(sControl(i)).Value)) Then
                'ok
                Data(i) = CVar(UCase(Me.Controls(sControl(i).Value)))
            Else
                MsgBox "Stock Number Must Be Prefixed With A or U" & Chr(10) & _
                       "Follow by x Digit Number", 16, "Error"
                Me.Controls(sControl(i)).SetFocus
                Exit Sub
            End If
        Case Else
            Data(i) = CStr(Me.Controls(sControl(i)).Value)
        End Select
    Next i
    'Write Data To UCM Pricing Sheet
    Application.ScreenUpdating = False
    For i = LBound(Data) To UBound(Data)
        Sheets("UCMPRICING").Range(RangeArr(i)).Value = Data(i)
        'clear form entry
        Me.Controls(sControl(i)).Value = ""
    Next i
    Application.ScreenUpdating = True
    MsgBox "Data Added Successfully!", 48, "Add Data"
    'Return To UCM Pricing Workbook
    Unload Me
End Sub


Private Sub Cancel_Click()
'Cancel VehicleInput User Form & Maximise Window
    Application.WindowState = xlMaximized
    Unload Me
End Sub

Private Sub ClearButton_Click()
'Reset VehicleInput User Form
    Call UserForm_Activate
End Sub


Private Sub UserForm_Activate()
    Dim i As Integer
    'Clear UCM Pricing Sheet Boxes Ready To Accept Input
    'Clear VehicleInput User Form Ready For Input
    Application.ScreenUpdating = False
    For i = LBound(sControl) To UBound(sControl)
        Sheets("UCMPRICING").Range(RangeArr(i)).Value = ""
        'clear form entry
        Me.Controls(sControl(i)).Value = ""
    Next i
    Application.ScreenUpdating = True
End Sub


Private Sub UserForm_Initialize()
'intialize arrays
'control names
    sControl = Array("CompDealer", _
                     "CompPrice", _
                     "CompVehicle", _
                     "CompYear", _
                     "CompPlate", _
                     "CompMiles", _
                     "CompSpec", _
                     "CompModelAdjust", _
                     "CompColourAdjust", _
                     "CompSpecAdjust", _
                     "OurYear", _
                     "OurPlate", _
                     "OurMiles", _
                     "OurSpec", _
                     "StockNumber", _
                     "RegNumber")

    'both arrays must be same size
    'I added last two ranges (J27, J29)
    ''you will need to adjust as required
    RangeArr = Array("C13", "C15", "C17", "C19", "C21", "C23", "C25", _
                     "C43", "C51", "C53", "J19", "J21", "J23", "J25", "J27", "J29")
    'Minimize Excel On VehicleInput User Form Run
    Application.WindowState = xlMinimized
End Sub

</SPAN>

Place Function code in standard module</SPAN>

</SPAN>
Function AlphaNumeric(ByVal CheckInput As Variant) As Boolean
    Dim sPos As Integer
    Dim sChr As String
    Dim ValidInput As String
    'Start at first character in CheckInput
    sPos = 1
    'values allowed
    ValidInput = "AU0123456789"
    'Test each character in CheckInput
    While sPos <= Len(CheckInput)
        'Single character in CheckInput
        sChr = Mid(CheckInput, sPos, 1)
        'If character is not in validinput, return FALSE
        If InStr(ValidInput, sChr) = 0 Then
            AlphaNumeric = False
            Exit Function
        End If
        'Increment counter
        sPos = sPos + 1
    Wend
    'return TRUE
    AlphaNumeric = True
End Function

</SPAN>

As stated, cannot confirm if any of this will work but hopefully, will give you something to go on.</SPAN>


Dave</SPAN>

 

[jonsharman]

Dave

Firstly thanks for your help with this; has taken me right back to A-Level IT all those years ago - perhaps if I had listened more in class I would not be working in the automotive retail industry!

The code has worked pretty much from the off but I have had to make a couple of small changes:

Under UserForm_Activate() I have added some code to set CompModelAdjust, CompColourAdjust and CompSpecAdjust to '0' after the UserForm has been cleared. It is rare the user will need to change these figures and this stops an error being returned if they do not enter a number at all:

Private Sub UserForm_Activate()
  
    Dim i As Integer
    'Clear UCM Pricing Sheet Boxes Ready To Accept Input
    'Clear VehicleInput User Form Ready For Input
    Application.ScreenUpdating = False
    For i = LBound(sControl) To UBound(sControl)
        Sheets("UCMPRICING").Range(RangeArr(i)).Value = ""
        'clear form entry
        Me.Controls(sControl(i)).Value = ""
    Next i
    CompModelAdjust.Value = "0"
    CompColourAdjust.Value = "0"
    CompSpecAdjust.Value = "0"
    Application.ScreenUpdating = True
End Sub

If I may trouble you for a bit of clarification - when you refer to the 'indexing' am I assuming this is the TabIndex value in the Control Properties? On this basis for each of the cases that an error can be generated the CASE line needs to contain the TabIndex number I want the error for?

Lastly when typing into the boxes for YEAR and PLATE in both Comparitor Vehicle and Our Vehicle I recieve two different error messages. When I put 'TEXT' into the YEAR list control no error is returned until I press Submit yet if I put 'TEXT' into PLATE list control and tab away an immediate message pops up titled 'Microsoft Forms' with 'Invalid property value'; is this linked to the above indexing?

I need to look at the STOCK NUMBER code as this is kicking an error no matter which way round I try to input but as its optional for now I have just memo'd it out.

Thanks

Jon

 

[dmt32]

First, pleased sort of works – it’s a big project & as with most requests on this board, you have to make a jolly good guess what OP is looking to achieve.</SPAN>

The indexing I am referring to are the array elements which are indexed by variable i.</SPAN>
The array starts at value 0 & its important to check that within the select case statement, the index values I have entered refer to correct controls to test for required data entry data types. So your problem “When I put 'TEXT' into the YEAR list control no error is returned” suggests that this control is set to accept text inputs rather than numeric. You need to review the array indexing against the control names array and update the select case statement accordingly.</SPAN>



Not sure why alpha numeric function did not work – have used it on other applications unless again, I have the array indexing incorrect for this control.</SPAN>

Can’t really answer your other point as code only operates when press commandbutton so can only conclude that you may have some other event code operating behind your form?</SPAN>

Hope helpful</SPAN>

Dave</SPAN>