I now realise I'd like to prevent the deletion of selected sheets as
well. I cannot see any event which I could trap which would prevent a
user from deleting a sheet. Any ideas?
On Jun 7, 2:22 am, sudhi na <nadab...@gmail.com> wrote:
> dear Harpreet & Jerry,
>
> here is file in which you cannot rename any of the sheets,I think.
> Harpreet's second method is used here for all sheets
>
> regards
> -Sudheer
>
> code:
> Public sheetname As String
> Private Sub Workbook_Open()
> sheetname = ActiveSheet.Name
> End Sub
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> sheetname = Sh.Name
> End Sub
>
> Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
> n = Sh.Name
> Sheets(n).Name = sheetname
> If n <> sheetname Then
> MsgBox ("Attempt to rename the previously selected sheet was cancelled")
> End If
> End Sub
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> shn = ActiveSheet.Name
> If shn <> sheetname Then
> ActiveSheet.Name = sheetname
> If shn <> sheetname Then
> MsgBox ("Attempt to rename the current sheet was cancelled")
> End If
> End If
> End Sub
>
> On Sun, Jun 6, 2010 at 10:43 AM, Harpreet Singh Gujral <
>
>
>
> harpreetguj...@gmail.com> wrote:
> > Jerry,
>
> > Two ways, if adopted can help protect the sheet the way you want to:
>
> > 1) Code should be used to hide, Renaming sheet option on the worksheet
> > menu bar i.e. going to Format ---> Sheets-----> Rename
>
> > 2) On worksheet_deactivate event can be used to rename the sheet back
> > to the same name it was before renaming it
>
> > The first code can exist in the Auto_open event, while a similar code
> > to reverse the step performed in step 1 should be included in the
> > workbookclose event
>
> > I am not very strong with the coding of the commandbars, but i beleive
> > members will post something with respect to this idea....
>
> > I hope this will help, these are some of the solutions i thought of
> > while working on your problem, i hope they work good
>
> > Harry
>
> > On Jun 5, 8:30 am, Jerry <centau...@live.co.uk> wrote:
> > > The subject says it all. I want to prevent the user from renaming a
> > > couple of worksheets in a workbook but I do not want to prevent the
> > > user from modifying the data on the worksheet.
>
> > --
>
> > ----------------------------------------------------------------------------------
> > 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
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 7000 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
>
>
> sheet_rename_protect.xls
> 41KViewDownload- 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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
