Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Incremental sheet names in a workbook
I want to have a button on an excel sheet. This button can be on the
sheet or could be on a floating bar, most preferably a floating bar to reduce workbook size. Name of first worksheet is let's say '1'. On press it should generate another sheet with name '2' having same button on sheet '2' as well. The workbook should also be able to logically detect the last or greatest sheet number value in the workbook so that the name of the added sheet is n+1. If sheets are 62,63,64,65,66,67,68 & the last sheet is 68, then the next sheet should be 69 even if I press the button on let's say sheet 65. This workbook may also have other sheets with alphanumeric names like List8, Bookings etc. The process should not alter with these sheets. Finally, to have a sequential sheet names, protection to edit sheet name is also required form any accidental change of sheet name, like rename sheet to abc1, abc22, etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Incremental sheet names in a workbook
I'm not aware of a way to protect the sheet names. You could create a macro
to add a sheet at the end. Try something like this: Sub AddSheet() Dim aWB As Workbook Dim aWS As Worksheet Set aWB = ActiveWorkbook Set aWS = Sheets.Add aWS.Name = "Sheet" & aWB.Worksheets.Count 'You could change "Sheet" to something else here aWS.Move after:=Sheets(Sheets.Count) End Sub "Sinner" wrote: I want to have a button on an excel sheet. This button can be on the sheet or could be on a floating bar, most preferably a floating bar to reduce workbook size. Name of first worksheet is let's say '1'. On press it should generate another sheet with name '2' having same button on sheet '2' as well. The workbook should also be able to logically detect the last or greatest sheet number value in the workbook so that the name of the added sheet is n+1. If sheets are 62,63,64,65,66,67,68 & the last sheet is 68, then the next sheet should be 69 even if I press the button on let's say sheet 65. This workbook may also have other sheets with alphanumeric names like List8, Bookings etc. The process should not alter with these sheets. Finally, to have a sequential sheet names, protection to edit sheet name is also required form any accidental change of sheet name, like rename sheet to abc1, abc22, etc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert Incremental sheet names in a workbook
If the workbook is protected (tools|Protection|protect workbook with the
Structure checked), then the user can't move, copy, insert, delete, rename worksheets. (See note.) And if your workbook always has worksheets named 1, 2, 3, ... and nothing more, you can do this: Option Explicit Sub testme() Dim myPWD As String myPWD = "hi" With ThisWorkbook .Unprotect Password:=myPWD .Worksheets.Add.Name = .Worksheets.Count + 1 .Protect Password:=myPWD End With End Sub Note: Workbook protection is easily broken. This ain't fullproof. Second, you'll want to protect the code, too--so that your password is hidden. Inside the VBE: Tools|VBAProject Properties|Protection tab. This password is easily bypassed, too. Sinner wrote: I want to have a button on an excel sheet. This button can be on the sheet or could be on a floating bar, most preferably a floating bar to reduce workbook size. Name of first worksheet is let's say '1'. On press it should generate another sheet with name '2' having same button on sheet '2' as well. The workbook should also be able to logically detect the last or greatest sheet number value in the workbook so that the name of the added sheet is n+1. If sheets are 62,63,64,65,66,67,68 & the last sheet is 68, then the next sheet should be 69 even if I press the button on let's say sheet 65. This workbook may also have other sheets with alphanumeric names like List8, Bookings etc. The process should not alter with these sheets. Finally, to have a sequential sheet names, protection to edit sheet name is also required form any accidental change of sheet name, like rename sheet to abc1, abc22, etc. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you make a summary page showing the workbook name with the excel sheet names | New Users to Excel | |||
create tally sheet for positions- insert names and tally # | Excel Discussion (Misc queries) | |||
How do I insert a new work sheet into an existing workbook? | Excel Discussion (Misc queries) | |||
insert the same header in every sheet in a excel workbook | Excel Discussion (Misc queries) | |||
How do I display list of tab names used in a workbook on a sheet | Excel Worksheet Functions |