Getting run-time error using User Form

[Livin404]

Greetings,

I think I've gotten closer; however I'm getting a "run time error" followed by a long number. In the same error messge box I get "Method 'Value' of object out of 'Range' failed.

I changed the first column to a Custom Format to a "dd mmm yy". I have a Header Row in Row 1. I have a total 6 columns, but only the first five I need the User Form for. The last column is just the day of the month. (For a Pivot Table).

Sub Submit()

Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Activity")

iRow = [Counta(Activity!A:A)] + 1

With sh

     .Cells(iRow, 1).Value = CDate(frmForm.Txtdate)
     .Cells(iRow, 2) = frmForm.Txtmsn.Value
     .Cells(iRow, 3) = frmForm.Cmbmode.Value
     .Cells(iRow, 4) = frmForm.Cmbdentonfms.Value
     .Cells(iRow, 5) = frmForm.Txtweight.Value
     
    
  End With


End Sub

Thank you,

 

[Siddharth Rout]

On which line are you getting the error?

BTW do you still get the error if you replace

iRow = [Counta(Activity!A:A)] + 1

with

iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row + 1

 

[Livin404]

On which line are you getting the error?

BTW do you still get the error if you replace

iRow = [Counta(Activity!A:A)] + 1

with

iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row + 1

I'm getting an error on the first line

 .Cells(iRow, 1).Value = CDate(frmForm.Txtdate)

 

[Livin404]

On which line are you getting the error?

BTW do you still get the error if you replace

iRow = [Counta(Activity!A:A)] + 1

with

iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row + 1

I did try tha solution you made and I get the same thing.

 

[Joe4]

If you place this line of code just above that line returning the error, what does it return when you try to run the code?

MsgBox frmForm.Txtdate

 

[Livin404]

If you place this line of code just above that line returning the error, what does it return when you try to run the code?

MsgBox frmForm.Txtdate

Strange it displays 19 Aug 22 which is what I typed in on the User Form. When select ok on the message box I get the Run-time error the same error as before. When I select the Debug. I get another messaage which is an automation error. "The Object invoked has disconnected from it's clients". Excel wil shut down and reopen.

 

[Joe4]

Strange it displays 19 Aug 22 which is what I typed in on the User Form. When select ok on the message box I get the Run-time error the same error as before. When I select the Debug. I get another messaage which is an automation error. "The Object invoked has disconnected from it's clients". Excel wil shut down and reopen.

Hmmm, sounds like the issue is with something outside of this code.
Maybe the connection (if you are linking/connecting to other data sources).
Or perhaps you have an corruption issue.

 

[dmt32]

Strange it displays 19 Aug 22 which is what I typed in on the User Form. When select ok on the message box I get the Run-time error the same error as before.

try this update to your code & see if resolves your issue but if not & as already suggested, you may have other issues like file corruption going on

Sub Submit()
    Dim sh          As Worksheet
    Dim iRow        As Long
    
    Set sh = ThisWorkbook.Worksheets("Activity")
    
    iRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1
    
    With Me.txtdate
        If Not IsDate(.Value) Then .Value = Date
    End With
    
    With sh
        
        With .Cells(iRow, 1)
            .Value = DateValue(Me.txtdate.Value)
            .NumberFormat = "dd mmm yy"
        End With
        
        .Cells(iRow, 2) = Me.Txtmsn.Value
        .Cells(iRow, 3) = Me.Cmbmode.Value
        .Cells(iRow, 4) = Me.Cmbdentonfms.Value
        .Cells(iRow, 5) = Me.Txtweight.Value
    End With
    
    MsgBox "Record Submitted", 64, "Submitted"
    
End Sub

Note I have replaced your userform name with ME keyword as I assume that the code is in your userforms code page?

Dave

 

[Livin404]

try this update to your code & see if resolves your issue but if not & as already suggested, you may have other issues like file corruption going on

Sub Submit()
    Dim sh          As Worksheet
    Dim iRow        As Long
   
    Set sh = ThisWorkbook.Worksheets("Activity")
   
    iRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1
   
    With Me.txtdate
        If Not IsDate(.Value) Then .Value = Date
    End With
   
    With sh
       
        With .Cells(iRow, 1)
            .Value = DateValue(Me.txtdate.Value)
            .NumberFormat = "dd mmm yy"
        End With
       
        .Cells(iRow, 2) = Me.Txtmsn.Value
        .Cells(iRow, 3) = Me.Cmbmode.Value
        .Cells(iRow, 4) = Me.Cmbdentonfms.Value
        .Cells(iRow, 5) = Me.Txtweight.Value
    End With
   
    MsgBox "Record Submitted", 64, "Submitted"
   
End Sub

Note I have replaced your userform name with ME keyword as I assume that the code is in your userforms code page?

Dave

I tried that, why would I need to change the frm.form to .me? Thank you,

 

[dmt32]

I tried that,

Did the update resolve your issue?

why would I need to change the frm.form to .me? Thank you,

You don't need to - but Me keyword allows you to refer to either the current worksheet, workbook, or userform where code resides without having to use their name.

Dave