Excel vba: Run-time error '-2147221080(800401a8) Method 'Range' of object '_worksheet' failed


I'm creating a tool in Excel with an external database. The database userform and buttons work, but when i go forward to other userforms and i return to the database i receive this runtime error when using one of the buttons.

The code that i use to determine the range works when activating the userform, but not when i return to the userform.

This line creates the error:

Set MyData = Ws.Range("A5").CurrentRegion.Offset(1)

Open in new window

According the internet the appropriate object variable is missing.

Do somebody know how to solve this?

Best regards,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Hos is Ws defined?

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There could be few possible reasons behind the error...

1) The variables Ws and MyData are not declared if you are using Option Explicit on top of the code.

2) Ws is not set before that line.

3) The scope of variables i.e. where you have declared those variables i.e. Public or Private.

To check this use MsgBox Ws.name just before the problem line that will give you an idea about what you are doing wrong.
ManuHuygheAuthor Commented:

Thanks for the fast replay.

@ Rgonzo1971,

Ws is defined as:

If Wb Is Nothing Then Set Wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Database.xlsx")
        Set Ws = Wb.Worksheets("Database")
        Set MyData = Ws.Range("A5").CurrentRegion.Offset(1)

Open in new window


1)Option explicit is not mentioned
2)i declare Ws when the database userform initialize so i didn't thought this was necessary.
3)Private Sub UserForm_Initialize()
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

2)i declare Ws when the database userform initialize so i didn't thought this was necessary.

maybe you should review where you declare your variable ( globally / locally )
ManuHuygheAuthor Commented:
Can you be a little more specific?
i'm not very expirenced in VBA.

All my codes for my database are in my database userform.
If i'm correct then when i make a public sub on a userform it can also be called from another sheet or i can create a sub in a module.

But just declaring variables so they can be used from all my worksheet, i don't know how to do.
without seeing more of the code I cannot tell
ManuHuygheAuthor Commented:
Attached you find the necessary files from the tool. The 3 files should be located in the same folder.
Thanks for the support.
ManuHuygheAuthor Commented:
In the "tool" workbook, you find the "database userform" where i declare the workbook (Wb) "Database" and the sheet (Ws) "database".
This part works as long as i stay in the "database userform", but when i move along to other userforms (for ex frmUnit) and i return then i receive the error message.

Is it possible to declare the Wb and Ws somehow that it's fix to use it from all userforms?
Because now i declared it also in frmUnit (there i need also the link to the database) but this causes also problems because the previous new input is erased as the Wb is reopend.
Rory ArchibaldCommented:
When you close the Database workbook, you should set the variable to Nothing:

Set Wb = Nothing
                    Workbooks("Database.xlsx").Close SaveChanges:=True

Open in new window

ManuHuygheAuthor Commented:
@ Rory
Why? What does it change?
Because i did it but i don't see any changes.
Rory ArchibaldCommented:
Because otherwise your variable is not set to Nothing so this line is not triggered:
If Wb Is Nothing Then Set Wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Database.xlsx")

Open in new window

which means you are trying to refer to a sheet/range in a closed workbook, which should cause an error. For me, the code actually failed on this line:
        Set Ws = Wb.Worksheets("Database")

Open in new window

rather than the one you mentioned:
        Set MyData = Ws.Range("A5").CurrentRegion.Offset(1)

Open in new window

but I suspect the reason would be the same.

Note: you need to ensure you set the variable to Nothing anywhere that you close the workbook.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.