Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Unprotect and Reprotect VBA Project

Posted on 2010-11-14
18
Medium Priority
?
2,442 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 93

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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 93

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 93

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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 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 1200 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses

886 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