Excel 2010 crashing on userform data entry.

[dhosi439]

When I run the following userform code, it will run once. After that I receive a Run-Time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed.

After clicking end on the prompt, Excel locks up and I have to end the process. Upon opening the document, the data for the second row is not there and the formatting is incorrect on the newly added row. Please not that the row of data is part of a table.

Additionally, if I attempt to delete the row of data that applies to the worksheet, Excel crashes with the following Event Viewer information. This only happens immediately after running the code. If I exit the workbook there isn't an issue.

Event viewer log for both issues is similar.

Faulting application name: EXCEL.EXE, version: 14.0.4756.1000, time stamp: 0x4b9c08e8
Faulting module name: EXCEL.EXE, version: 14.0.4756.1000, time stamp: 0x4b9c08e8
Exception code: 0xc0000005
Fault offset: 0x00127de8
Faulting process id: 0x1418
Faulting application start time: 0x01cbe80a04c19164
Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Faulting module path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Report Id: 5352e940-53fd-11e0-8a15-f0def126af42

This works fine in Excel 2007, but not in Excel 2010.

I've looked over my code and I'm sure I am missing something with the switch to a newer VBA in Excel 2010, but I'm not sure what it is.

The first block of code runs on command button click and calls the second block of code to apply the data on the userform.

Private Sub cmdAdd_Click()
'Set lSheet value based on Checkbox status | call Apply_Record, pass lSheet variable
Dim lSheet As Worksheet

If cbxChecking.Value = "True" Then
    Set lSheet = Worksheets("Checking")
        Call Apply_Record$(lSheet)
ElseIf cbxChecking.Value = "False" Then
    Set lSheet = Worksheets("Savings")
        Call Apply_Record$(lSheet)
End If

frmMain.lstData.RowSource = "CheckingData"
frmMain.lstData.Selected(lstData.ListCount - 1) = True

End Sub

Public Sub Apply_Record(iSheet)
Dim iRow As Long, ws As Worksheet

'set passed variable to ws variabe
Set ws = iSheet

'find  first empty row in database
If frmMain.cbxFirst.Value = "True" Then
    iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Else
    iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If

'check for a date
If Trim(frmMain.txtDate.Value) = "" Then
    MsgBox "Please enter a date"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = frmMain.txtDate.Value
ws.Cells(iRow, 3).Value = frmMain.cbVenue.Value
ws.Cells(iRow, 4).Value = frmMain.cbDesc.Value
ws.Cells(iRow, 5).Value = frmMain.cbType.Value
ws.Cells(iRow, 6).Value = frmMain.txtDepo.Value
ws.Cells(iRow, 7).Value = frmMain.txtWith.Value
ws.Cells(iRow, 9).Value = frmMain.cbVeri.Value

'clear the data from userform
frmMain.txtDate.Value = ""
frmMain.cbVenue.Value = ""
frmMain.cbDesc.Value = ""
frmMain.cbType.Value = ""
frmMain.txtDepo.Value = ""
frmMain.txtWith.Value = ""
frmMain.cbVeri.Value = ""

'Set focus to close button
frmMain.cmdClose.SetFocus

'Form Analysis Refresh
Call SumLoop_Apply

'Refresh Venues to see new data entries in userform
If frmMain.cbxChecking.Value = "True" Then
    frmMain.cbDesc.RowSource = "CheckDesc"
ElseIf frmMain.cbxChecking.Value = "False" Then
    frmMain.cbDesc.RowSource = "SavDesc"
End If

End Sub

Any help with the code or with this specific issue would be greatly appreciated as I am trying to move from Excel 2007 to 2010.

 

[dhosi439]

Does anyone have any suggestions?

 

[dhosi439]

This is the code I am using to apply data from the userform to a table. I am still experiencing the issues noted above.

Public Sub Apply_Record()
Dim ws As Worksheet, oNewRow As ListRow
If frmMain.cbxChecking.Value = "True" Then 'set worksheet and list object to savings table
    Set ws = Worksheets("Checking")
    Set oNewRow = ws.ListObjects("Checking").ListRows.Add(AlwaysInsert:=True)
    frmMain.lstData.RowSource = "CheckingData"
ElseIf frmMain.cbxChecking.Value = "False" Then
    Set ws = Worksheets("Savings")
    Set oNewRow = ws.ListObjects("Savings").ListRows.Add(AlwaysInsert:=True)
    frmMain.lstData.RowSource = "SavingsData"
End If
'copy the data to the database
With oNewRow.Range
    .Cells(1, 2).Value = frmMain.DTPicker.Value
    .Cells(1, 3).Value = frmMain.cbVenue.Value
    .Cells(1, 4).Value = frmMain.cbDesc.Value
    .Cells(1, 5).Value = frmMain.cbType.Value
    .Cells(1, 6).Value = frmMain.txtAmount.Value
    .Cells(1, 8).Value = StrConv(frmMain.cbVeri.Value, vbUpperCase)
End With
Call SumLoop_Apply 'Form Analysis Refresh
'clear the data from userform
With frmMain
    .cbVenue.Value = ""
    .cbDesc.Value = ""
    .cbType.Value = ""
    .txtAmount.Value = ""
    .cbVeri.Value = ""
End With
frmMain.cmdClose.SetFocus 'Set focus to close button
If frmMain.cbxChecking.Value = "True" Then 'Refresh Venues to see new data entries in userform
    frmMain.cbDesc.RowSource = "CheckDesc"
ElseIf frmMain.cbxChecking.Value = "False" Then
    frmMain.cbDesc.RowSource = "SavDesc"
End If
End Sub

 

[dhosi439]

Cross reference link:

http://www.ozgrid.com/forum/showthread.php?t=156653

 

[whitew5720]

I have seen the same issue on a sorted insert...
Table has column "Name" sorted A-Z.

Loop...
i1 = -1
i1 = WorksheetFunction.MATCH( InsName, .ListRows("Name").DatabodyRange, 1 )
If i1 = -1 Then Stop

ix = -1
ix = .ListRows.Add(i1).Index
if ix <> i1 Then Stop

end of loop

What happens. I am inserting some number of new entries into the table.
Some number are successfully added.
If the insert point is creater than row 326 (for example, but is always the same row), then the insert fails.
Exiting the code results in being unable to select cells - it appears the UI Events handler is broken.

My workaround - eventually I concluded that the Table itself was corrupt and that the failing Insert is but a symptom of the corrupt table - the workaround was to copy the table data and formulas to a new sheet. Create another new sheet that will contain the non-corrupt version of the table. Delete all the rows in the corrupt table. Move the table into the "another new sheet". Resize the table to the original size and restore the data and formulas save to "a new sheet".

It did not work simply to delete the rows of the table, resize and restore that data.
The problem remained at the same insert row number.
Hence my further conclusion that the corruption was a combination of Table Object issues intersecting with the Sheet.

I have had other List Object corruption, or List Object Engine stalls, and concluded under other circumstances that an element of the issue is Formating. I believe that a possibly underlying cause is related to "Styles" style formatting and legacy style formating (with cells individually formated by hand than having an applied style).

I managed to work around this issue when first observed back in February/March of this year, and there have been no problems until this week when it has returned, but with some kind of mutation that is immune to the February/March work-around described above.

This problem always appeared in our most complex, macro-intensive Sales Operations tools, but this week after an upgrade from SharePoint 2007 to SharePoint 2010 another much simpler tool has started to fail, only a bit differently. This time, the List Object is connected to a SharePoint 2010 list object, and the insert row fails in this way.

nx = .ListRows.Count

ix = -1
ix = .ListRows.Add.Index ' Add to last row of Table
If ix <= nx then Stop

The test however passes as if a new row has been added.
On inspection of the table however, it is clear that the row has NOT been added.
Nor is an error raised.

The reference .ListColumns("Name").DataBodyRange.Cells(ix) = InsName does not fail either.
However, on inspection of the table, the value InsName has been stored into the Total Row.
It is as if the Table Resize for the insert failed with no error propogation to the VBA Object Model. In this sense this is simlar to the first problem described, because there is no error failure recorded either.

In the first case (the sorted insert), on inspection, it appears that Table Resize for the insert also failed. This is clear because although a blank row is inserted at the selected insert point (row i1), the table, which includes a total row, was not correctly resized. Instead after the undetected, failed resize, we found that the content of what was formerly the TotalRow cells was moved outside the table to the row below the TotalRow, and that when the rows of the Data Range were shifted down, that the last row of the DatabodyRange over-wrote the Totals Row.

As an experiment I am going to retry this by first hiding the totals row (you'll have detected some of this issue is you've ever tried to resize a table with a totals row from code - the calculation is weird), making the insert, then enabling the Totals row (you do not lose Total row definition).

This response is also making me think that the problem may be related to the ambiguity of table size definition in the Sheet representation and the existence of a dummy row that is not real when there is no totals row (or there is one with a totals row if the table is empty - it is quite a mess). Which brings back to the formating issue as insert row seems not to capture manual cell format adjustments correctly depending on whether the insert is in the first row, a mid row, or the final row. Examples of formating mismanagement include copying manual forrmating of the Title Row into an inserted first row, inserting a row with formatted text indents - Excel applies an additional indent in each new row, loss of number format, and even loss of column formulas if the insert is at the last row (under a circumstance not fully understood).

I notice in your cross-referenced link that you mention that possibly your FONT related code is an issue, and in your case, I would say I am positive that it is. Whether you have a coding error, or are hitting an Excel issues is another question.