Keep userform open when opening or closing other workbooks

[bj1280]

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.

[SamT]

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

[bj1280]

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.

[SamT]

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.