Hide workbook

[Juriemagic]

Hi,

I have posted last week onMrExcel...here is the link: http://www.mrexcel.com/forum/excel-questions/879024-hide-workbook.html

I have searched VBA Express but did not find what I am after. I was just wondering: I have a workbook which opens via macro. This workbook opens in it's own instance. To prevent users from just opening the book straight away, I set the workbook property to hidden. Now, this can easily be overridden. Is there a way to "permanently" hide the workbook, so that it can only be called up with a code?...and if there is, can it be made un-hidden, but also only by code perhaps?...I appreciate all and any assistance..Thank you all...

[snb]

either


Sub M_snb()
getobject "G:\OF\example.xlsx"
end sub
or

Sub M_snb()
with createobject("excel.Appplication"
with .workbooks.open("G:\OF\example.xlsx")

end with
end with
end sub

[Juriemagic]

Hi snb,

I see these codes call the hidden workbook. I fail to see how this "permanently" hides the workbook so that it cannot be opened directly, but only via code?..maybe I just don't understand the above?..

[snb]

No magic, Jurie.

Save the file and try to open it without code.


Sub M_snb()
getobject("G:\OF\example.xlsx").close -1
End Sub

[Paul_Hossler]

If you can have macros in the hidden WB, you can add this to the WB open to close immediately, unless it's call by the 'Master Macro'




Option Explicit

Private Sub Workbook_Open()

If GetSetting("MyApp", "Check", "OkToOpen") <> "YES" Then
ThisWorkbook.Close (False)
Else
ActiveWindow.Visible = False
ThisWorkbook.Saved = True
End If
End Sub





The 'Opening WB' macro would look something like this



Option Explicit

Sub OpenHiddenWorkbook()

Call SaveSetting("MyApp", "Check", "OkToOpen", "YES")
Workbooks.Open Filename:="C:\Users\USERNAME\Desktop\TheHiddenWorkbook.xlsm"
Call SaveSetting("MyApp", "Check", "OkToOpen", "NO")
End Sub

[Juriemagic]

Hello snb,

okay, I accept that I am extremely daft. I have saved your code AS IS in the "This Workbook" of the file, closed it, and opened it directly without any problems, I also changed the path to the correct path for the book, but still opened it...I am sorry, but I do not get this one...

[Juriemagic]

Good morning Paul,

This code is sooooooo close, when I click the button, I can see in the taskbar the 2nd workbook opening, but it closes immediately. I am quite happy with how the workbook behaves when you try to open it directly...the application opens, but no workbook. so, it's just the macro to call the workbook that maybe has a little something missing?

[Paul_Hossler]

The easiest thing to try would be to turn off screen updating. I 'think' that there are some other ways also. If you don't like this, I'll look around

Also try saving the Hiddne WB as Hidden and see it that helps





Sub OpenHiddenWorkbook()

Call SaveSetting("MyApp", "Check", "OkToOpen", "YES")
Application.ScreenUpdating=False
Workbooks.Open Filename:="C:\Users\USERNAME\Desktop\TheHiddenWorkbook.xlsm"
Application.ScreenUpdating=True
Call SaveSetting("MyApp", "Check", "OkToOpen", "NO")

End Sub

[Juriemagic]

UUGGGHHHH...sorry..just me..

Nope, the book does not open. I have tried so many variations of the "yes's" and "no's"..."true's" and "false"s"..it's not even funny. I have also very extensively searched the internet for explanations on "getsettings" etc, since this is a first for me to work with that. Please, Paul_Hossler, see if you can help me with this...this will be the final of "build-ins" to finish of my project....

[snb]

To keep it simple:


Sub M_snb()
With GetObject("G:\OF\example.xlsx")
.Save
.Close 0
End With
Workbooks.Open "G:\OF\example.xlsx"
End Sub