Hi Paul,
Sorry about that. The password is "apple", to unprotect the VBA
project; you were closer than you thought:)
Thanks so much!
Kent
________________________________
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Paul Schreiner
Sent: September 20, 2011 12:08 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Template to modify workbooks
I won't be able to work with it unless I can unprotect the VBA project.
I tried "pineapple" but it didn't work..
perhaps "grapefruit"? "Mangos"? "UglyFruit"? (just kidding)
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: "Boucher, Kent C" <kent.bouc...@tbs-sct.gc.ca>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Tue, September 20, 2011 10:11:59 AM
Subject: RE: $$Excel-Macros$$ Template to modify workbooks
Hi Paul - thanks so much for your time!!
I have attached the workbook that is going to be coming back to 20,000 times
titled "Reference Excel Workbook", which contains the macros that I want to
disable. Yes they are "Event" type macros, and they are in "ThisWorkbook"
module.
The password for this workbook and worksheets is "pineapple".
Is there a quick and simple way of creating a template to diable the macro and
to unprotect the workbook and worksheets?
I have attached the "Reference Book Template", which is the workbook I would
like to use to open and diasable the "Reference Excel Workbook" macro and
protection.
Your time and energy is much appreciated!!
Thank you,
Kent
________________________________
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Paul Schreiner
Sent: September 20, 2011 9:26 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Template to modify workbooks
Since these are "Event" type macros, they are probably in the "ThisWorkbook"
module.
You cannot delete the MODULE, but you can delete the macros within it.
try this:
Sub Remove_Test()
Dim cnt, wbName
Application.EnableEvents = False
wbName = "VBAtest.xls"
Set vbaModule = Workbooks(wbName).VBProject.VBComponents
cnt =
Workbooks(wbName).VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
Workbooks(wbName).VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines
1, cnt
End Sub
basically, you'll want to place this within a loop in which you open a workbook,
set wbName = the workbook name...
delete the macros
the perform your other actions and save the file.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: HarryP Knuckles <kent.bouc...@tbs-sct.gc.ca>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, September 20, 2011 8:55:49 AM
Subject: Re: $$Excel-Macros$$ Template to modify workbooks
Good morning Paul,
Thank you for taking interest in my issue - I really appreciate it!
To answer your questions:
1) Yes, ideally I would like to disable the macro permanently and then
be able to save the disabled version. Having said that, if it is much
easier to complete the task by leaving the macro in place, then I can
work around that.
2) I am using MS Excel 2003 to create the template.
Any advice you can provide will be of huge assistance - thank you!
On Sep 19, 4:10 pm, Paul Schreiner
<schreiner_p...@att.net<mailto:schreiner_p...@att.net>> wrote:
> Are you wanting to disable the macros "permanently" and save the file?
> or are you just needing them disabled so you can extract the information you
> need?
>
> If you just need them deactivated temporarily,
> you can create a macro that issues:
> Application.enableevents = false
>
> then, open each of the 20,000 workbooks,
> do whatever needs done,
> close the workbook.
>
> If you're wanting to REMOVE the macros from the workbook.
> That gets a little trickier.
>
> What version of Excel are you using?
> There may be some options there (like saving as .xlsx, which would remove all
> macros)
>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>
> ________________________________
> From: HarryP Knuckles
> <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca>>
> To: MS EXCEL AND VBA MACROS
> <excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>>
> Sent: Mon, September 19, 2011 3:58:32 PM
> Subject: $$Excel-Macros$$ Template to modify workbooks
>
> Good afternoon,
>
> I have 20,000 workbooks that are going to be returned to me shortly
> and i need a template from which i can open the workbooks
> (individually) and have a marco to perform three necessary functions
> on all of these workbooks (each workbook will have a unique name). My
> objective is to:
>
> 1) Unprotect the workbooks.
> 2) Unprotect the worksheets.
> 3) Disable a macro that is in each of the original workbooks (20,000)
> designed to prevent right click, cut, copy and paste.
>
> I have included below the macro that is present in all 20,000
> workbooks that i need to have diabled through the new template
> workbook. Can you folks provide some guidance as to how i can
> complete
> this task using a template.
> Your help is greatly appreciated!!!
> ___________________________________________________________________________
> ________________
>
> Private Sub Workbook_Activate()
> Application.CutCopyMode = False
> Application.OnKey "^c", ""
> Application.CellDragAndDrop = False
> Application.CommandBars("Ply").Controls("View Code").Enabled = False
> End Sub
>
> Private Sub Workbook_Deactivate()
> Application.CellDragAndDrop = True
> Application.OnKey "^c"
> Application.CutCopyMode = False
> End Sub
>
> Private Sub Workbook_WindowActivate(ByVal Wn As Window)
> Application.CutCopyMode = False
> Application.OnKey "^c", ""
> Application.CellDragAndDrop = False
> End Sub
>
> Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
> Application.CellDragAndDrop = True
> Application.OnKey "^c"
> Application.CutCopyMode = False
> End Sub
>
> Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
> Target As Range, Cancel As Boolean)
> Cancel = True
> MsgBox "Right click menu deactivated." & vbCrLf & _
> "Cannot copy or ''drag & drop''.", 16, "For this workbook:"
> End Sub
>
> Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
> Target As Range)
> Application.CutCopyMode = False
> End Sub
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> Application.OnKey "^c", ""
> Application.CellDragAndDrop = False
> Application.CutCopyMode = False
> End Sub
>
> Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
> Application.CutCopyMode = False
> End Sub
>
> --
> ----------------------------------------------------------------------------------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to
> excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to
excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at
http://www.excel-macros.blogspot.com<http://www.excel-macros.blogspot.com/>
4. Learn VBA Macros at
http://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com/>
5. Excel Tips and Tricks at
http://exceldailytip.blogspot.com<http://exceldailytip.blogspot.com/>
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at
http://www.excel-macros.blogspot.com<http://www.excel-macros.blogspot.com/>
4. Learn VBA Macros at
http://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com/>
5. Excel Tips and Tricks at
http://exceldailytip.blogspot.com<http://exceldailytip.blogspot.com/>
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
