"Run-time error '91': Object variable or With block variable not set" error

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
Hi,

I get the "Run-time error '91': Object variable or With block variable not set" error when I run my code below". The error occurs at the line directly below.

VBA Code:
With wbCurUsers.Worksheets(1)

Any help would be much appreciated. Thank you, SS


Code I'm trying to run:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wbCurUsers As Workbook
    Dim strUser As String
   
    strUser = Environ("UserName")
   
    Set wbCurUsers = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Current Users.xlsx")
   
    With wbCurUsers.Worksheets(1)
        .Columns(1).Replace What:=strUser, Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
   
    wbCurUsers.Close SaveChanges:=True
    
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Looks good; works for me (up to loading one of my own wb's). I tried with an invalid file reference but can't suppress the error message (can't find...) so I suppose you have not done that since you're not getting that error. You're not using On Error Resume Next either, so not that. Maybe try another sheet reference (e.g. by name, not by index). Or is your sheet a chart sheet? AFAIK, a chart is a sheet but it's not a worksheet.

If none of that helps, maybe try Sheets reference - wbCurUsers.Sheets("SheetNameHere")
 
Upvote 0
Looks good; works for me (up to loading one of my own wb's). I tried with an invalid file reference but can't suppress the error message (can't find...) so I suppose you have not done that since you're not getting that error. You're not using On Error Resume Next either, so not that. Maybe try another sheet reference (e.g. by name, not by index). Or is your sheet a chart sheet? AFAIK, a chart is a sheet but it's not a worksheet.

If none of that helps, maybe try Sheets reference - wbCurUsers.Sheets("SheetNameHere")
I have noticed that it only happens when I use Power Automate to launch the workbook, do something, then save and close.
 
Upvote 0
Don't know what PA is, sorry. Only other suggestion I have is to step through the code. You can close the workbook to call the code but you'd need a breakpoint so it doesn't just close on you, or put a Stop statement before your problem line. As I see it, you don't know if the problem is that the wb object is invalid or if it's the sheet reference. If stepping through and examining what's going on doesn't help (you can use immediate window to refer to variables whose values have been set) then you could try Dim sht As Worksheet. Set the wb object as before, but then try to Set the sht object to Worksheets(1). Or use the other syntax as I've suggested. I still don't know if the reference is a chart or not (probably not).

EDIT - it's also possible to call the sub from the immediate window, but simpler to do as suggested, I think.
 
Upvote 0
Don't know what PA is, sorry. Only other suggestion I have is to step through the code. You can close the workbook to call the code but you'd need a breakpoint so it doesn't just close on you, or put a Stop statement before your problem line. As I see it, you don't know if the problem is that the wb object is invalid or if it's the sheet reference. If stepping through and examining what's going on doesn't help (you can use immediate window to refer to variables whose values have been set) then you could try Dim sht As Worksheet. Set the wb object as before, but then try to Set the sht object to Worksheets(1). Or use the other syntax as I've suggested. I still don't know if the reference is a chart or not (probably not).

EDIT - it's also possible to call the sub from the immediate window, but simpler to do as suggested, I think.
I will give that a try. Thanks.
 
Upvote 0
then try to Set the sht object to Worksheets(1)
Thought perhaps I should clarify that it would be like so:
VBA Code:
Dim wbCurUsers As Workbook
Dim sht As Worksheet
Dim strUser As String

Set wbCurUsers = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Current Users.xlsx")

'this may error and reveal this reference to be the issue
Set sht = wbCurUsers.Worksheets(1) 
'if not I'd add this to test. If the sheet name is displayed then I'm running out of ideas
msgbox sht.Name
 
Upvote 0
Solution

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

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