Solved

Windows 7 > Windows Explorer > Preview of Excel Workbook

Posted on 2013-01-15
15
980 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
Want to promote your upcoming event?

Attending an event? Speaking at a conference? Or exhibiting at a tradeshow? Easily inform your contacts by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

15 Experts available now in Live!

Get 1:1 Help Now