Solved

Windows 7 > Windows Explorer > Preview of Excel Workbook

Posted on 2013-01-15
15
989 Views
Last Modified: 2013-01-23
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
Comment
Question by:Russellbrown
  • 6
  • 6
  • 3
15 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38781755
post what you hv done so far and will guide u
gowflow
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 135 total points
ID: 38782262
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
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 365 total points
ID: 38782531
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38785155
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38785743
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38785751
No apologies, gowflow, fun is too important!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38786080
do not understand ur comment who is talking about appologies ?
gowflow
0
Why spend so long doing email signature updates?

Do you spend loads of your time carrying out email signature updates? Not very interesting are they? Don’t let signature updates get you down. Let Exclaimer Cloud - Signatures for Office 365 make managing email signatures a breeze.

 
LVL 26

Expert Comment

by:redmondb
ID: 38786095
Failed humour, gowflow, don't worry about it.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38786110
yes failed humor.
0
 

Author Comment

by:Russellbrown
ID: 38787078
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38787169
Thanks, Russellbrown.

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

Thanks,
Brian.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 365 total points
ID: 38787251
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
 

Assisted Solution

by:Russellbrown
Russellbrown earned 0 total points
ID: 38793063
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38793901
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
 

Author Closing Comment

by:Russellbrown
ID: 38809121
Both experts were wonderful and very helpful in responding to my questions.Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now