Import module in protected project?

[zRobbie]

Hello fellowNewsgroupReaders,

I want to import a revised module in a worksheet. I can do that with the
command

Workbooks("MyWorkbook.xls").VBProject.VBComponents.Import FName

where filename contains the path and name. This works great. (thanks to Chip
Pearson...)

But: If I've protected my VBA modules with a password this won't work
anymore.

I looked intensly in all options on VBComponents, but the only thing I found
is the option "Protection". This only tells me if the poject is protected.
But can I unprotect the VBA modules (and later, after importing, protect it
again?)

Greetz,
Rob Zoomer/Factos

[Chip Pearson]

Rob,

Here's some code created by Bill Manville that uses SendKeys to unprotect and
protect a project.

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" & Password & "~~%{F11}", True

If VBP.Protection = vbext_pp_locked Then
' failed - maybe wrong password
SendKeys "%{F11}%TE", True
End If

' leave no evidence of the password
Password = ""
' go back to the previously active workbook
wbActive.Activate

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ProtectVBProject(WB As Workbook, ByVal Password As String)

Dim VBP As VBProject, oWin As VBIDE.Window
Dim wbActive As Workbook
Dim i As Integer

Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

' 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 "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password & "~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
WB.Save
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"zRobbie" <zro...@hot-well-get-riddofthis-mail.com> wrote in message
news:XwlQ8.2231905$Lj7.74549256@Flipper...

[Rob]

Chip (and others)

first, thanks for answering so fast!

Seems like the code of Bill Manville has one problem.
As an example:

the workbook to unprotect is "WBU.xls"
the workbook that holds the code to unprotect WBU is "WBC.xls"

I use this code to start the sequence

Sub Start()

Dim WB As Workbook
Dim Password As String
Set WB = Workbooks("WBU.xls")
Password = "test"
UnprotectVBProject WB, Password
End Sub

Now what happens, is that when the key sequence (marked at ***ROB***) is
been sent to the VBA editor, workbook WBC is still selected (active) in the
VBA editor. Thus the code applies to it's own workbook WBC instead of WBU.
somehow the process of selecting the proper book is ignored. I checked it
with a msgbox telling me the name of the active workbook and that showed
that WBU was the active book So that is correct. Still, the wrong workbook
is selected in the VBA editor.

I created a button in WBC that starts the program, closed the Editor, but
that did not do the trick.

So how can I select the proper Workbook (WBU) to run the macro too?

Greetz,
Rob

"Chip Pearson" <ch...@cpearson.com> schreef in bericht
news:eLwbIiGGCHA.2172@tkmsftngp10...

> Rob,
>
> Here's some code created by Bill Manville that uses SendKeys to unprotect
and
> protect a project.
>
> 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}"

>***ROB*** SendKeys "%{F11}%TE" & Password & "~~%{F11}", True

[Tom Ogilvy]

ActiveWorkbook has nothing to do with it. You want to work on the
ActiveProject - the project with the focus is the project that get
"administered".

Regards,
Tom Ogilvy

Rob <In...@fac-well-get-riddofthis-tos.com> wrote in message
news:PkqQ8.2238123$Lj7.75004452@Flipper...

[Rob]

Chip (and others)

Sub Start()

Greetz,
Rob

> Rob,
>
> Here's some code created by Bill Manville that uses SendKeys to unprotect
and
> protect a project.
>
> 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}"

> ***Rob*** SendKeys "%{F11}%TE" & Password & "~~%{F11}", True

[Rob]

Tom,
thanks for the hint. But: miserable as I feel, I cannot find HOW to
focus..... Suggestion of one line with code how-to will do?
%-)

Rob

"Tom Ogilvy" <twog...@msn.com> schreef in bericht
news:ett5CaLGCHA.2696@tkmsftngp12...

[Chip Pearson]

Rob,

For the Unprotect operation a much simpler way is simply use code like the
following:

Sub UnprotectVBProject(WB As Workbook)
Set Application.VBE.ActiveProject = WB.VBProject
SendKeys "~PASSWORD~"
End Sub

Of course, change "PASSWORD" to the actual password for the project (but keep
the leading and trailing ~ characters -- these represent the ENTER key).

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Rob" <In...@fac-well-get-riddofthis-tos.com> wrote in message

news:IZCQ8.2263192$Lj7.75494980@Flipper...

[alok....@gmail.com]

Chip Pearson wrote:
> Rob,
>
> For the Unprotect operation a much simpler way is simply use code
like the
> following:
>
> Sub UnprotectVBProject(WB As Workbook)
> Set Application.VBE.ActiveProject = WB.VBProject

In Excel 10.0, this above does not select the project of an XLA addin
in the Projects window. Tried by running the UnprotectVBProject sub
routine from within VBE and also from excel Run macro dialog box but to
no avail. Also the Application.VBE.ActiveProject help file indicates
that this is now a readonly property. So, Microsoft now seems to have
permanently fixed the unlocking of VBE Projects via VBA! Right?