Ashish you have the patience of a saint!! The workbook layout is perfect! Your book1 unprotects each of the worksheets perfectly! Is it possible to have your book1 unprotect the workbook itself (same password, "apple")?
Is there anyway to stop or pause the macro in the returned workbook while the new macro workbook has it opened? Ashish - YOU really have been a life saver! Thanks so much for your help! Sincerely, Kent ________________________________ From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of ashish koul Sent: September 20, 2011 1:07 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Template to modify workbooks hi Kent Try this one On Tue, Sep 20, 2011 at 10:31 PM, Boucher, Kent C <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca>> wrote: Hi Ashish, The workbook that you sent back looks awesome! When I attempt to use it; I receive a run-time error '1004' for not having the correct password. I have tried to edit the marco without any luck. Is it possible to change the setting to have "apple" as the only password (all workbooks are coming back with the password "apple" as the password to unprotect the workbook and worksheets. I so much appreciate what you have done for me! Thank you a million!! Kent ________________________________ From: excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> [mailto:excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>] On Behalf Of ashish koul Sent: September 20, 2011 12:26 PM To: excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> Subject: Re: $$Excel-Macros$$ Template to modify workbooks Hi Kent try this open and run the macro choose the workbook . it will save the template file in the same directory as of source file to view the code of this macro press single space On Tue, Sep 20, 2011 at 8:30 PM, Boucher, Kent C <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca>> wrote: Thank you Ashish - Yes I just send a copy of the workbooks to them to have them posted. I believe the workbook is up now - thanks for the suggestion, much appreciated! Regards, Kent ________________________________ From: excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> [mailto:excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com>] On Behalf Of ashish koul Sent: September 20, 2011 10:56 AM To: excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> Subject: Re: $$Excel-Macros$$ Template to modify workbooks HI Kent if u want to share ur workbook within the group write a mail to excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> and attach ur wrkb On Tue, Sep 20, 2011 at 6:34 PM, HarryP Knuckles <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca>> wrote: Good morning Ashish! Thanks you for taking the time to assist with my issue - really appreciated! All the workbooks are currently in *.xls format. My objective is to create a template workbook that is will be used to CONTROL and change (unprotect the workbook and worksheets) the *.xls files. I would like to share with you a sample of the workbook but as I am kind of new at posting messages and I can not see how to attach my workbook. On Sep 19, 9:21 pm, ashish koul <koul.ash...@gmail.com<mailto:koul.ash...@gmail.com>> wrote: > Hi Harry > > 1 are all the workbooks in xlsm or xls format is it possible for u to share > a sample workbook > > On Tue, Sep 20, 2011 at 1:28 AM, HarryP Knuckles > <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca> > > > > > > > wrote: > > 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<http://www.excel-macros.blogspot.com> > > 4. Learn VBA Macros > > athttp://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com> > > 5. Excel Tips and Tricks > > athttp://exceldailytip.blogspot.com<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 > > -- > *Regards* > * * > *Ashish Koul* > *http://www.excelvbamacros.com/* > > P Before printing, think about the environment.- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- 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 -- Regards Ashish Koul http://www.excelvbamacros.com/ P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- 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 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 -- Regards Ashish Koul http://www.excelvbamacros.com/ P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- 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 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 -- Regards Ashish Koul http://www.excelvbamacros.com/ P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- 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
