Consulting

Results 1 to 4 of 4

Thread: Keep userform open when opening or closing other workbooks

  1. #1
    VBAX Regular
    Joined
    Oct 2019
    Posts
    8
    Location

    Keep userform open when opening or closing other workbooks

    Hi all, I'm fairly to VBA and teaching myself (mind you I'm 60) but really need some help to solve one last problem I have with my code.

    I have a workbook (Contacts) that contains three sheets of data - on opening the workbook, code runs that hides the workbook and only shows the userform. I have set the workbook to run vbModeless, so I can at anytime open any other excel workbook and create new projects etc. All of that and all the functions of the userform work perfect.

    My problem is that when I close the other workbooks, it auto shows the sheets from 'Contacts' behind the userform - something that I don't want to happen. Likewise, if I have a new project open and then open 'Contacts' it does the same thing. The Userform is the only thing that must remain open all the time, unless I physically close it and the workbook/sheets must remain hidden unless I physically call for them with code that I have on an edit button on the userform.

    Is there anything I can do that will open or close other workbooks without affecting the operation of workbook 'Contacts' - or am I dreaming that this is even possible. Believe me, I have tried many things - obviously unsuccessfully and it is starting to frustrate me no end. Any and all help will be very much appreciated.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,988
    Location
    Check out Application Events, especially:
    WorkbookActivate
    WorkbookBeforeClose

    Using Application Events requires a bit of prep code
    In the ThisWorkbook Code page
    Option Explicit
    
    Public WithEvents ThisApp As Application
     
    Private Sub Workbook_Open()
        Set ThisApp = Me.Application
          Call Sub_to_Hide_ThisWorkbook 'Needs to be separate sub, not in the Workbook_Open sub
          Call SubTo_Show_UserForm       'Needs to be separate sub, not in the Workbook_Open sub
          'The rest of your sub code
    End Sub
    
    Private Sub ThisApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
       If Not Wb Is Me Then
          Call Sub_to_Hide_ThisWorkbook
          Call SubTo_Show_UserForm      
       End If
    End Sub
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Oct 2019
    Posts
    8
    Location
    Thanks for the response SamT - but like I said - a real newbie to all of this. When you say
     needs to be in a separate sub and then the rest of the sub code
    - are you referring to it being placed in the userform sub somewhere ??

    Anyway, did that but only just kept getting compile errors or sub or function not defined errors. I get the drift of the code you have supplied, but no idea where to exactly put it - especially the
     Call Sub_to_Hide_ThisWorkbook and Call SubTo_Show_UserForm
    .

    Greatly appreciate your time and effort.
    Last edited by bj1280; 10-14-2019 at 11:01 PM.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,988
    Location
    on opening the workbook, code runs that hides the workbook and only shows the userform... - are you referring to it being placed in the userform sub somewhere ??
    Whatever code module that/those procedure(s) are in now is fine, but, you have to be able to run it/them from an Application Event procedure. That means it/they need to be in their own sub(s), or be duplicated in the Application Event Sub.

    Always place "Option Explicit" at the top of all your code pages. You should use the VBA Menu >> Tools >> Options >> Editor Tab and check all the boxes in the top Frame. This will auto place "Option Explicit" in new code pages and enable other code checking tools in VBA.

    To get help on any VBA term, place the cursor in or next to the word and press F1. Pressing F2 will bring up an interesting page that gives you a lot of information about VBA terms.

    I am 69+yo and still use Excel XP, depending on your system you may have to modify the code to work on your system.

    You might even have to go to Microsoft.com for help. DuckDuckgo is your friend. Search the internet for "Excel VBA " + the VBA term. "Excel VBA Application Event"

    Good tutorial here. Detailed tutorial here. MS Excel online help here.
    Last edited by SamT; 10-15-2019 at 05:01 AM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

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