Make INVISIBLE workbook VISIBLE again

hensleyj

New Member
Joined
Apr 2, 2012
Messages
39
Hey everyone,

When my main workbook loads, it opens a second worksheet which holds data with has visible = false.

I am trying to find a way for a macro run in my first workbook, to make the second workbook come visible again.

I can use Ctrl G, in the "immediate" window and type "'Windows("jason hensley.xlsm").Visible = True" and it comes back. But i need to allow all users to be able to press a button and it does it in the background.

The other thing is, i need it to have a dynamic range reference as the workbook name always changes. I have tried a few things using "(Sheets("Ranges").Range("L387").Value).Visible = True" but none work.
Does anyone know a way to do this? ohhh yeh, as above and below, the location of the file name is located:

Sheet: "Ranges"
File name: "L387"
And if you need it: the file path islocated: "I387"

Any help would be appreciated!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
mayb try

Code:
Workbooks(Sheets("Ranges").Range("L387").Value).Visible = True
 
Upvote 0
Unfortunately not.

The only way i can tell you can make it visible again is by forcing it to close, then when it reopens, set the visibility again.

I tried to set the file invisible with the above, and it didnt work.

I also tried to make it visible without success.

But what you had in mind is what i am trying to achieve in theory.
 
Upvote 0
How are you opening the jason hensley.xlsm file? If it's in the same instance of Excel as the macro workbook, what JLGWiz suggested should work. If it's in a separate instance of excel, it wouldn't.


You could also use this to reference the 2nd workbook in the same instance of Excel by index number instead of by name.

Workbooks(2).Visible = True
 
Upvote 0
When my main workbook loads, it opens a second worksheet which holds data with has visible = false.

Can you post the code that does this?
 
Upvote 0
A Workbook doesn't have a Visible property. You should be able to use:
Code:
Windows(Sheets("Ranges").Range("L387").Value).Visible = True
or something like
Code:
Workbooks(Sheets("Ranges").Range("L387").Value).Windows(1).Visible = True
 
Upvote 0
Thanks Rorya, your second suggestion worked like a charm!

When the main workbook opens, i run the code below which opens the second workbook invisible.

Your code helps me make it visible again.

I need 2 more things to make this all come together properly. I need 2 more lines of code to make the sheet invisible again.

- 1st code: needs to be run from the sheet that was invisible to make it invisible again. This line is to be added to a macro inside the invisible workbook, which adds a line of info to the sheet. It needs to make it invisible again after adding the line.
- 2nd code: needs to be run from the main sheet to make the second sheet invisible again. this option is when no info is being added to the second sheet, but just for the purposes of viewing it.

They wont be run at the same time, they are required for 2 different steps.

I am concerned using the "activewindow.visible = false" option, if by some accident the user switches or another window becomes active, and it makes it invisible by selection rather than making it invisible in accordance with its filename.

I have not used dynamic references and the visibility options before, its all quite new to me. So you are helping me greatly, appreciate it!

Code:
sub TDBenefits_click()
    
    Application.ScreenUpdating = False    Me.Hide
    'Sheets("TD Payment Calculator").Visible = True
    Sheets("TD Payment Calculator").Select
    Range("G5").Select
    
    Dim myworkbook As Workbook
    Set myworkbook = WORKBOOKS.Open(Filename:=Sheets("Ranges").Range("I387").Value & "\" & Sheets("Ranges").Range("K387").Value)
    ActiveWindow.Visible = False
    Application.ScreenUpdating = True

end sub
 
Upvote 0
Never mind, using what was provided i managed to get the workbooks disappearing and reappearing from view perfectly with the visible line.

THANKS EVERYONE for your suggestions.

Thanks Rorya, your second suggestion worked like a charm!

When the main workbook opens, i run the code below which opens the second workbook invisible.

Your code helps me make it visible again.

I need 2 more things to make this all come together properly. I need 2 more lines of code to make the sheet invisible again.

- 1st code: needs to be run from the sheet that was invisible to make it invisible again. This line is to be added to a macro inside the invisible workbook, which adds a line of info to the sheet. It needs to make it invisible again after adding the line.
- 2nd code: needs to be run from the main sheet to make the second sheet invisible again. this option is when no info is being added to the second sheet, but just for the purposes of viewing it.

They wont be run at the same time, they are required for 2 different steps.

I am concerned using the "activewindow.visible = false" option, if by some accident the user switches or another window becomes active, and it makes it invisible by selection rather than making it invisible in accordance with its filename.

I have not used dynamic references and the visibility options before, its all quite new to me. So you are helping me greatly, appreciate it!

Code:
sub TDBenefits_click()
    
    Application.ScreenUpdating = False    Me.Hide
    'Sheets("TD Payment Calculator").Visible = True
    Sheets("TD Payment Calculator").Select
    Range("G5").Select
    
    Dim myworkbook As Workbook
    Set myworkbook = WORKBOOKS.Open(Filename:=Sheets("Ranges").Range("I387").Value & "\" & Sheets("Ranges").Range("K387").Value)
    ActiveWindow.Visible = False
    Application.ScreenUpdating = True

end sub
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top