VBA Userform data entry into table error and crash

[Lewzerrrr]

Hello,

I'm trying to create a userform that will input data into a sheet however when ran it gives me a Method Value Of Object Range Failed error and then completely crashes Excel.
The sheet i'm trying to input in is a table so i'm not sure if that's what's throwing it out as if I change the sheet reference to a blank sheet it works perfectly fine.

Here's the code:

Private Sub WriteDataToSheet()

    Dim newRow As Long
    
    With shData
    
        newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1
        
        .Cells(newRow, 1).Value = txtID.Value
        .Cells(newRow, 2).Value = cboCategory.Value
        .Cells(newRow, 3).Value = cboSubcategory.Value
        .Cells(newRow, 4).Value = txtCost.Value
        .Cells(newRow, 5).Value = txtDate.Value
        .Cells(newRow, 6).Value = cboMonth.Value
            
    End With

End Sub

It gets stuck on the first .Cells(newRow, 1).value line.

Any suggestions?

Thanks

 

[dmt32]

Hi,
your code seems to work ok

Two things

where is object variable shData intialized?

Have you declared Row as a variable somewhere in your project?

newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1

Dave

 

[Lewzerrrr]

Hi,
your code seems to work ok

Two things

where is object variable shData intialized?

Have you declared Row as a variable somewhere in your project?

newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1

Dave

Very strange

Not sure I understand the first question, if it helps I've changed it to Sheets("Data") and it still produces the same error. If I change it to a new/blank worksheet it works perfectly fine so at glance it looks as if it's an issue with the sheet or the table

Yes row is a variable in another module

Public Sub DeleteRow(ByVal row As Long)

    shData.Range("A2").Offset(row).EntireRow.Delete

End Sub

Thanks,

 

[dmt32]

Very strange

Not sure I understand the first question, if it helps I've changed it to Sheets("Data") and it still produces the same error.

Question is simple you have an Object Variable in your code (shData) just wanted to understand where in your project you initialized it?

example

Set shData = ThisWorkbook.Worksheets("Data")

Yes row is a variable in another module

Public Sub DeleteRow(ByVal row As Long)

    shData.Range("A2").Offset(row).EntireRow.Delete

End Sub

Thanks,

Row is a property of Range & you may (or may not) being getting a conflict using it as a parameter in your code

You could try changing it & see if resolves

Public Sub DeleteRow(ByVal lngRow As Long)

    shData.Range("A2").Offset(lngRow).EntireRow.Delete

End Sub

Dave

 

[Lewzerrrr]

Question is simple you have an Object Variable in your code (shData) just wanted to understand where in your project you initialized it?

example

Set shData = ThisWorkbook.Worksheets("Data")

Row is a property of Range & you may (or may not) being getting a conflict using it as a parameter in your code

You could try changing it & see if resolves

Public Sub DeleteRow(ByVal lngRow As Long)

    shData.Range("A2").Offset(lngRow).EntireRow.Delete

End Sub

Dave

Gotcha, it's not a variable, it's just the sheetname. Can't recall what the actual term is called but in the VBAProject box it's shData (Data) and others are Sheet1 (Budget Overview) so i'd just reference Sheet1 rather than Sheets("Budget Overview")

I've changed the row variable but problem still persists

Could I post a google drive link to it?

 

[dmt32]

Gotcha, it's not a variable, it's just the sheetname. Can't recall what the actual term is called but in the VBAProject box it's shData (Data) and others are Sheet1 (Budget Overview) so i'd just reference Sheet1 rather than Sheets("Budget Overview")

I've changed the row variable but problem still persists

Could I post a google drive link to it?

ok using sheets code name should not be a problem in same workbook

Place a link to file (and include dummy data) here - this will give others an opportunity to assist

Dave

 

[Lewzerrrr]

ok using sheets code name should not be a problem in same workbook

Place a link to file (and include dummy data) here - this will give others an opportunity to assist

Dave

Personal Budget(1).xlsm

drive.google.com

 

[dmt32]

Hi,
first, your range in worksheet Data is a Table & this needs a different approach in your coding

Private Sub WriteDataToSheet()
    Dim tblData     As ListObject
    Dim NewRecord   As ListRow
    
    Set tblData = shData.ListObjects(1)

    'Add New row to the table
    Set NewRecord = tblData.ListRows.Add(AlwaysInsert:=True)
    
    With NewRecord
    
        .Range(1).Value = Me.txtID.Value
        .Range(2).Value = Me.cboCategory.Value
        .Range(3).Value = Me.cboSubcategory.Value
        .Range(4).Value = Me.txtCost.Value
        .Range(5).Value = Me.txtDate.Value
        .Range(6).Value = Me.cboMonth.Value
            
    End With

End Sub

second, the sample workbook provided, it just keeps crashing when trying to write data to the table.
To check, I imported the userform to a clean workbook & all seems to work ok

Don't have too much time to spend investigating but if you continue to get same issues suggest either try repairing the workbook or create a fresh workbook & see if this resolves.

Dave