Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Hello newsgroup,
I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
ASKING for and getting the password is the easy part with a simple inputbox
at the beginning of the macro. The harder part is passing this password to unlock the vba project. I think it can be done but don't know the code. Does it not ask for the code automatically, as when you try to open a locked project? "Eric van Uden" wrote: Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Unlocking the vba project with code isn't supported in VBA. As a
workaround, sendkeys has been offered in the past. -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Thanks for your reply, gocush.
I believe it is like this: a prompt will appear automatically, but only when there is no unprotected VBA project. That means that if I was working in VBE or another workbook is open and has unprotected macro code, I would not be prompted foor a password. I may be wrong here, but this seems to be my exerience. Eric "gocush" /delete schreef in bericht ... ASKING for and getting the password is the easy part with a simple inputbox at the beginning of the macro. The harder part is passing this password to unlock the vba project. I think it can be done but don't know the code. Does it not ask for the code automatically, as when you try to open a locked project? "Eric van Uden" wrote: Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Hello Tom,
I'm afraid I may not have expressed myself clearly before. I am not looking to unlock the project by means of code. It would be rather pointless to lock my code behind a password and then provide an easy way to dismiss with it. I want to supply the password by hand, but at least be prompted for it. Like what happens when you call 'Application.Worksheets(1).Unprotect' with a worksheetbutton and the worksheet has a password set. At this moment I only get Error 50289, saying that Excel cannot execute the macro because the project is protected. So maybe I need the part just before where I could enter the SendKeys bit ;-) To explain my intentions: I use template to create enduser workbooks. Once individualised I want to finalise the workbook. E.g., some formulas link to other workbooks. These formulas can be replaced by their values, so the workbook will load faster. Some modules also become redundant, and I want to delete them. But this is not possible while the project is locked. It would be fastest and safest if I would be prompted for the password, supply it by hand and then have the cleanup-code execute. Eric "Tom Ogilvy" schreef in bericht ... Unlocking the vba project with code isn't supported in VBA. As a workaround, sendkeys has been offered in the past. -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
If sendkeys was the solution, then I expect that the solution included
showing the dialog. If you want to omit including the password, then that should get you want you want. You can search google for this group and keyworkds like project password sendkeys -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello Tom, I'm afraid I may not have expressed myself clearly before. I am not looking to unlock the project by means of code. It would be rather pointless to lock my code behind a password and then provide an easy way to dismiss with it. I want to supply the password by hand, but at least be prompted for it. Like what happens when you call 'Application.Worksheets(1).Unprotect' with a worksheetbutton and the worksheet has a password set. At this moment I only get Error 50289, saying that Excel cannot execute the macro because the project is protected. So maybe I need the part just before where I could enter the SendKeys bit ;-) To explain my intentions: I use template to create enduser workbooks. Once individualised I want to finalise the workbook. E.g., some formulas link to other workbooks. These formulas can be replaced by their values, so the workbook will load faster. Some modules also become redundant, and I want to delete them. But this is not possible while the project is locked. It would be fastest and safest if I would be prompted for the password, supply it by hand and then have the cleanup-code execute. Eric "Tom Ogilvy" schreef in bericht ... Unlocking the vba project with code isn't supported in VBA. As a workaround, sendkeys has been offered in the past. -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Hello Tom,
Thank you for the follow up. I found one post that might be the one you recall, but it didn't provide a resolution to my problem. Once again: I'm not loooking for 'password recovery'. I only want the dialog box that asks for the VBA-password to pop up the way it does when you try to start a macro recording and the VBAproject is protected. You are prompted for the password and then the recording starts. Any other sugestions would be most welcome. Eric "Tom Ogilvy" schreef in bericht ... If sendkeys was the solution, then I expect that the solution included showing the dialog. If you want to omit including the password, then that should get you want you want. You can search google for this group and keyworkds like project password sendkeys -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello Tom, I'm afraid I may not have expressed myself clearly before. I am not looking to unlock the project by means of code. It would be rather pointless to lock my code behind a password and then provide an easy way to dismiss with it. I want to supply the password by hand, but at least be prompted for it. Like what happens when you call 'Application.Worksheets(1).Unprotect' with a worksheetbutton and the worksheet has a password set. At this moment I only get Error 50289, saying that Excel cannot execute the macro because the project is protected. So maybe I need the part just before where I could enter the SendKeys bit ;-) To explain my intentions: I use template to create enduser workbooks. Once individualised I want to finalise the workbook. E.g., some formulas link to other workbooks. These formulas can be replaced by their values, so the workbook will load faster. Some modules also become redundant, and I want to delete them. But this is not possible while the project is locked. It would be fastest and safest if I would be prompted for the password, supply it by hand and then have the cleanup-code execute. Eric "Tom Ogilvy" schreef in bericht ... Unlocking the vba project with code isn't supported in VBA. As a workaround, sendkeys has been offered in the past. -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
I did the google search and modified this code from Bill Manville to stop at
the password prompt. Sub TryToUnlock() Dim WB As Workbook Set WB = Workbooks("TestUnlock.xls") UnprotectVBProject WB End Sub Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String) ' Bill Manville, 29-Jan-2000 ' Dim VBP As VBProject, oWin As VBIDE.Window Dim wbActive As Workbook Dim i As Integer Set VBP = WB.VBProject Set wbActive = ActiveWorkbook If VBP.Protection < vbext_pp_locked Then Exit Sub Application.ScreenUpdating = False ' close any code windows to ensure we hit the right project For Each oWin In VBP.VBE.Windows If InStr(oWin.Caption, "(") 0 Then oWin.Close Next oWin WB.Activate ' now use lovely SendKeys to unprotect Application.OnKey "%{F11}" SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True 'If VBP.Protection = vbext_pp_locked Then ' failed - maybe wrong password ' SendKeys "%{F11}%TE", True 'End If End Sub -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello Tom, Thank you for the follow up. I found one post that might be the one you recall, but it didn't provide a resolution to my problem. Once again: I'm not loooking for 'password recovery'. I only want the dialog box that asks for the VBA-password to pop up the way it does when you try to start a macro recording and the VBAproject is protected. You are prompted for the password and then the recording starts. Any other sugestions would be most welcome. Eric "Tom Ogilvy" schreef in bericht ... If sendkeys was the solution, then I expect that the solution included showing the dialog. If you want to omit including the password, then that should get you want you want. You can search google for this group and keyworkds like project password sendkeys -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello Tom, I'm afraid I may not have expressed myself clearly before. I am not looking to unlock the project by means of code. It would be rather pointless to lock my code behind a password and then provide an easy way to dismiss with it. I want to supply the password by hand, but at least be prompted for it. Like what happens when you call 'Application.Worksheets(1).Unprotect' with a worksheetbutton and the worksheet has a password set. At this moment I only get Error 50289, saying that Excel cannot execute the macro because the project is protected. So maybe I need the part just before where I could enter the SendKeys bit ;-) To explain my intentions: I use template to create enduser workbooks. Once individualised I want to finalise the workbook. E.g., some formulas link to other workbooks. These formulas can be replaced by their values, so the workbook will load faster. Some modules also become redundant, and I want to delete them. But this is not possible while the project is locked. It would be fastest and safest if I would be prompted for the password, supply it by hand and then have the cleanup-code execute. Eric "Tom Ogilvy" schreef in bericht ... Unlocking the vba project with code isn't supported in VBA. As a workaround, sendkeys has been offered in the past. -- Regards, Tom Ogilvy "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message ... Hello newsgroup, I have a macro that manipulates a workbook to make it a definitive version. This includes replacing formulas with their values and deleting a code module that is no longer needed. The macro is executed with a button in the worksheet. The VBAproject is locked with a password. Now, of course, the macro that would delete the code module will not execute because the project is locked. Is there any way to prompt for the VBA password when the worksheet button is pushed, then executing the macro when the project is unlocked? I Hope I'm making sense. Thanks in advance for any suggestions. Eric |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Hello Tom,
I really appreciate the lengths you went to to help me! Sorry for the delay in my response, but there's also work... ;-) Wow, this turns out to be much more complicated than I would have thought. I suppose because security issues are involved. My Excel 2002 doesn't recognise the VBProject type and the VBIDE.Window type. Can you tell me what I have to reference to change this? I find so many object libraries to choose from, that I'm lost. I hope I'm not imposing, but this has led me into unfamiliar territory, I'm afraid. Thanks again, so far. Eric "Tom Ogilvy" schreef in bericht ... |I did the google search and modified this code from Bill Manville to stop at | the password prompt. | | Sub TryToUnlock() | Dim WB As Workbook | Set WB = Workbooks("TestUnlock.xls") | UnprotectVBProject WB | End Sub | | Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String) | ' Bill Manville, 29-Jan-2000 | ' | Dim VBP As VBProject, oWin As VBIDE.Window | Dim wbActive As Workbook | Dim i As Integer | | Set VBP = WB.VBProject | Set wbActive = ActiveWorkbook | | If VBP.Protection < vbext_pp_locked Then Exit Sub | | Application.ScreenUpdating = False | | ' close any code windows to ensure we hit the right project | For Each oWin In VBP.VBE.Windows | If InStr(oWin.Caption, "(") 0 Then oWin.Close | Next oWin | | WB.Activate | ' now use lovely SendKeys to unprotect | Application.OnKey "%{F11}" | SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True | | 'If VBP.Protection = vbext_pp_locked Then | ' failed - maybe wrong password | ' SendKeys "%{F11}%TE", True | 'End If | | End Sub | | -- | Regards, | Tom Ogilvy | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello Tom, | | Thank you for the follow up. | I found one post that might be the one you recall, but it didn't provide a | resolution to my problem. | | Once again: I'm not loooking for 'password recovery'. | I only want the dialog box that asks for the VBA-password to pop up the | way | it does when you try to start a macro recording and the VBAproject is | protected. You are prompted for the password and then the recording | starts. | | Any other sugestions would be most welcome. | | Eric | | | "Tom Ogilvy" schreef in bericht | ... | If sendkeys was the solution, then I expect that the solution included | showing the dialog. If you want to omit including the password, then | that | should get you want you want. You can search google for this group and | keyworkds like | | project password sendkeys | | -- | Regards, | Tom Ogilvy | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello Tom, | | I'm afraid I may not have expressed myself clearly before. | I am not looking to unlock the project by means of code. It would be | rather | pointless to lock my code behind a password and then provide an easy | way | to | dismiss with it. | I want to supply the password by hand, but at least be prompted for it. | Like what happens when you call 'Application.Worksheets(1).Unprotect' | with | a | worksheetbutton and the worksheet has a password set. | | At this moment I only get Error 50289, saying that Excel cannot execute | the | macro because the project is protected. | | So maybe I need the part just before where I could enter the SendKeys | bit | ;-) | | To explain my intentions: I use template to create enduser workbooks. | Once individualised I want to finalise the workbook. E.g., some | formulas | link to other workbooks. These formulas can be replaced by their | values, | so | the workbook will load faster. Some modules also become redundant, and | I | want to delete them. But this is not possible while the project is | locked. | It would be fastest and safest if I would be prompted for the password, | supply it by hand and then have the cleanup-code execute. | | | Eric | | "Tom Ogilvy" schreef in bericht | ... | Unlocking the vba project with code isn't supported in VBA. As a | workaround, sendkeys has been offered in the past. | | -- | Regards, | Tom Ogilvy | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello newsgroup, | | I have a macro that manipulates a workbook to make it a definitive | version. | This includes replacing formulas with their values and deleting a | code | module that is no longer needed. | The macro is executed with a button in the worksheet. | The VBAproject is locked with a password. | | Now, of course, the macro that would delete the code module will not | execute | because the project is locked. | | Is there any way to prompt for the VBA password when the worksheet | button | is | pushed, then executing the macro when the project is unlocked? | | I Hope I'm making sense. Thanks in advance for any suggestions. | | Eric | | | | | | | | | | | | |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
in the VBE in tools = references you need to find the Microsoft Visual
Basic Extensibility Library and place a check mark next to it. Then you can examine it in the object browser. It shows up as VBIDE as the Library. You can look at Chip Pearson's site for some additional information on coding in the VBE. http://www.cpearson.com/excel/vbe.htm -- Regards, Tom Ogilvy "Eric van Uden" <ericvanuden THISGOES @ OUT supermail.nl wrote in message ... Hello Tom, I really appreciate the lengths you went to to help me! Sorry for the delay in my response, but there's also work... ;-) Wow, this turns out to be much more complicated than I would have thought. I suppose because security issues are involved. My Excel 2002 doesn't recognise the VBProject type and the VBIDE.Window type. Can you tell me what I have to reference to change this? I find so many object libraries to choose from, that I'm lost. I hope I'm not imposing, but this has led me into unfamiliar territory, I'm afraid. Thanks again, so far. Eric "Tom Ogilvy" schreef in bericht ... |I did the google search and modified this code from Bill Manville to stop at | the password prompt. | | Sub TryToUnlock() | Dim WB As Workbook | Set WB = Workbooks("TestUnlock.xls") | UnprotectVBProject WB | End Sub | | Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String) | ' Bill Manville, 29-Jan-2000 | ' | Dim VBP As VBProject, oWin As VBIDE.Window | Dim wbActive As Workbook | Dim i As Integer | | Set VBP = WB.VBProject | Set wbActive = ActiveWorkbook | | If VBP.Protection < vbext_pp_locked Then Exit Sub | | Application.ScreenUpdating = False | | ' close any code windows to ensure we hit the right project | For Each oWin In VBP.VBE.Windows | If InStr(oWin.Caption, "(") 0 Then oWin.Close | Next oWin | | WB.Activate | ' now use lovely SendKeys to unprotect | Application.OnKey "%{F11}" | SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True | | 'If VBP.Protection = vbext_pp_locked Then | ' failed - maybe wrong password | ' SendKeys "%{F11}%TE", True | 'End If | | End Sub | | -- | Regards, | Tom Ogilvy | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello Tom, | | Thank you for the follow up. | I found one post that might be the one you recall, but it didn't provide a | resolution to my problem. | | Once again: I'm not loooking for 'password recovery'. | I only want the dialog box that asks for the VBA-password to pop up the | way | it does when you try to start a macro recording and the VBAproject is | protected. You are prompted for the password and then the recording | starts. | | Any other sugestions would be most welcome. | | Eric | | | "Tom Ogilvy" schreef in bericht | ... | If sendkeys was the solution, then I expect that the solution included | showing the dialog. If you want to omit including the password, then | that | should get you want you want. You can search google for this group and | keyworkds like | | project password sendkeys | | -- | Regards, | Tom Ogilvy | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello Tom, | | I'm afraid I may not have expressed myself clearly before. | I am not looking to unlock the project by means of code. It would be | rather | pointless to lock my code behind a password and then provide an easy | way | to | dismiss with it. | I want to supply the password by hand, but at least be prompted for it. | Like what happens when you call 'Application.Worksheets(1).Unprotect' | with | a | worksheetbutton and the worksheet has a password set. | | At this moment I only get Error 50289, saying that Excel cannot execute | the | macro because the project is protected. | | So maybe I need the part just before where I could enter the SendKeys | bit | ;-) | | To explain my intentions: I use template to create enduser workbooks. | Once individualised I want to finalise the workbook. E.g., some | formulas | link to other workbooks. These formulas can be replaced by their | values, | so | the workbook will load faster. Some modules also become redundant, and | I | want to delete them. But this is not possible while the project is | locked. | It would be fastest and safest if I would be prompted for the password, | supply it by hand and then have the cleanup-code execute. | | | Eric | | "Tom Ogilvy" schreef in bericht | ... | Unlocking the vba project with code isn't supported in VBA. As a | workaround, sendkeys has been offered in the past. | | -- | Regards, | Tom Ogilvy | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | ... | Hello newsgroup, | | I have a macro that manipulates a workbook to make it a definitive | version. | This includes replacing formulas with their values and deleting a | code | module that is no longer needed. | The macro is executed with a button in the worksheet. | The VBAproject is locked with a password. | | Now, of course, the macro that would delete the code module will not | execute | because the project is locked. | | Is there any way to prompt for the VBA password when the worksheet | button | is | pushed, then executing the macro when the project is unlocked? | | I Hope I'm making sense. Thanks in advance for any suggestions. | | Eric | | | | | | | | | | | | |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
Hello again Tom,
I must seem a hopeles case, but I cannot get this code to work. My testworkbook is TestUnlock.xls. I referenced the library you specified, even restarted, and the object browser is showing its objects. Only one type mismatch keeps blocking things. The line that produces the error 13 (type mismatch) is 'Set VBP = WB.VBProject'. I cut the lines with 'wbActive' and 'i' that seemed redundant and must be leftover from the original code, and now have this: Could you possibly explain this, or would you advise I give up and return to my dayjob? '==============================================boc = Sub TryToUnlock() Dim WB As Workbook Set WB = Workbooks("TestUnlock.xls") UnprotectVBProject WB End Sub Sub UnprotectVBProject(WB As Workbook) ' Bill Manville, 29-Jan-2000 Dim VBP As VBProject, oWin As VBIDE.Window Set VBP = WB.VBProject If VBP.Protection < vbext_pp_locked Then Exit Sub ' close any code windows to ensure we hit the right project For Each oWin In VBP.VBE.Windows If InStr(oWin.Caption, "(") 0 Then oWin.Close Next oWin WB.Activate ' now use lovely SendKeys to unprotect Application.OnKey "%{F11}" SendKeys "%{F11}%TE", True End Sub '==============================================eoc = "Tom Ogilvy" schreef in bericht ... | in the VBE in tools = references you need to find the Microsoft Visual | Basic Extensibility Library and place a check mark next to it. | | Then you can examine it in the object browser. It shows up as VBIDE as the | Library. | | You can look at Chip Pearson's site for some additional information on | coding in the VBE. | | http://www.cpearson.com/excel/vbe.htm | | | -- | Regards, | Tom Ogilvy | | | | | "Eric van Uden" <ericvanuden THISGOES @ OUT supermail.nl wrote in message | ... | Hello Tom, | | | I really appreciate the lengths you went to to help me! | | Sorry for the delay in my response, but there's also work... ;-) | | Wow, this turns out to be much more complicated than I would have thought. | I | suppose because security issues are involved. | | My Excel 2002 doesn't recognise the VBProject type and the VBIDE.Window | type. | | Can you tell me what I have to reference to change this? I find so many | object libraries to choose from, that I'm lost. | | I hope I'm not imposing, but this has led me into unfamiliar territory, | I'm | afraid. | | | Thanks again, so far. | | | Eric | | | "Tom Ogilvy" schreef in bericht | ... | |I did the google search and modified this code from Bill Manville to stop | at | | the password prompt. | | | | Sub TryToUnlock() | | Dim WB As Workbook | | Set WB = Workbooks("TestUnlock.xls") | | UnprotectVBProject WB | | End Sub | | | | Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String) | | ' Bill Manville, 29-Jan-2000 | | ' | | Dim VBP As VBProject, oWin As VBIDE.Window | | Dim wbActive As Workbook | | Dim i As Integer | | | | Set VBP = WB.VBProject | | Set wbActive = ActiveWorkbook | | | | If VBP.Protection < vbext_pp_locked Then Exit Sub | | | | Application.ScreenUpdating = False | | | | ' close any code windows to ensure we hit the right project | | For Each oWin In VBP.VBE.Windows | | If InStr(oWin.Caption, "(") 0 Then oWin.Close | | Next oWin | | | | WB.Activate | | ' now use lovely SendKeys to unprotect | | Application.OnKey "%{F11}" | | SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True | | | | 'If VBP.Protection = vbext_pp_locked Then | | ' failed - maybe wrong password | | ' SendKeys "%{F11}%TE", True | | 'End If | | | | End Sub | | | | -- | | Regards, | | Tom Ogilvy | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello Tom, | | | | Thank you for the follow up. | | I found one post that might be the one you recall, but it didn't | provide | a | | resolution to my problem. | | | | Once again: I'm not loooking for 'password recovery'. | | I only want the dialog box that asks for the VBA-password to pop up | the | | way | | it does when you try to start a macro recording and the VBAproject is | | protected. You are prompted for the password and then the recording | | starts. | | | | Any other sugestions would be most welcome. | | | | Eric | | | | | | "Tom Ogilvy" schreef in bericht | | ... | | If sendkeys was the solution, then I expect that the solution | included | | showing the dialog. If you want to omit including the password, | then | | that | | should get you want you want. You can search google for this group | and | | keyworkds like | | | | project password sendkeys | | | | -- | | Regards, | | Tom Ogilvy | | | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello Tom, | | | | I'm afraid I may not have expressed myself clearly before. | | I am not looking to unlock the project by means of code. It would | be | | rather | | pointless to lock my code behind a password and then provide an | easy | | way | | to | | dismiss with it. | | I want to supply the password by hand, but at least be prompted for | it. | | Like what happens when you call | 'Application.Worksheets(1).Unprotect' | | with | | a | | worksheetbutton and the worksheet has a password set. | | | | At this moment I only get Error 50289, saying that Excel cannot | execute | | the | | macro because the project is protected. | | | | So maybe I need the part just before where I could enter the | SendKeys | | bit | | ;-) | | | | To explain my intentions: I use template to create enduser | workbooks. | | Once individualised I want to finalise the workbook. E.g., some | | formulas | | link to other workbooks. These formulas can be replaced by their | | values, | | so | | the workbook will load faster. Some modules also become redundant, | and | | I | | want to delete them. But this is not possible while the project is | | locked. | | It would be fastest and safest if I would be prompted for the | password, | | supply it by hand and then have the cleanup-code execute. | | | | | | Eric | | | | "Tom Ogilvy" schreef in bericht | | ... | | Unlocking the vba project with code isn't supported in VBA. As a | | workaround, sendkeys has been offered in the past. | | | | -- | | Regards, | | Tom Ogilvy | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello newsgroup, | | | | I have a macro that manipulates a workbook to make it a | definitive | | version. | | This includes replacing formulas with their values and deleting | a | | code | | module that is no longer needed. | | The macro is executed with a button in the worksheet. | | The VBAproject is locked with a password. | | | | Now, of course, the macro that would delete the code module will | not | | execute | | because the project is locked. | | | | Is there any way to prompt for the VBA password when the | worksheet | | button | | is | | pushed, then executing the macro when the project is unlocked? | | | | I Hope I'm making sense. Thanks in advance for any suggestions. | | | | Eric | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for VBAproject password
With the reference set, this worked for me:
Sub Tester1() Dim WB As Workbook Dim VBP As VBIDE.VBProject Set WB = ActiveWorkbook Set VBP = WB.VBProject MsgBox VBP.Name End Sub That was as an example. So Dim VBP as VBIDE.VBProject (although the previous code worked for me in xl2002 as posted). -- Regards, Tom Ogilvy "Eric van Uden" <ericvanuden THISGOES @ OUT supermail.nl wrote in message ... Hello again Tom, I must seem a hopeles case, but I cannot get this code to work. My testworkbook is TestUnlock.xls. I referenced the library you specified, even restarted, and the object browser is showing its objects. Only one type mismatch keeps blocking things. The line that produces the error 13 (type mismatch) is 'Set VBP = WB.VBProject'. I cut the lines with 'wbActive' and 'i' that seemed redundant and must be leftover from the original code, and now have this: Could you possibly explain this, or would you advise I give up and return to my dayjob? '==============================================boc = Sub TryToUnlock() Dim WB As Workbook Set WB = Workbooks("TestUnlock.xls") UnprotectVBProject WB End Sub Sub UnprotectVBProject(WB As Workbook) ' Bill Manville, 29-Jan-2000 Dim VBP As VBProject, oWin As VBIDE.Window Set VBP = WB.VBProject If VBP.Protection < vbext_pp_locked Then Exit Sub ' close any code windows to ensure we hit the right project For Each oWin In VBP.VBE.Windows If InStr(oWin.Caption, "(") 0 Then oWin.Close Next oWin WB.Activate ' now use lovely SendKeys to unprotect Application.OnKey "%{F11}" SendKeys "%{F11}%TE", True End Sub '==============================================eoc = "Tom Ogilvy" schreef in bericht ... | in the VBE in tools = references you need to find the Microsoft Visual | Basic Extensibility Library and place a check mark next to it. | | Then you can examine it in the object browser. It shows up as VBIDE as the | Library. | | You can look at Chip Pearson's site for some additional information on | coding in the VBE. | | http://www.cpearson.com/excel/vbe.htm | | | -- | Regards, | Tom Ogilvy | | | | | "Eric van Uden" <ericvanuden THISGOES @ OUT supermail.nl wrote in message | ... | Hello Tom, | | | I really appreciate the lengths you went to to help me! | | Sorry for the delay in my response, but there's also work... ;-) | | Wow, this turns out to be much more complicated than I would have thought. | I | suppose because security issues are involved. | | My Excel 2002 doesn't recognise the VBProject type and the VBIDE.Window | type. | | Can you tell me what I have to reference to change this? I find so many | object libraries to choose from, that I'm lost. | | I hope I'm not imposing, but this has led me into unfamiliar territory, | I'm | afraid. | | | Thanks again, so far. | | | Eric | | | "Tom Ogilvy" schreef in bericht | ... | |I did the google search and modified this code from Bill Manville to stop | at | | the password prompt. | | | | Sub TryToUnlock() | | Dim WB As Workbook | | Set WB = Workbooks("TestUnlock.xls") | | UnprotectVBProject WB | | End Sub | | | | Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String) | | ' Bill Manville, 29-Jan-2000 | | ' | | Dim VBP As VBProject, oWin As VBIDE.Window | | Dim wbActive As Workbook | | Dim i As Integer | | | | Set VBP = WB.VBProject | | Set wbActive = ActiveWorkbook | | | | If VBP.Protection < vbext_pp_locked Then Exit Sub | | | | Application.ScreenUpdating = False | | | | ' close any code windows to ensure we hit the right project | | For Each oWin In VBP.VBE.Windows | | If InStr(oWin.Caption, "(") 0 Then oWin.Close | | Next oWin | | | | WB.Activate | | ' now use lovely SendKeys to unprotect | | Application.OnKey "%{F11}" | | SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True | | | | 'If VBP.Protection = vbext_pp_locked Then | | ' failed - maybe wrong password | | ' SendKeys "%{F11}%TE", True | | 'End If | | | | End Sub | | | | -- | | Regards, | | Tom Ogilvy | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello Tom, | | | | Thank you for the follow up. | | I found one post that might be the one you recall, but it didn't | provide | a | | resolution to my problem. | | | | Once again: I'm not loooking for 'password recovery'. | | I only want the dialog box that asks for the VBA-password to pop up | the | | way | | it does when you try to start a macro recording and the VBAproject is | | protected. You are prompted for the password and then the recording | | starts. | | | | Any other sugestions would be most welcome. | | | | Eric | | | | | | "Tom Ogilvy" schreef in bericht | | ... | | If sendkeys was the solution, then I expect that the solution | included | | showing the dialog. If you want to omit including the password, | then | | that | | should get you want you want. You can search google for this group | and | | keyworkds like | | | | project password sendkeys | | | | -- | | Regards, | | Tom Ogilvy | | | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello Tom, | | | | I'm afraid I may not have expressed myself clearly before. | | I am not looking to unlock the project by means of code. It would | be | | rather | | pointless to lock my code behind a password and then provide an | easy | | way | | to | | dismiss with it. | | I want to supply the password by hand, but at least be prompted for | it. | | Like what happens when you call | 'Application.Worksheets(1).Unprotect' | | with | | a | | worksheetbutton and the worksheet has a password set. | | | | At this moment I only get Error 50289, saying that Excel cannot | execute | | the | | macro because the project is protected. | | | | So maybe I need the part just before where I could enter the | SendKeys | | bit | | ;-) | | | | To explain my intentions: I use template to create enduser | workbooks. | | Once individualised I want to finalise the workbook. E.g., some | | formulas | | link to other workbooks. These formulas can be replaced by their | | values, | | so | | the workbook will load faster. Some modules also become redundant, | and | | I | | want to delete them. But this is not possible while the project is | | locked. | | It would be fastest and safest if I would be prompted for the | password, | | supply it by hand and then have the cleanup-code execute. | | | | | | Eric | | | | "Tom Ogilvy" schreef in bericht | | ... | | Unlocking the vba project with code isn't supported in VBA. As a | | workaround, sendkeys has been offered in the past. | | | | -- | | Regards, | | Tom Ogilvy | | | | "Eric van Uden" <eric[ @ ]doornroosje[ . ]nl wrote in message | | ... | | Hello newsgroup, | | | | I have a macro that manipulates a workbook to make it a | definitive | | version. | | This includes replacing formulas with their values and deleting | a | | code | | module that is no longer needed. | | The macro is executed with a button in the worksheet. | | The VBAproject is locked with a password. | | | | Now, of course, the macro that would delete the code module will | not | | execute | | because the project is locked. | | | | Is there any way to prompt for the VBA password when the | worksheet | | button | | is | | pushed, then executing the macro when the project is unlocked? | | | | I Hope I'm making sense. Thanks in advance for any suggestions. | | | | Eric | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cracking VBAProject Password | Excel Discussion (Misc queries) | |||
Password Prompt | Excel Discussion (Misc queries) | |||
VBAProject Password upon exiting Excel | Excel Discussion (Misc queries) | |||
Password Prompt | Excel Discussion (Misc queries) | |||
VBAProject Password | Excel Programming |