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]

The following code found within 'Module1' is now highlighting as shown with the following error: 'Microsoft Visual Basic - Run-time error 1004: Method 'Range of Object_Worksheet failed'

Sub irc_mi_Click()
[B]UserForm.Show[/B]
End Sub

 

[walkerl8]

Does anyone have any advice for the issue within this thread?

Thanks in advance

 

[MARK858]

Is your UserForm actually called UserForm and not something like UserForm1 etc.

The codes below are running fine for me with the UserForm called UserForm1.

Sub UserForminit()
   UserForm1.Show
End Sub

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.AthleteInput.AddItem Item.Text
    Next Item
    'fill exercise combobox
    For Each Item In ws.Range("ExerciseList")  'dynamic named range
        Me.ExerciseInput.AddItem Item.Text
    Next Item
End Sub

file attached in link...

https://app.box.com/s/b2wsjfn27a931if66nsl96bpgudnkauw

 

[walkerl8]

Mark,

The UserForm is called just that (UserForm)

I have provided a link to the database for you to view if that makes things easier.

Thanks in advance,

https://www.dropbox.com/s/yxxruq7uk8b1oup/S&C (FINAL) (Update).xlsm?dl=0

 

[MARK858]

See last post I made for the file I was using.

 

[walkerl8]

Yes I am looking - I keep encountering the issue

See last post I made for the file I was using.

 

[MARK858]

Your named ranges are on sheet Validations not Database.

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 Sheets("Validations").Range("AthleteList")   'dynamic named range
        Me.AthleteInput.AddItem Item.Text
    Next Item
    'fill exercise combobox
    For Each Item In Sheets("Validations").Range("ExerciseList")  'dynamic named range
        Me.ExerciseInput.AddItem Item.Text
    Next Item
End Sub

 

[walkerl8]

Ah I see,

That seems to have brought the UserForm up.

The 'Date' box should automatically input the present date - do you know why that isn't happening now?

Appreciate your time,

 

[MARK858]

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

 

[walkerl8]

Mark,

That works perfectly now - I had to make a couple of changes to the post it area of the code which now seems to paste into the database. Last thing if you have any advice - if you bring the UserForm up on the file and click 'Add' - a message box appears, however if only 'Date' is filled in the message appears to say "leteInput" when it should say "AthleteInput" (or whatever textboox/combobox requires filling in).

Do you know why this is?

Thanks