Unprotect and Reprotect VBA Project

Theva, in the Workbook_Open macro put this code

Private Sub Workbook_Open()
Dim s As Worksheet

For Each s In Sheets
  s.Protect Password:=cstrPASSWORD, UserInterfaceOnly:=True
Next

End Sub

Select allOpen in new window

Then you can take out all the protect/unprotect stuff because the code will always be able to run. Only the user interface will be protected.

Hi,

I have removed all unprotect and reprotect  code from sheets and lock the VBA project with “test” word. When I open the workbook it shows error message as  “Password you supply not correct”. How to fix this?

Theva,

Do you mean unlock the VBA project, or unprotect the workbook/worksheets?

If the former, Tommy's code won't do it.  I am unaware of a reliable method to do that from VBA; the best I can guess at would be a SendKeys hack, but that would be pretty unreliable.

I presume you need to unlock the VBA project because you are doing an export/import of a module.  Why do you need to do that?

Patrick

<Friendly aside to Tommy> Declaring a variable as type Worksheet and using it to enumerate the Sheets collection is, IMHO, a bad habit to get into: if you try this on a workbook that has a Chart sheet or Excel4Macro sheet, you will get a type mismatch.  If you type the variable as Worksheet, then use the Worksheets collection. </aside>

Hi Patrick,

You’re right. I’m intent to unlock the VBA project to allow “GMT Module” copied into new workbook. After the whole process completed, need to relock the VBA project.

Hi Patrick,

Currently I have 2 module extracting module “GMT”. Hope you can rewrite these code to allow workbook copied with macro.

Module1 :

End If
    Sheets("Request").Activate
    Range("M12").Activate
   
  Dim Wsh As Worksheet
  Application.DisplayAlerts = False
  Set Wsh = Worksheets("Request")   'Set source
  Application.ScreenUpdating = False
  Wsh.Copy                'Copy source sheet to new workbook
  ThisWorkbook.VBProject.VBComponents("GMT").Export Environ("Temp") & "\GMT.bas"
  ActiveWorkbook.VBProject.VBComponents.Import (Environ("Temp") & "\GMT.bas")
   
  Wsh.Range("AB:AC").Copy
  ActiveSheet.Range("AB:AC").PasteSpecial xlPasteValues

Module 2:

Set oLapp = CreateObject("Outlook.Application")
    Set oItem = oLapp.CreateItem(olMailItem)
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Request").Copy
    Call deleteButton2
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ThisWorkbook.VBProject.VBComponents("GMT").Export Environ("Temp") & "\GMT.bas"
    ActiveWorkbook.VBProject.VBComponents.Import (Environ("Temp") & "\GMT.bas")

Select allOpen in new window

Theva,

You misunderstand me.

You said you wanted to get the code into "new workbooks".  The most expeditious to do that is to make sure that the code already exists in a template, and that you create the new workbooks based off of a template.

THAT is my advice to you.  If you choose not to follow it, that is your right, but I will not be able to help you.

Patrick

Hi,

I do realized its kind of too tedious to reset the whole module. Appreciate if Patrick could help me with code that allow unprotect and reproteck the VBA project for module export.

Theva,

As I indicated in http:#a34133331, I know of no reliable way to do that.  If you insist on this course, you will have to do it without me.

If no one else chimes in within an hour or two, I suggest you click 'Request Attention'.

Patrick

Hi Rorya,

Thanks for the input. Indeed, I prefer to take Patrick's suggestion but the problem is I need to tackle 2 modules that requiring module export http:#a34133448, and that also quite complicated to resolve. Looks like I'm having roadblock :(

I don't see why that is a problem for either method we have suggested. Failing that, I would suggest you store the code in hidden worksheets and simply write it from there to the target workbook.

I think I have an easier solution for you. Just copy all the code from the GMT module to the TOP of the Request sheet's code. Then when you copy the sheet, the code will go with it. No need to import/export anything.

Hi Tommy,

I have tested your suggested solution but its giving error value at column AB:AC. I have attached the sample file that I've generated for your review.
TestFile.xls

Hi,

I believe this got something to do with "ActiveSheet.Range("AB:AC").PasteSpecial xlPasteValues" at send mail module

Yeah, the user defined functions seem to need to be in a module so leave the GMT module the way it was. You're using paste special so you shouldn't need the module anyway. If you just remove the import/export lines it should work.
I tried it and it worked halfway through then it broke it later. I think you have some code running that shouldn't be running. I'll check it out in a little while.

Hi,

Thanks for the help and guide.