• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

Windows 7 > Windows Explorer > Preview of Excel Workbook

Hi,

Is there an excel vba code that will render an Excel Workbook invisible when viewed from the Windows Explorer ( in Windows 7 ) i.e. I would like to lock the Application down so that:
a) when the workbook is fired up a userform will be fired up to guide the users, and
b) when previewed from Windows Explorer ( WIndows 7 ) the users cannot view the contents in the workbook/ worksheets.

I have been able to hide all worksheets except one lead sheet ( say Sheet1 ) as well as incorporating a Workbook Open Event where a userform fires up when the excel workbook is opened. However, I am still able to  view the lead sheet, Sheet1 from the Windows Explorer.  How do I prevent users from previewing the contents of the workbook?

Thanks
0
Russellbrown
Asked:
Russellbrown
  • 6
  • 6
  • 3
4 Solutions
 
gowflowCommented:
post what you hv done so far and will guide u
gowflow
0
 
redmondbCommented:
Hi, Russellbrown.

Please see attached. (Just hit Cancel on the form.)

Please note that it is easy for an even slightly knowledgeable user to get full access to the file. But that's true for all pure Excel options. (Well, a password-protected Add-in would take a bit more effort.)

Regards,
Brian.Hidden.xlsm
0
 
gowflowCommented:
I think this is what you want to see instead.
Just activate macroes

the two buttons are just there no code behind.
gowflow
ShowForm.xlsm
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
redmondbCommented:
Very nice, gowflow, I hadn't come across "Application.Visible = True" being used before.

Just for fun, the attached...
 - Doesn't close Excel.
 - The Form gives the user the option of
   (A) Restoring Excel, while continuing to run.
   (B) Restoring Excel and terminating.

Regards,
Brian.
ShowForm-V2.xlsm
0
 
gowflowCommented:
for sure we can put as many options as we want but asker's post was based on 2 issues:
1 - show the form (ONLY) and hide the excel
2 - if the workbook is previewed (in window explorer if you click on the workbook is show you a preview of it so he wanted to hide the worksheets so this is what the beforeclose hidding the sheets ensure that when the book is closed no sheets are shown.

Rgds/gowflow
0
 
redmondbCommented:
No apologies, gowflow, fun is too important!
0
 
gowflowCommented:
do not understand ur comment who is talking about appologies ?
gowflow
0
 
redmondbCommented:
Failed humour, gowflow, don't worry about it.
0
 
gowflowCommented:
yes failed humor.
0
 
RussellbrownAuthor Commented:
Hi both,

Wasn't able to revert earlier as I was on the road. I like the part where only the form is visible and not the rest of the workbook. The solutions are very helpful. Thank you.

However on the preview part, it works well only in xlsm format but not in xls format. I will have to resort to a workaround where I will hide all the worksheets and leaving a blank worksheet. This way viewers cannot preview the contents from the Windows Explorer.
0
 
redmondbCommented:
Thanks, Russellbrown.

Yes, I'm seeing the same issue with xls v. xlsm. Why must your users use xls?

Thanks,
Brian.
0
 
gowflowCommented:
Well no need to hid all the worksheet just the instruction that I had there I will redist the code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Cover").Activate
Application.ActiveWindow.WindowState = xlMinimized
Application.ActiveWindow.DisplayWorkbookTabs = False
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.ActiveWindow.DisplayWorkbookTabs = False
Application.ActiveWindow.WindowState = xlMinimized
Application.WindowState = xlMinimized
Application.Visible = False
UserForm1.Show 0
End Sub

Open in new window


Now as it is now yest You will need 1 worksheet that you can call Cover that is the default and that popsup in first like in the attached.
ShowForm.xls
0
 
RussellbrownAuthor Commented:
Dear gowflow,

Very nice indeed! Much appreciated.

Dear Brian,

It is the lowest common denominated and some users are still in the Excel 2003 mode.

Once again thank you both very much for the great replies.
0
 
redmondbCommented:
russelbrown,

It is the lowest common denominated and some users are still in the Excel 2003 mode.
By installing Microsoft's Office Compatibility Pack, Office 2003 users can access the newer file formats. See here for more details.

Brian.
0
 
RussellbrownAuthor Commented:
Both experts were wonderful and very helpful in responding to my questions.Thank you.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now