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

Reetesh

New Member
Joined
Sep 6, 2020
Messages
31
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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."
 
L

Legacy 456155

Guest
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.

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

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Reetesh

New Member
Joined
Sep 6, 2020
Messages
31
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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".
 
L

Legacy 456155

Guest
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

New Member
Joined
Sep 6, 2020
Messages
31
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
That would be really great man... Thanks for all your help again.
 
L

Legacy 456155

Guest

ADVERTISEMENT

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

New Member
Joined
Sep 6, 2020
Messages
31
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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"?
 
L

Legacy 456155

Guest
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".

VBA Code:
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

1599502052629.png


See the codename of the worksheet named "List" ?
 

Reetesh

New Member
Joined
Sep 6, 2020
Messages
31
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,718
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top