Excel / Visual Basic 7: (vbmodeless) issues. My brain is melting trying to find a fix

[nizzlebizzle]

Thank you everyone, now resolved*****

Hello everyone,

I'm new here, I've read the rules, but please inform/forgive me if I have missed anything or posted incorrectly.

Now, to my problem:

I have created my first form in Visual Basic that has allowed me to create a searchable and editable database.

Upon allowing colleagues to test the form, I quickly released that they cannot use any of their other Excel workbooks that they have open (The other workbooks are not related in any way to my workbook/form).

After some researching, I found that I can add (vbmodeless) to the button that opens the form. This then allows the team to flick between the form and use other workbooks to look at other information and then go back onto the form.

The only problem I have now, is that when they go back to the form, it reads an error in the macros, because its trying to use the macros on another workbook and not my workbook.

In short, is there any way you can use (vbmodeless), but lock the forms Macros to my specific workbook?

If anyone can solve this riddle, they will be my best friend. I'm aiming to have this ready for its presentation next week on the 6th of March.

Thank you.

[jdc2000]

Post some sample code that you are using.

It should be possible to have the macros check for and specify the Workbook and Sheet names to run on, provided that you are using unique names and not the generic names like Sheet1, etc.

[vbfbryce]

You can dim a workbook object, then set it to "thisworkbook," then use it in your code.

Dim wb As Workbook
    
    Set wb = ThisWorkbook
    
    with wb
    '.....do whatever
end with

In my example, this code is in the user form.

[nizzlebizzle]

Post some sample code that you are using.

It should be possible to have the macros check for and specify the Workbook and Sheet names to run on, provided that you are using unique names and not the generic names like Sheet1, etc.

JDC2000, Thank you so much. That's exactly the problem. When I saw 'workbook' in your comment, I realised....

In all of my references to the location, I was only referencing the worksheet, not the workbook. I've now amended it as follows:

TRows = Workbooks("RBACCreationDB").Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

Thank you for your help

[nizzlebizzle]

Thank you for taking the time to help. I appreciate it.