Solved

Windows 7 > Windows Explorer > Preview of Excel Workbook

Posted on 2013-01-15
15
1,014 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

Question has a verified solution.

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

How to record audio from input sources to your PC – connected devices, connected preamp to record vinyl discs, streaming media, that play through your audio card: Vista, Windows 7, Windows 8, Windows 8.1 and Windows 10 – both 32 bit & 64.
An article on effective troubleshooting
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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