VBA to avoid other Excel Instance/workbooks from closing when “Application.visible=False”

[Reetesh]

Hello.
I'm fairly new to VBA and need help with this small problem.

I've an excel workbook which has a Userform in it. Whenever the userform is active it hides the workbook on which the userform is. To do this I've used "Application.visible=False". Now the problem that i'm facing is other workbooks are getting closed as well, which were already open before, whenever the Userform is active. I've tried using "Windows("workbook.xls").Visible = False", however it stops some of the functions on Userform and 'Thisworkbook.Application.visible=False" & "Workbooks("workbook.xls").Application.visible=False" on the workbook in which the userform is, however its still the same. It closes all the workbook which were already open. I've tried to use "Sheets("Home").Visible = xlVeryHidden" however its giving the "Runtime Error 1004: Unable to set the visible property of the Worksheet class."

 

[Legacy 456155]

I found another post here that shed some light.

Use...
[Tracker.xls]List!Table
...as your rowsource.

You may want to establish your rowsource via code in case the workbook or worksheet names change.

lstActivityName.RowSource = "[" & ThisWorkbook.Name & "]" & Sheet4.Name & "!Table"

 

[Reetesh]

Sorry for being a pain again. But do you want me to add another module so that the combo box establish the rowsource through that module?
If yes, could you help me with the code on how the combo box will read the module so that it displays the options from the "Table" which is there on "List" worksheet.
Because when i used the VBA code mentioned above it gives "Runtime Error '424': Object Required".

 

[Legacy 456155]

No pain. I suppose that I would put that line of code in the userform's initialize event.
In the example you uploaded, Sheet!List has a codename = "Sheet4". That is what the code is referring to. I you refer to the codename, the code will continue to function correctly even if the worksheet is renamed.

 

[Reetesh]

That would be really great man... Thanks for all your help again.

 

[Legacy 456155]

That would be really great man...

What would really be great? I'm not sure I am understanding you. Are you wanting me to edit the example you uploaded?

 

[Reetesh]

I'm really sorry.. i thought you said that you'll be updating the code on the workbook itself which i uploaded on the drive which you shared earlier.
So what exactly should i do you are saying?
In this code which you mentioned earlier
"lstActivityName.RowSource = "[" & ThisWorkbook.Name & "]" & Sheet4.Name & "!Table"
Should i keep "Sheet4.Name" as just "Sheet4"?

 

[Legacy 456155]

That's ok. We'll get there. Sheet4 is the CodeName. The friendly name or tab name is "List". It's better to use the codename in your code because the user cannot, or should not, change it.

As for -> lstActivityName.RowSource = "[" & ThisWorkbook.Name & "]" & Sheet4.Name & "!Table"
This line of code should go in the userform named "Trackers".

Private Sub UserForm_Initialize()
         
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    On Error GoTo ErrorHandler
 
    Me.txtName = VBA.UCase(Environ("Username"))
        Me.cmdLogout.Enabled = False
        Me.cmdStart.Enabled = False
        Me.cmdEnd.Enabled = False
        Me.CommandButton2.Enabled = False
    'If unexpected log out

    lstActivityName.RowSource = "[" & ThisWorkbook.Name & "]" & Sheet4.Name & "!Table"
 
ErrorHandler:
 
    Call Load_Incompleted_Activity
    Call Load_Incompleted_Activity2
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub
 
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False

End Sub

See the codename of the worksheet named "List" ?

 

[Reetesh]

Thank you so much dataluver. It works like a charm and doing exactly what i want.
None of the workbooks which were open earlier are getting hidden when the Userform is active, and it now only hides the workbook through which the userform is accessed.
I've been trying to sort this out for quite some time and scratched my head a lot just to sort this out.
Thanks a lot for you help again mate. appreciated