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

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

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

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

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

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

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

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