excel - Why Is Range Worksheet's Object Failing? - Stack Overflow

admin管理员组

文章数量:1122846

I'm trying to change the value of the cell "L83", as another cell is updated manually. those cells make part of a table.

this is the code that I'm using:

    Sub UpdateCell()
        ThisWorkbook.Sheets(1).Range("$L$83").Value = "AAA"
        Sheets(1).Range("L83").Value = "AAA"
        Sheets(1).Cells(83, 12).Value = "AAA"
        Sheets(1).ListObjects("tblLIBERACOES").DataBodyRange(83, 12).Value = "AAA"
    End Sub

all of those 3 options raise the following error:

Run-time error '-2147417848 (80010108)'
Method 'Range' of object '_Worksheet' failed

I'm using Excel 365.

thanks in advance.

P.S: Here goes my full code:

Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateALERTS Target
End Sub

UpdateALERTS procedure:

Sub UpdateALERTS(fieldNAME As Range)
    If (IsInsideColumn("REGISTRATION DATE")) Then
        EraseALERTS fieldNAME
    End If
End Sub

IsInsideColumn procedure:

Function IsInsideColumn(columnName As String) As Boolean
    On Error Resume Next
    
    Dim tRng As Range
    
    Set tRng = Intersect(Sheets(1).ListObjects("tblLIBERACOES").ListColumns(columnName).Range, ActiveCell)
    
    If Not tRng Is Nothing Then IsInsideColumn = True
    
    Set tRng = Nothing
End Function

EraseALERTS procedure:

Sub EraseALERTS(fieldNAME As Range)
    Dim colYELLOW As Integer
    Dim line As Integer
    
    colYELLOW = Sheets(1).ListObjects("tblLIBERACOES").ListColumns("YELLOW ALERT").Range.Column
    line = fieldNAME.Row
    Application.EnableEvents = False
    Sheets(1).Cells(line, colYELLOW).Value = ""
    Application.EnableEvents = True
End Sub

I'm trying to change the value of the cell "L83", as another cell is updated manually. those cells make part of a table.

this is the code that I'm using:

    Sub UpdateCell()
        ThisWorkbook.Sheets(1).Range("$L$83").Value = "AAA"
        Sheets(1).Range("L83").Value = "AAA"
        Sheets(1).Cells(83, 12).Value = "AAA"
        Sheets(1).ListObjects("tblLIBERACOES").DataBodyRange(83, 12).Value = "AAA"
    End Sub

all of those 3 options raise the following error:

Run-time error '-2147417848 (80010108)'
Method 'Range' of object '_Worksheet' failed

I'm using Excel 365.

thanks in advance.

P.S: Here goes my full code:

Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateALERTS Target
End Sub

UpdateALERTS procedure:

Sub UpdateALERTS(fieldNAME As Range)
    If (IsInsideColumn("REGISTRATION DATE")) Then
        EraseALERTS fieldNAME
    End If
End Sub

IsInsideColumn procedure:

Function IsInsideColumn(columnName As String) As Boolean
    On Error Resume Next
    
    Dim tRng As Range
    
    Set tRng = Intersect(Sheets(1).ListObjects("tblLIBERACOES").ListColumns(columnName).Range, ActiveCell)
    
    If Not tRng Is Nothing Then IsInsideColumn = True
    
    Set tRng = Nothing
End Function

EraseALERTS procedure:

Sub EraseALERTS(fieldNAME As Range)
    Dim colYELLOW As Integer
    Dim line As Integer
    
    colYELLOW = Sheets(1).ListObjects("tblLIBERACOES").ListColumns("YELLOW ALERT").Range.Column
    line = fieldNAME.Row
    Application.EnableEvents = False
    Sheets(1).Cells(line, colYELLOW).Value = ""
    Application.EnableEvents = True
End Sub

• excel
• vba
• excel-365

Share Improve this question Follow edited Nov 22, 2024 at 19:01 Alex Boorjs asked Nov 22, 2024 at 16:59 Alex BoorjsAlex Boorjs 8911 gold badge22 silver badges1010 bronze badges 13

• 1 Maybe Sheets(1) is not a worksheet? If you know you want a worksheet then it's typically better to work with the Worksheets collection, since Sheets also contains other types of sheet, such as Chart sheets. – Tim Williams Commented Nov 22, 2024 at 17:13
• ^^^ see here – cybernetic.nomad Commented Nov 22, 2024 at 17:14
• @TimWilliams yes, it is. when I print Sheets(1).Name it returns the name of the sheet properly. – Alex Boorjs Commented Nov 22, 2024 at 17:16
• Yes I just tested and the non-worksheet-related error would be Run-time error '438': Object doesn't support this property or method. – Tim Williams Commented Nov 22, 2024 at 17:18
• 1 ...and use Application.EnableEvents = False if you're updating a cell and don't want the event handler to fire. Don't forget to set it back to True when you're done. – Tim Williams Commented Nov 22, 2024 at 18:56

 |  Show 8 more comments

1 Answer 1

Sorted by: Reset to default 0

A Worksheet Change: Change Value in the Corresponding Row of Another Column in an Excel Table

• I'm not sure how convenient it is to split this into so many procedures. I would use the following instead.
• The code will only run when a single cell is changed.

Sheet Module, e.g. Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    EraseAlert Target
End Sub

Standard Module, e.g. Module1, or the Same Sheet Module

Sub EraseAlert(ByVal Target As Range)
    
    Dim WasSuccess As Boolean: WasSuccess = True
    Dim MsgString As String
    On Error GoTo ClearError ' start error-handling routine
    
    ' Define constants.
    Const TABLE_NAME As String = "tblLIBERACOES"
    Const TARGET_COLUMN_TITLE As String = "REGISTRATION DATE"
    Const CHANGE_COLUMN_TITLE As String = "YELLOW ALERT"
    Const CHANGE_VALUE As String = vbNullString
    
    ' Restrict 'Target' to a single cell.
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    ' Reference the ranges.
    Dim targetColumn As Range, changeColumn As Range, targetCell As Range
    With Target.Worksheet.ListObjects(TABLE_NAME)
        Set targetColumn = .ListColumns(TARGET_COLUMN_TITLE).DataBodyRange
        Set targetCell = Intersect(targetColumn, Target)
        If targetCell Is Nothing Then Exit Sub ' not in target column
        Set changeColumn = .ListColumns(CHANGE_COLUMN_TITLE).DataBodyRange
    End With
    
    ' Store the changed value in a variable.
    Dim TargetValue As Variant: TargetValue = targetCell.Value
    
    ' Disable events to prevent retriggering this or triggering any other event
    ' (all or nothing).
    Application.EnableEvents = False
    
    ' Undo to check if there was a change or the user changed his/her mind...
    Application.Undo ' selects the cell
    Application.Goto targetCell.Offset(1) ' therefore, select the one below
    
    ' ... i.e. just confirmed (pressed 'Enter') the same value:
    If CStr(targetCell.Value) <> CStr(TargetValue) Then
        ' Write back the changed value.
        targetCell.Value = TargetValue
        ' Change the value in the change column.
        Intersect(targetCell.EntireRow, changeColumn).Value = CHANGE_VALUE
    'Else ' no change; do nothing
    End If

ProcExit:
    On Error Resume Next ' defer error trapping; prevent endless loop
        Application.EnableEvents = True
        If Not WasSuccess Then MsgBox MsgString, vbCritical
    On Error GoTo 0 ' enable error trapping
    Exit Sub
ClearError: ' continue error-handling routine
    MsgString = "Run-time error [" & Err.Number & "]:" _
        & vbLf & vbLf & Err.Description
    WasSuccess = False
    Resume ProcExit ' redirect error-handling routine
End Sub

本文标签： excelWhy Is Range Worksheet39s Object FailingStack Overflow