Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Jon,
Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? Actually, no there isn't so you can stop looking. One workaround is to make all sheets but one visible when saving the workbook (rendeiring it useless) and on that sheet place a notice that macros need to be enabled in order to use it. If macros are enabled when it's opened, that notice sheet will hide itself and the other sheets will be made visible. Attached below is some code and instructions on how to do this: (Please make note that Excel is not a secure environment so if you're using this to protect the workbook, it can be circumvented by an experienced user) John <snip The only way to do this is to hide all the sheets except one when the workbook is saved (making the workbook unusable). On that one unhidden sheet, you'll need to place some text alerting the user that they need to enable macros and instruction on how to do so. You'll also need to protect the VBA project so they can't easily get at the underlying code. Please be aware though that an experienced user can crack any VBA code that you have or make the sheets that you've hidden, visible again even without getting into your VBA code. Having said that, here's some code that will attempt to do what you want. Try this in a brandy new workbook first to see how it works and then adapt the code to fit your needs (based on how your sheets are laid out) How it works: On Close, nothing happens (except the close) because the stored copy already has all but sheet(1) hidden. If the user saves the workbook... His/her last active cell address is saved in a memvar The sub to hide all but the first sheet is activated. By default, this activates sheet(1) firing the code there. There's a one second timer in the sheet activation code (allowing the actual save to take place with all but the first sheet hidden). After one second the OpenWorkbook sub is called taking the user to the sheet he/she was last on. The last cell on that sheet that was active is now selected. '***** Workbook Code***** Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Remember where the user was WhereWasI = ActiveCell.Address ' Call sub to hide all but the first sheet HidealmostAll End Sub Private Sub Workbook_Open() ' Prevent Sheet1 Activate code from firing Application.EnableEvents = False ' Call opening ceremonies sub OpenWorkbook Application.EnableEvents = True End Sub '***** Sheet(1) Code ****** Option Explicit Private Sub Worksheet_Activate() Application.OnTime Now + TimeValue("00:00:01"), "OpenWorkbook" End Sub '****Module1 Code ***** Option Explicit Public WhereWasI As String Sub HidealmostAll() ' Insure Sheet(1) is Visible Sheets(1).Visible = True Dim a As Integer ' Hide all the other sheets For a = 2 To Sheets.Count Sheets(a).Visible = xlVeryHidden Next a End Sub Sub OpenWorkbook() Dim a As Integer ' Display all the other sheets For a = 2 To Sheets.Count Sheets(a).Visible = True Next a Sheets(1).Visible = xlVeryHidden If WhereWasI = "" Then Exit Sub Range(WhereWasI).Activate End Sub <snip Jon wrote: Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Jon,
You probably have formatted or otherwise sued some rows below or columns to the right of your data. On the bloated workbook, select the sheets individually and press Ctrl + End. If the last row and column is well below where you think it should be, you'll need to delete the empty rows and columns. Check here for an example of how to do this both manually and with VBA: http://www.contextures.com/xlfaqApp.html#Unused John Jon wrote: Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Tom,
The best you can do is conceal enough of your workbook Which should be done in the Workbook.Before_Save Event. That way it insures that (if macros were enabled when it was opened in the first place) that it would always be saved with the selected sheets hidden. There is no foolproof way to do it. Agreed John Tom Ogilvy wrote: No. The best you can do is conceal enough of your workbook (which is restored by a workbook_open event macro) so that if macros are not enabled, the workbook is not very usable. There is no foolproof way to do it. You could put your macro code in an addin and link (create a reference in the VBE) the workbook to the addin. Regards, Tom Ogilvy Jon wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Thanks guys...no wonder it was driving me nuts...looks like this scenario
was not anticipated when MS added security to Office. I'm kind of surprised though as it seems that security ought to be a 2 way street i.e. the user is free to decline the running of code in the Excel environment but the developer should also have a way of stopping the user from operating the Excel spreadsheet in a way that was not intended (in the absence of macros). I have several sophisticated Excel apps that I have developed and it bothers me that many users may inadvertantly get themselves into trouble by declining macros. Actually I imagine that in some situations this could cause the applications to generate false output, and there would be no way to tell. Not good. Jon PS You guys are fast too, thanks for the quick answers. I can go to bed now without feeling like I'm missing something obvious at least!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Haha...don't sweat it. Looks like we all get "code" disfunctionality at
night. Excle troubles or no Excel troubles! Take care...I'm going to bed. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Another idea would be to put all the good stuff in a different workbook, and
then put just enough code in the "first" workbook that if they have macros turned on, then it will open the second workbook with all of the stuff in it. At least this way, they wouldn't have a "dead" copy which they might tinker with. A little better control plus you don't have to do so much hiding and stuff. -- HTH Richard Choate, CPA "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Jon,
Its a Catch 22 situation. What're you gonna use to stop the user opening the workbook... a macro? But the user has just disabled macros!!! ATB Henry "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Richard,
Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. Whether there's one workbook or two doesn't really matter. If the user is experienced enough to crack the VBA password (which isn't all that difficult), I'm sure he/she would easily be able to find the name, path and password to the second workbook within the code. Attempting to use any home made protection scheme is nothing more than an exercise in futility. I'm guessing from the OP's post (though he didn't make it crystal clear) that he had a workbook that depended on macros to operate correctly and wanted to insure that they were enabled. I didn't get the impression that he wanted to protect it from anyone. If that was his intent, the "hide all sheets except one" should work rather well. John "R. Choate" wrote: Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. That is at least more secure than having all the goodies right there in the file with the dead macros. -- RMC,CPA "Henry" wrote in message ... Jon, Its a Catch 22 situation. What're you gonna use to stop the user opening the workbook... a macro? But the user has just disabled macros!!! ATB Henry "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
John,
All decent points. However, I think it is easier to secure a file that a user isn't already in than one which is already open. First, the security of a password to open a file, as I understand it, is much tougher than the security of passwords on code or on sheets. That is, it would take a much better expert to crack that PW. Also, the 2nd file could be placed in a secure directory. Again, another layer. You could be clever for additional security with the 2nd file option. Therefore, I could not agree that it is equally futile. However, your point about the OP not mentioning security problems is quite valid and I am guilty of over-discussing the security issue for this post. I'll end it here. -- RMC,CPA "John Wilson" wrote in message ... Richard, Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. Whether there's one workbook or two doesn't really matter. If the user is experienced enough to crack the VBA password (which isn't all that difficult), I'm sure he/she would easily be able to find the name, path and password to the second workbook within the code. Attempting to use any home made protection scheme is nothing more than an exercise in futility. I'm guessing from the OP's post (though he didn't make it crystal clear) that he had a workbook that depended on macros to operate correctly and wanted to insure that they were enabled. I didn't get the impression that he wanted to protect it from anyone. If that was his intent, the "hide all sheets except one" should work rather well. John "R. Choate" wrote: Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. That is at least more secure than having all the goodies right there in the file with the dead macros. -- RMC,CPA "Henry" wrote in message ... Jon, Its a Catch 22 situation. What're you gonna use to stop the user opening the workbook... a macro? But the user has just disabled macros!!! ATB Henry "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
Richard,
the security of a password to open a file, as I understand it, is much tougher than the security of passwords on code or on sheets. Very true. Most of the password crackers that you can buy to break that password use brute force and can take a few days to accomplish their task depending on the password. That is, it would take a much better expert to crack that PW. That it would ($45.00 to www.lostpasswords.com) Also, the 2nd file could be placed in a secure directory. In your scenario though, that second file is opened via VBA from the first file. In order to do that, the password and any necessary code to get to that file and open it would have to be in the VBA coding of the first file. Least common denominator is the VBA in the first file. Once that's cracked and you can see the code, the rest is child's play. Therefore, I could not agree that it is equally futile. The extent of futility depends on the knowledge and experience of the user that you're trying to protect it from. Granted, there are many who don't even know what a macro is and for them, protecting a workbook and VBA code would probably work well. I'm certain that most, if not all, of the regular contributors to the ng's would have no problem at all with whatever protection scheme that you or I or anyone else could come with. The point of all of this is that while protecting a workbook or the VBA coding is not secure, many people still do ask for ways to do it. Whenever I answer one of these posts, I always make it a point to let the OP know how vulnerable it is. If they still decide to use whatever I give them, it's their choice. To give the OP the impression that there *is* a way within Excel to make their work safe and secure from prying eyes would be to mislead them. John "R. Choate" wrote: John, All decent points. However, I think it is easier to secure a file that a user isn't already in than one which is already open. First, the security of a password to open a file, as I understand it, is much tougher than the security of passwords on code or on sheets. That is, it would take a much better expert to crack that PW. Also, the 2nd file could be placed in a secure directory. Again, another layer. You could be clever for additional security with the 2nd file option. Therefore, I could not agree that it is equally futile. However, your point about the OP not mentioning security problems is quite valid and I am guilty of over-discussing the security issue for this post. I'll end it here. -- RMC,CPA "John Wilson" wrote in message ... Richard, Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. Whether there's one workbook or two doesn't really matter. If the user is experienced enough to crack the VBA password (which isn't all that difficult), I'm sure he/she would easily be able to find the name, path and password to the second workbook within the code. Attempting to use any home made protection scheme is nothing more than an exercise in futility. I'm guessing from the OP's post (though he didn't make it crystal clear) that he had a workbook that depended on macros to operate correctly and wanted to insure that they were enabled. I didn't get the impression that he wanted to protect it from anyone. If that was his intent, the "hide all sheets except one" should work rather well. John "R. Choate" wrote: Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. That is at least more secure than having all the goodies right there in the file with the dead macros. -- RMC,CPA "Henry" wrote in message ... Jon, Its a Catch 22 situation. What're you gonna use to stop the user opening the workbook... a macro? But the user has just disabled macros!!! ATB Henry "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
I got an Excel Macro/Security question that is driving me nuts.....
John, good points as always. Can't really argue with most of them and I
enjoyed the discussion. I also look forward to meeting with you on other posts. Thanks for your time and knowledge. -- RMC,CPA "John Wilson" wrote in message ... Richard, the security of a password to open a file, as I understand it, is much tougher than the security of passwords on code or on sheets. Very true. Most of the password crackers that you can buy to break that password use brute force and can take a few days to accomplish their task depending on the password. That is, it would take a much better expert to crack that PW. That it would ($45.00 to www.lostpasswords.com) Also, the 2nd file could be placed in a secure directory. In your scenario though, that second file is opened via VBA from the first file. In order to do that, the password and any necessary code to get to that file and open it would have to be in the VBA coding of the first file. Least common denominator is the VBA in the first file. Once that's cracked and you can see the code, the rest is child's play. Therefore, I could not agree that it is equally futile. The extent of futility depends on the knowledge and experience of the user that you're trying to protect it from. Granted, there are many who don't even know what a macro is and for them, protecting a workbook and VBA code would probably work well. I'm certain that most, if not all, of the regular contributors to the ng's would have no problem at all with whatever protection scheme that you or I or anyone else could come with. The point of all of this is that while protecting a workbook or the VBA coding is not secure, many people still do ask for ways to do it. Whenever I answer one of these posts, I always make it a point to let the OP know how vulnerable it is. If they still decide to use whatever I give them, it's their choice. To give the OP the impression that there *is* a way within Excel to make their work safe and secure from prying eyes would be to mislead them. John "R. Choate" wrote: John, All decent points. However, I think it is easier to secure a file that a user isn't already in than one which is already open. First, the security of a password to open a file, as I understand it, is much tougher than the security of passwords on code or on sheets. That is, it would take a much better expert to crack that PW. Also, the 2nd file could be placed in a secure directory. Again, another layer. You could be clever for additional security with the 2nd file option. Therefore, I could not agree that it is equally futile. However, your point about the OP not mentioning security problems is quite valid and I am guilty of over-discussing the security issue for this post. I'll end it here. -- RMC,CPA "John Wilson" wrote in message ... Richard, Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. Whether there's one workbook or two doesn't really matter. If the user is experienced enough to crack the VBA password (which isn't all that difficult), I'm sure he/she would easily be able to find the name, path and password to the second workbook within the code. Attempting to use any home made protection scheme is nothing more than an exercise in futility. I'm guessing from the OP's post (though he didn't make it crystal clear) that he had a workbook that depended on macros to operate correctly and wanted to insure that they were enabled. I didn't get the impression that he wanted to protect it from anyone. If that was his intent, the "hide all sheets except one" should work rather well. John "R. Choate" wrote: Well, if the user doesn't know where the 2nd workbook is, or what the name of it is, or what the PW is, then those things might keep the user out. That is at least more secure than having all the goodies right there in the file with the dead macros. -- RMC,CPA "Henry" wrote in message ... Jon, Its a Catch 22 situation. What're you gonna use to stop the user opening the workbook... a macro? But the user has just disabled macros!!! ATB Henry "Jon" wrote in message ... Is there a way to allow a user to open a workbook ONLY if they have allowed the macros to execute? I've searched and searched for a proper setting for this but haven't been able to come up with squat yet. I know that there has to be a way.....someone help, please before I lose the rest of my mind......... Thank you. Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A Really Stupid Obvious Question that is Driving Me Nuts | Excel Discussion (Misc queries) | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
question driving me nuts | Excel Discussion (Misc queries) | |||
Excel / VB is driving me nuts!! | Excel Worksheet Functions |