Excel VBA Object Variable or With Block Variable Not Set Error

B

BeachDog_2600

New Member
Joined
Feb 4, 2025
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello!

I'm trying to figure out where I'm going wrong with my code. Basically I want my code to decide what protections to initiate and how to proceed based on the data in column C on the 'LOA' tab. My table starts at A8 (headers) and is dynamic. My code should be allowing user entry from C9 to last row and only allowing users to edit columns A-B & H-O if there is data selected in Column C for that row. I had to adjust some of the ranges and that's where I started receiving the error. My original code allowed users to edit the headers because there was data in C8 so I tried to update the code to prevent that.

My current code is below:

VBA Code: 
Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim wsRef As Worksheet
    Dim lastRow As Long
    Dim lastRowRef As Long
    
    ' Set worksheet references
    Set ws = ThisWorkbook.Sheets("LOA")
    Set wsRef = ThisWorkbook.Sheets("References")
    
    ' Unprotect workbook and sheets
    ThisWorkbook.Unprotect "123456"
    ws.Unprotect "123456"
    wsRef.Unprotect "123456"
    
    ' Sort data on LOA sheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("C8:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange ws.Range("A8:O" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Set up data validation for Employee Name
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row
    With ws.Range("C9:C" & lastRow).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef
    End With
    
    ' Set formulas for columns D-G
    ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)"
    ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)"
    ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)"
    ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)"
    
    ' Find the last row with data in column C
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    ' Set the range for column C
    Set dataRange = ws.Range("C9:C" & lastRow)
    
    ' Loop through each cell in column C
    For Each cell In dataRange
        If Not IsEmpty(cell) Then
            ' Unlock corresponding cells in A, B, and H-O
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False
            ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = False
        Else
            ' Lock corresponding cells in A, B, and H-O
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True
            ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = True
        End If
    Next cell
    
    ws.Range("C9:C" & lastRow).Locked = False
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    
    ' Protect References sheet
    wsRef.Protect "123456"
    
    ' Protect workbook
    ThisWorkbook.Protect "123456"
End Sub

Option Explicit

Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _
    "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work."

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim lastRow As Long
    On Error GoTo ErrorHandler
    
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    If Not Intersect(Target, Me.Range("C9:C" & lastRow)) Is Nothing Then
        Application.EnableEvents = False
        Me.Unprotect "123456"
        
        UpdateCellLockStatus Target
        
        Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    End If

ExitSub:
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    Resume ExitSub
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        Select Case Application.CutCopyMode
            Case xlCut
                ShowWarningMessage "cut"
            Case xlCopy
                ShowWarningMessage "copy"
        End Select
    End If
End Sub

Private Sub UpdateCellLockStatus(ByVal Target As Range)
    Dim cell As Range
    Dim affectedRange As Range
    
    For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow))
        Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _
                                  Me.Range("H" & cell.Row & ":O" & cell.Row))
        
        affectedRange.Locked = (cell.Value = "")
    Next cell
End Sub

Private Sub ShowWarningMessage(ByVal action As String)
    MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning"
End Sub

Private Sub Worksheet_Change_Paste(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        ShowWarningMessage "paste"
    End If
End Sub

Option Explicit

Sub AddNewRowToTable()
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim table_object_row As ListRow
    
    ' Set references to the worksheet and table
    Set ws = ThisWorkbook.Worksheets("LOA")  ' Replace "Sheet1" with your sheet name
    Set tbl = ws.ListObjects(1)  ' Replace "Table1" with your table name

    ' Unprotect workbook and sheets
    ws.Unprotect "123456"
    
    Set table_object_row = tbl.ListRows.Add
    table_object_row.Range(1, 1).Value = ""
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub

My old code that worked is below:

VBA Code: 
Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim wsRef As Worksheet
    Dim lastRow As Long
    Dim lastRowRef As Long
    
    ' Set worksheet references
    Set ws = ThisWorkbook.Sheets("LOA")
    Set wsRef = ThisWorkbook.Sheets("References")
    
    ' Unprotect workbook and sheets
    ThisWorkbook.Unprotect "123456"
    ws.Unprotect "123456"
    wsRef.Unprotect "123456"
    
    ' Sort data on LOA sheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("C9:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange ws.Range("A9:AL" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Set up data validation for Employee Name
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row
    With ws.Range("C9:C" & lastRow).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef
    End With
    
    ' Set formulas for columns D-G
    ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)"
    ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)"
    ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)"
    ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)"
    
    ' Find the last row with data in column C
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    ' Set the range for column C
    Set dataRange = ws.Range("C1:C" & lastRow)
    
    ' Loop through each cell in column C
    For Each cell In dataRange
        If Not IsEmpty(cell) Then
            ' Unlock corresponding cells in A, B, and H-AL
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False
            ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = False
        Else
            ' Lock corresponding cells in A, B, and H-AL
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True
            ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = True
        End If
    Next cell
    
    ws.Range("C9:C" & lastRow).Locked = False
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    
    ' Protect References sheet
    wsRef.Protect "123456"
    
    ' Protect workbook
    ThisWorkbook.Protect "123456"
End Sub

Option Explicit

Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _
    "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work."

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    
    If Not Intersect(Target, Me.Range("C:C")) Is Nothing Then
        Application.EnableEvents = False
        Me.Unprotect "123456"
        
        UpdateCellLockStatus Target
        
        Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    End If

ExitSub:
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    Resume ExitSub
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        Select Case Application.CutCopyMode
            Case xlCut
                ShowWarningMessage "cut"
            Case xlCopy
                ShowWarningMessage "copy"
        End Select
    End If
End Sub

Private Sub UpdateCellLockStatus(ByVal Target As Range)
    Dim cell As Range
    Dim affectedRange As Range
    
    For Each cell In Intersect(Target, Me.Range("C:C"))
        Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _
                                  Me.Range("H" & cell.Row & ":AL" & cell.Row))
        
        affectedRange.Locked = (cell.Value = "")
    Next cell
End Sub

Private Sub ShowWarningMessage(ByVal action As String)
    MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning"
End Sub

Private Sub Worksheet_Change_Paste(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        ShowWarningMessage "paste"
    End If
End Sub

Option Explicit

Sub AddNewRowToTable()
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim table_object_row As ListRow
    
    ' Set references to the worksheet and table
    Set ws = ThisWorkbook.Worksheets("LOA")  ' Replace "Sheet1" with your sheet name
    Set tbl = ws.ListObjects(1)  ' Replace "Table1" with your table name

    ' Unprotect workbook and sheets
    ws.Unprotect "123456"
    
    Set table_object_row = tbl.ListRows.Add
    table_object_row.Range(1, 1).Value = ""
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub

I'm also trying to add code that will delete all of the data in a row (without removing data validations or formatting) when the data in Column C is deleted. I tried the following code, but I wasn't sure how to implement it.

VBA Code: 
Option Explicit

Private Const WARNING_MESSAGE_DEL As String = "Warning: You are attempting to delete data in Column C." & vbNewLine & _
    "This action will clear the entire row. Do you want to proceed?"
    
Private Sub Worksheet_BeforeDelete(ByVal Target As Range, Cancel As Boolean)
    Dim intersectRange As Range
    
    ' Check if the deletion involves Column C
    Set intersectRange = Intersect(Target, Me.Columns("C"))
    
    If Not intersectRange Is Nothing Then
        If MsgBox(WARNING_MESSAGE_DEL, vbExclamation + vbYesNo, "Deletion Warning") = vbNo Then
            Cancel = True
        End If
    End If
End Sub

Private Sub ClearRowExceptColumnC(ByVal rowNumber As Long)
    ' Clear cells to the left of Column C
    Me.Range("A" & rowNumber & ":B" & rowNumber).ClearContents
    
    ' Clear cells to the right of Column C
    Me.Range("D" & rowNumber & ":" & Me.Cells(rowNumber, Columns.Count).Address).ClearContents
End Sub

I'm very much a novice when it comes to VBA, but I love the customizability and am trying to learn more. If you have any other advice it would be very much appreciated!
 
Sort by date Sort by votes
Did you id the line that raises the error? I didn't see that and it's vital, I think. IMO you should post the offending line outside the code in your post, and if it appears more than once, then use comments in your code. I understand the error; I'm just too lazy to search for that needle in your haystack ;)
After a quick look, I suspect it involves your reference to Me. because that code is in a sheet module. AFAIK, Me would only apply to referencing a userform and the code would be in the userform module, not in a sheet event.
 
Upvote 0
Micron said:
I suspect it involves your reference to Me. because that code is in a sheet module. AFAIK, Me would only apply to referencing a userform and the code would be in the userform module, not in a sheet event.
Click to expand...
Me works fine in a Worksheet module, it just refers to the object the code resides in.... you can't use it in a regular module.
 
Upvote 0
What happens if you add the 2 lines in blue as shown:

Rich (BB code): 
Private Sub UpdateCellLockStatus(ByVal Target As Range)
    Dim cell As Range
    Dim affectedRange As Range
    Dim lastRow As Long
    
    lastRow = Me.Cells(Me.Rows.Count, "C").End(xlUp).Row
    For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow))
        Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _
                                  Me.Range("H" & cell.Row & ":O" & cell.Row))
        
        affectedRange.Locked = (cell.Value = "")
    Next cell
End Sub
 
Upvote 0
You're using Option Explicit but you don't have a variable for DataRange in Workbook Open event.
Set DataRange = ws.Range("C9:C" & lastRow)
It looks like you have a module for every procedure that you posted because you have Option Explicit at the beginning of each procedure. It's not necessary to create a module for each procedure BTW.

EDIT - I see you have other undeclared variables as well. Best that you repeatedly try to compile your code and fix the problems that that will find.
 
Last edited:
Upvote 0
Alex Blakenburg said:
What happens if you add the 2 lines in blue as shown:

Rich (BB code): 
Private Sub UpdateCellLockStatus(ByVal Target As Range)
    Dim cell As Range
    Dim affectedRange As Range
    Dim lastRow As Long
   
    lastRow = Me.Cells(Me.Rows.Count, "C").End(xlUp).Row
    For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow))
        Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _
                                  Me.Range("H" & cell.Row & ":O" & cell.Row))
       
        affectedRange.Locked = (cell.Value = "")
    Next cell
End Sub
Click to expand...
Unfortunately that gives me the same error.
 
Upvote 0
Micron said:
You're using Option Explicit but you don't have a variable for DataRange in Workbook Open event.
Set DataRange = ws.Range("C9:C" & lastRow)
It looks like you have a module for every procedure that you posted because you have Option Explicit at the beginning of each procedure. It's not necessary to create a module for each procedure BTW.

EDIT - I see you have other undeclared variables as well. Best that you repeatedly try to compile your code and fix the problems that that will find.
Click to expand...
I'm sorry if this is a dumb question, but when I go in to VBA I click on 'Debug' and then 'Compile VBAProject', but nothing happens. What would the next step be?
 
Upvote 0
I don't know because if you have Option Explicit at the top of a module and if code in that module uses an undeclared variable, it should not compile. The only exception I can think of is if the reference is to a property, object or method that is preceded by the Bang operator ( ! ) as in Me!Range("BadRangeReferenceHere"). At least that is the case for Access, which I'm more familiar with. I had to fix 3 or 4 undeclared variables to get your code to compile. One of them was ID'd in post 4 (1st blue line. I'm trying to test your code and raise the error, but might not succeed since my wb doesn't have what yours has.
 
Upvote 0
I took the old code that worked and went through notating every place that I updated or added code. Please find that below and let me know if there is anything else I can provide that would help!

VBA Code: 
Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim wsRef As Worksheet
    Dim lastRow As Long
    Dim lastRowRef As Long
    
    ' Set worksheet references
    Set ws = ThisWorkbook.Sheets("LOA")
    Set wsRef = ThisWorkbook.Sheets("References")
    
    ' Unprotect workbook and sheets
    ThisWorkbook.Unprotect "123456"
    ws.Unprotect "123456"
    wsRef.Unprotect "123456"
    
    ' Sort data on LOA sheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("C9:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ‘***UPDATED CODE*** ws.Sort.SortFields.Add Key:=ws.Range("C8:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ***UPDATED CODE***
    With ws.Sort
        .SetRange ws.Range("A9:AL" & lastRow)
         ‘***UPDATED CODE*** .SetRange ws.Range("A8:O" & lastRow) ***UPDATED CODE***
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Set up data validation for Employee Name
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row
    With ws.Range("C9:C" & lastRow).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef
    End With
    
    ' Set formulas for columns D-G
    ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)"
    ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)"
    ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)"
    ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)"
    
    ' Find the last row with data in column C
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    ' Set the range for column C
    Set dataRange = ws.Range("C1:C" & lastRow)
    ‘***UPDATED CODE*** Set dataRange = ws.Range("C9:C" & lastRow) ***UPDATED CODE***

    ' Loop through each cell in column C
    For Each cell In dataRange
        If Not IsEmpty(cell) Then
            ' Unlock corresponding cells in A, B, and H-O
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False
            ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = False
            ‘***UPDATED CODE*** ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = False ***UPDATED CODE***
        Else
            ' Lock corresponding cells in A, B, and H-O
            ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True
            ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = True
            ‘***UPDATED CODE*** ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = True ***UPDATED CODE***
        End If
    Next cell
    
    ws.Range("C9:C" & lastRow).Locked = False
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    
    ' Protect References sheet
    wsRef.Protect "123456"
    
    ' Protect workbook
    ThisWorkbook.Protect "123456"
End Sub

Option Explicit

Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _
    "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work."

Private Sub Worksheet_Change(ByVal Target As Range)
    ‘***ADDED CODE*** Dim ws As Worksheet ***ADDED CODE***
    ‘***ADDED CODE*** Dim lastRow As Long ***ADDED CODE***
    On Error GoTo ErrorHandler

    ‘***ADDED CODE*** lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ***ADDED CODE***
    If Not Intersect(Target, Me.Range("C:C")) Is Nothing Then
    ‘***UPDATED CODE*** If Not Intersect(Target, Me.Range("C9:C" & lastRow)) Is Nothing Then ***UPDATED CODE***
        Application.EnableEvents = False
        Me.Unprotect "123456"
        
        UpdateCellLockStatus Target
        
        Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
    End If

ExitSub:
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    Resume ExitSub
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        Select Case Application.CutCopyMode
            Case xlCut
                ShowWarningMessage "cut"
            Case xlCopy
                ShowWarningMessage "copy"
        End Select
    End If
End Sub

Private Sub UpdateCellLockStatus(ByVal Target As Range)
    Dim cell As Range
    Dim affectedRange As Range
    
    For Each cell In Intersect(Target, Me.Range("C:C"))
    ‘***UPDATED CODE*** For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow)) ***UPDATED CODE***
        Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _
                                                    Me.Range("H" & cell.Row & ":AL" & cell.Row))
                                                    ‘***UPDATED CODE*** Me.Range("H" & cell.Row & ":O" & cell.Row)) ***UPDATED CODE***
        
        affectedRange.Locked = (cell.Value = "")
    Next cell
End Sub

Private Sub ShowWarningMessage(ByVal action As String)
    MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning"
End Sub

Private Sub Worksheet_Change_Paste(ByVal Target As Range)
    If Not Application.CutCopyMode = False Then
        ShowWarningMessage "paste"
    End If
End Sub

Option Explicit

Sub AddNewRowToTable()
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim table_object_row As ListRow
    
    ' Set references to the worksheet and table
    Set ws = ThisWorkbook.Worksheets("LOA")
    Set tbl = ws.ListObjects(1)

    ' Unprotect workbook and sheets
    ws.Unprotect "123456"
    
    Set table_object_row = tbl.ListRows.Add
    table_object_row.Range(1, 1).Value = ""
    
    ' Protect LOA sheet
    ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub
 
Upvote 0
Update: I can raise the error in worksheet change event if I
- put a Stop statement near the top of the workbook open event code
- change the error handler to:
VBA Code: 
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    'Resume ExitSub '<<<
    Resume
- save the wb and re-open
- then step through the code
For me, the error is raised because lastrow = 0, although the error doesn't seem to make sense for that reason.

I don't think posting code that works is going to help but I'll try to look at it after you reply to this suggestion. Add this blue line and see if it returns 0.
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim lastrow As Long
    On Error GoTo ErrorHandler
    
    lastrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).row
    Msgbox lastrow
    If Not Intersect(Target, Me.Range("C9:C" & lastrow)) Is Nothing Then
then save and re-open your wb. BTW, you get the error when you open the wb or when doing something else?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Object Variable or With Block Variable Not Set Error
Replies
2
Views
190
NorthbyNorthwest
N
D
  • Question Question
Need VBA Code for dynamic row height
Replies
1
Views
58
kvsrinivasamurthy
K
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
435
tixarah
T
C
  • Question Question
Auto Hide Rows Based On Cell Value
Replies
2
Views
235
kevin9999
K
P
  • Question Question
Use VBA to send HTML email with excel data
Replies
7
Views
525
Micron
M

Forum statistics

Threads
1,226,795
Messages
6,193,046
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top