VBA Error (UserForm)

[walkerl8]

Hi All,

Am encountering an error with the following code. Hopefully the code is pretty self-explanatory - I simply have a UserForm which until all fields are completed then the 'Add' function isn't available to click to paste the data. I am encountering the error within the bolded section of the code.


Appreciate anyone's recommendations.


Thanks,


Luke

Option Explicit
Private Sub AddButton_Click()
    If ValidateInput = "Failed" Then Exit Sub
    PostToFile
End Sub
Private Sub NewEntryButton_Click()
    If ValidateInput = "Failed" Then Exit Sub
    PostToFile
    ClearForm
End Sub
Private Sub ClearFormButton_Click()
    ClearForm
End Sub
Private Sub CancelButton_Click()
    Unload Me
End Sub
[B]Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim Item As Range
    Set ws = ThisWorkbook.Sheets("Database")
    txbDate = Date
    'fill athlete combobox
    For Each Item In ws.Range("AthleteList")   'dynamic named range
        cmbAthlete.AddItem Item
    Next Item
    'fill exercise combobox
    For Each Item In ws.Range("ExerciseList")  'dynamic named range
        cmbExercise.AddItem Item
    Next Item
End Sub[/B]
Sub ClearForm()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Name <> "txbDate" Then
            Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
            Case "ComboBox"
                ctrl.ListIndex = -1
            End Select
        End If
    Next
End Sub
Function ValidateInput()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "TextBox"
            If ctrl.Text = "" Then
                ValidateInput = "Failed"
                MsgBox "Please enter " & Right(ctrl.Name, Len(ctrl.Name) - 3)
                Exit Function
            End If
        Case "ComboBox"
            If ctrl.ListIndex = -1 Then
                ValidateInput = "Failed"
                MsgBox "Please make selection " & Right(ctrl.Name, Len(ctrl.Name) - 3)
                Exit Function
            End If
        End Select
    Next
    ValidateInput = "Passed"
End Function
Sub PostToFile()
    Dim ws As Worksheet
    Dim nr As Long
    Dim reg As Range
    Set ws = ThisWorkbook.Sheets("Database")
    nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(nr, 1) = CDate(txbDate)
    ws.Cells(nr, 3) = cmbAthlete
    ws.Cells(nr, 7) = cmbExercise
    ws.Cells(nr, 8) = Val(txbLoad)
    ws.Cells(nr, 9) = Val(txbReps)
    ws.Cells(nr, 11) = txbComments
End Sub

 

[walkerl8]

I figured that part out - just needed to delete the -3 aspect of the validate code. However the message box is coming up with the name 'AthleteInput' or whatever when I only want it to say something like "Please Make Selection from 'Athlete'"

 

[MARK858]

That is what your -3 is doing... remember you changed the name.

Right(ctrl.Name, Len(ctrl.Name) [COLOR="#FF0000"]- 3[/COLOR])

 

[walkerl8]

Yeah sorry mate I just figured that - the text and comboboxes are named 'AthleteInput' 'ExerciseInput'. Therefore in the Message box that is what is coming up. Is there a way to change that so rather than the message saying 'Please Make Selection From AthleteInput' - it instead says 'Please Make Selection From Athlete Dropdown' or 'Please Input Reps' for example

The ideal thing would be for the message boxes to loop through in order so that if it is a textbox that is missing input then the message would be:

'Please Input ...'

and if it is a combobox that is missing then the message would be:

'Please Make Selection From ...'

That is what your -3 is doing...

Right(ctrl.Name, Len(ctrl.Name) [COLOR=#ff0000]- 3[/COLOR])

 

[MARK858]

Not for me tonight maybe someone else will re-write it or start a new thread.

 

[walkerl8]

Thanks for all of your help Mark