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

 

[MARK858]

Which line is highlighted in the block below and what exactly does the error say?

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

 

[mikerickson]

Try

cmbExercise.AddItem Item.Text

 

[walkerl8]

Mark,

I have highlighted below the areas that the debug keeps highlighting.

Error is: 'Compile Error - Variable Not Defined

Which line is highlighted in the block below and what exactly does the error say?

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim Item As Range
    Set ws = ThisWorkbook.Sheets("Database")
    [B]txbDate[/B] = Date
    'fill athlete combobox
    For Each Item In ws.Range("AthleteList")   'dynamic named range
        [B]cmbAthlete[/B].AddItem Item
    Next Item
    'fill exercise combobox
    For Each Item In ws.Range("ExerciseList")  'dynamic named range
        c[B]mbExercise[/B].AddItem Item
    Next Item
End Sub

 

[walkerl8]

Mike,

Your suggestion still encounters the same error as per my reply to Mark

Try

cmbExercise.AddItem Item.Text

 

[MARK858]

That is because you have Option Explicit at the top of your subs but you haven't declared all your variables (your bolded errors).

Either declare all your variables (the correct answer IMHO) or remove/comment out the Option Explicit line.

 

[walkerl8]

Oh I see,

Would you mind guiding me through that - as I am still learning the basics of this and a lot of the code has come from advice on here.

Really appreciate your help

 

[MARK858]

I am assuming below that you have actually changed your Combobox names and are not actually assigning them to a variable.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet, txbDate As Date
    Dim Item As Range
    Set ws = ThisWorkbook.Sheets("Database")
    txbDate = Date
    'fill athlete combobox
    For Each Item In ws.Range("AthleteList")   'dynamic named range
        Me.cmbAthlete.AddItem Item.Text
    Next Item
    'fill exercise combobox
    For Each Item In ws.Range("ExerciseList")  'dynamic named range
        Me.cmbExercise.AddItem Item.Text
    Next Item
End Sub

 

[walkerl8]

Mark,

The two Combobox names have been changed to:

AthleteInput
ExerciseInput

Your code now highlights at the following areas with the error: 'Compile Error - Method or Data Member Not Found'

Private Sub UserForm_Initialize()
    Dim ws As Worksheet, txbDate As Date
    Dim Item As Range
    Set ws = ThisWorkbook.Sheets("Database")
    txbDate = Date
    'fill athlete combobox
    For Each Item In ws.Range("AthleteList")   'dynamic named range
        Me.[B]cmbAthlete[/B].AddItem Item.Text
    Next Item
    'fill exercise combobox
    For Each Item In ws.Range("ExerciseList")  'dynamic named range
        Me.[B]cmbExercise[/B].AddItem Item.Text
    Next Item
End Sub

 

[MARK858]

Then change the names in the code.