dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    3

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

    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.
    Last edited by nizzlebizzle; Feb 27th, 2018 at 01:42 PM. Reason: Resolved.

  2. #2
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,104

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

    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.

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,480

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

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

    Code:
    Dim wb As Workbook
        
        Set wb = ThisWorkbook
        
        with wb
        '.....do whatever
    end with
    In my example, this code is in the user form.

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    3

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

    Quote Originally Posted by jdc2000 View Post
    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    3

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.