Switching between locked VBA modules in VBA

dawsona

Board Regular
Joined
Jul 28, 2002
Messages
66
I have the following VBA code that unprotects a Project in a different workbook then deletes the existing module.

I then want it to unprotect the project that the current macro is running in to copy the module over to the other workbook.

Sub add_etc()

Dim DProj As Object
Set DProj = ActiveWorkbook.VBProject

Application.VBE.MainWindow.Visible = True

For Each OpWin In DProj.VBE.Windows
If InStr(OpWin.Caption, "(") > 0 Then OpWin.Close
Next OpWin

Application.SendKeys "pword", True
Application.SendKeys "~", True

' Delete existing module

DProj.VBE.selectedvbcomponent.Activate
Application.VBE.activevbproject.VBComponents.Remove
Application.VBE.activevbproject.VBComponents("AD_Macros")

' Unprotect source file project

aaa = ThisWorkbook.Name
Workbooks(aaa).Activate

Dim SProj As Object
Set SProj = ActiveWorkbook.VBProject

For Each OpWin In SProj.VBE.Windows
If InStr(OpWin.Caption, "(") > 0 Then OpWin.Close
Next OpWin

Application.VBE.MainWindow.Visible = True
Application.SendKeys "test", True
Application.SendKeys "~", True

VBProj.VBE.selectedvbcomponent.Activate

' Copy module in

' Next bit of code works okay

End Sub

The code works until it gets to the

' Unprotect source file project

when the sendkeys function is sending to the Excel spreadsheet instead of the project. I think it may be something to do with the OpWin.Caption (gained from a previous post) as I don't quite understand what it means in English!

This is the culmination of several of my recent posts and if you can crack this for me I would be delighted
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top