Solved

Unprotect and Reprotect VBA Project

Posted on 2010-11-14
18
2,236 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Theva
  • 8
  • 4
  • 4
  • +1
18 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34133199
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.
0
 

Author Comment

by:Theva
ID: 34133292
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?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34133331
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>
0
 

Author Comment

by:Theva
ID: 34133356
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.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 34133410
Theva,

OK, but why do you need to do that at all?  If you need to get that module into a new workbook, you are far better off simply creating a template file (*.xlt for Excel 2003, *.xltm for Excel 2007/2010) that already has that code in it, and then create the new workbooks based on that template.

For example:

Dim NewWb As Workbook

Const TemplatePath As String = "z:\folder\subfolder\My Template.xlt"

Set NewWb = Workbooks.Add(TemplatePath)

Open in new window


Patrick
0
 

Author Comment

by:Theva
ID: 34133448
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

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34133483
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
0
 

Author Comment

by:Theva
ID: 34133501
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.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34133532
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
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 34135300
If you wish to copy the code to an unprotected workbook, then I suggest you either do as Patrick suggested, or save the code as a text file rather than trying to copy it from your existing project, and then import it to the target workbook.
If the target workbook is also protected, then you definitely ought to go Patrick's route. (I do have some code for unprotecting and protecting projects, but it's pretty complicated)
0
 

Author Comment

by:Theva
ID: 34135571
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 :(
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34135582
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.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34136252
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.
0
 

Author Comment

by:Theva
ID: 34143949
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
0
 

Author Comment

by:Theva
ID: 34144056
Hi,

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

Expert Comment

by:TommySzalapski
ID: 34150424
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.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 300 total points
ID: 34152457
Found it. In your sendMailWithMacro module
In the SendMsg Functon
Under this line
    ThisWorkbook.Sheets("Request").Copy
Put these lines
    ThisWorkbook.Sheets("Request").Range("AB:AC").Copy
    ActiveSheet.Range("AB:AC").PasteSpecial xlPasteValues

Then put all the GMT stuff back where it was and remove all the import/export lines.
0
 

Author Closing Comment

by:Theva
ID: 34190324
Hi,

Thanks for the help and guide.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you don't know how to downgrade, my instructions below should be helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now