Avatar of Theva
ThevaFlag for Malaysia asked on

Unprotect and Reprotect VBA Project

Hi Experts,

I need Experts help. How to unprotect VBA project while running the macro and reprotect the VBA project after the whole process completed. Currently I’m facing difficulty to run the macro after I lock the VBA project.  I have attached the workbook for Experts perusal. The VBA password is “test”. Hope Experts could help me to resolve this problem.



workbookt.xls
OutlookMicrosoft Excel

Avatar of undefined
Last Comment
Theva

8/22/2022 - Mon
TommySzalapski

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

Open 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.
ASKER
Theva

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?
Patrick Matthews

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>
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Theva

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.
SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Theva

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")

Open in new window

Patrick Matthews

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Theva

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.
Patrick Matthews

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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Theva

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 :(
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rory Archibald

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.
TommySzalapski

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.
ASKER
Theva

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Theva

Hi,

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

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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Theva

Hi,

Thanks for the help and guide.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck