 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007VBA Project - Second opinion please!
Hi, I'd like to open my VBA (protected by a password), delete and replace a module (macro, courtesy of Mr. Chip Pearson's website), protect the vba project with the same password, save the file and exit. When I posted to request the assistance to write a macro for the above project, I was informed that one CANNOT open and close the VBA project with a macro. It's hard to believe that with the sophistication of the program, a macro command for the above project is not available. Thank you. I hate to burst your bubble, but you just can't if there is a password on the file. There is nothing in the code that lets you get around a password. go into VBA and press F2, select VBIDE for your library and search around for anything relating to password access. It doesn't exist. The most it will let you do is see if the file is protected...  SignatureJNW > Hi, > [quoted text clipped - 11 lines] > > Thank you. I'm sorry I did not make myself clear. I know the password because its my own workbook. So, can a macro be written for it? Thanks again. > I hate to burst your bubble, but you just can't if there is a password on the > file. There is nothing in the code that lets you get around a password. go [quoted text clipped - 17 lines] > > > > Thank you. There have been posts that suggest that using Sendkeys to go through the menu system and enter the password, but that's not a robust technique. I'd never use it in anything that I needed to depend on (or give to others). > I'm sorry I did not make myself clear. I know the password because its my own > workbook. [quoted text clipped - 26 lines] > > > > > > Thank you. SignatureDave Peterson Hi Dave, The reason why I need it is, I let my officemates use my workbook. In order for them to print is, they have to have a "printing password" that I provide. They usually print about 26 worksheets. Everytime they have to print a worksheet, they have to use the password. I'd like to change the Before Print Event (from Mr. Chip Pearson's website) to "Cancel = False", but is limited to print 30 times so, they won't have to use the "printing password" everytime. I am only giving the "printing password" not the "VBA Password" to other users. Thank you. End Sub > There have been posts that suggest that using Sendkeys to go through the menu > system and enter the password, but that's not a robust technique. [quoted text clipped - 31 lines] > > > > > > > > Thank you. I wouldn't expect most users feel comfortable inside the VBE modifying code. I don't quite understand what you're doing, but if you allow the users to break the rule about number of hardcopies, why make it difficult? Why not just let them do it without any interference? > Hi Dave, > [quoted text clipped - 57 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, I'm sorry that I have not explained myseld clearly. I came up with a loan packaging workbook template with the help of individuals including yourself in this NG. Every now and then, I let my officemates use my workbook. I protected the workbook from printing. When they are done with the loan package, I give them the "printing password" so they print a maximum of 30 times. However, due to the "printing password", everytime they click print they are always asked for the password. To solve this problem I came with the idea that when the user has the correct "printing password", the macro I'm asking will open the vba project, delete the existing before print event and change it with a new one. When the new before print event is in place, the user will no longer be prompted to key in the "printing password". Of course, the user will still be limited to print 30x. On Mr. Chip Pearson's website, one cannot delete the VBA if its protected. The macro I'm asking will include my "VBA project password" so it can be opened and saved. Thank you. > I wouldn't expect most users feel comfortable inside the VBE modifying code. > [quoted text clipped - 64 lines] > > > > > > Dave Peterson There is still no good way to have a macro unprotect that project (and reprotect it). Maybe you could write your code differently so that if the user answers with the correct password (once in that session), then they aren't prompted again. Option Explicit Dim OkToPrint As Boolean 'initial value of false Dim pCtr As Long 'initial value of 0 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim myPWD As String Dim UserPWD As String myPWD = "hi" If pCtr > 30 Then OkToPrint = False Else If OkToPrint = True Then 'don't ask again Else 'ask UserPWD = InputBox(Prompt:="Enter the password") OkToPrint = CBool(UserPWD = myPWD) End If End If If OkToPrint Then 'add one to the print counter pCtr = pCtr + 1 Else Cancel = True MsgBox "Too many prints or not correct password" End If End Sub Remember that print preview will count toward the total, too. === Personally, I wouldn't bother with this. This kind of code just makes it more difficult for the user--and they'll find a way around it. Either by disabling macros or disabling events or by just closing and reopening the workbook. And I think it just causes bad feelings among co-workers. > Hi Dave, > [quoted text clipped - 94 lines] > > > > Dave Peterson SignatureDave Peterson Thank you Mr. Peterson. Your macro worked perfectly! My workbook disables pop-up menu, save the workbook everytime you click and other things to protect it. However, I'm aware that somebody can find a way to go around it if they want to. My co-workers understand why I'm doing it. We are hoping that management will buy us a loan packaging software (a real one). Going back to my original question, you said that, "There is still no good way to have a macro unprotect that project (and reprotect it)." Why? I thought that with the sophistication of the program, everything can be done. Thanks again for your time and effort. Hava a nice day! > There is still no good way to have a macro unprotect that project (and reprotect > it). [quoted text clipped - 143 lines] > > > > > > Dave Peterson You may have protected the workbook's structure or a worksheet (via tools|Protection), but you didn't protect the project (inside the VBE, Tools|VBAProject Properties tab|Protection). And if you could unlock that project, then anyone who could open your workbook could try to crack your project's password--(with enough time and determination, that is). Just start a macro, go on vacation and come back when it's cracked. > Thank you Mr. Peterson. Your macro worked perfectly! > [quoted text clipped - 165 lines] > > > > Dave Peterson SignatureDave Peterson Mr. Peterson, I totally understand what you're saying. You know, when I was asking for a macro to open, wirite a macro, save "MY OWN" vba project, I thought that the macro would be similar to Unprotecting and Protecting a "sheet". By the way, it was so nice of you to put notes on your macro. It helped me understand the macro better. I still wish that there's a "simple way" of opening and closing one's own VBA project with a password. Thanks again! > You may have protected the workbook's structure or a worksheet (via > tools|Protection), but you didn't protect the project (inside the VBE, [quoted text clipped - 175 lines] > > > > > > Dave Peterson The bad news is that the simple way doesn't always work. It involves using SendKeys. If you search google in the *excel* newsgroups for: unprotect vba project sendkeys You'll find lots of hits: http://groups.google.co.uk/groups?as_q=unprotect+vba+project+sendkeys&num=100 But I still wouldn't use it. > Mr. Peterson, > [quoted text clipped - 194 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.