Solved

Windows 7 > Windows Explorer > Preview of Excel Workbook

Posted on 2013-01-15
15
1,027 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
15 Comments
 
LVL 30

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 30

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 30

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 30

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 30

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 30

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

696 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