Make INVISIBLE workbook VISIBLE again

[hensleyj]

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!

 

[JLGWhiz]

mayb try

Workbooks(Sheets("Ranges").Range("L387").Value).Visible = True

 

[hensleyj]

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.

 

[AlphaFrog]

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

 

[JLGWhiz]

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?

 

[RoryA]

A Workbook doesn't have a Visible property. You should be able to use:

Windows(Sheets("Ranges").Range("L387").Value).Visible = True

or something like

Workbooks(Sheets("Ranges").Range("L387").Value).Windows(1).Visible = True

 

[hensleyj]

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!

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

 

[hensleyj]

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!

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