Dear Forum,
I need to Add & Rename Sheets a particular no of Sheets Dynamically using a Command Button..
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
Please find the attachment below..I owuld appreciate if someone adds a new attachemnt as Im not good in VBA...
Last edited by e4excel; 11-13-2009 at 03:19 AM.
Please see attachment. I made the range C2:C22, but you can make it any range you want. I imagine you wouldn't want more than 20 sheets, but you never know.
Please Login or Register to view this content.
Last edited by Whizbang; 10-28-2009 at 03:43 PM.
Hi all,
I recommend changing the name of the variable which is dimmed as "Cells" because this word has an existing meaning* in Excel's VBA & you may confuse Excel &/or yourself by using this approach. A common technique to prevent this confusion is to remove the vowels eg.Please Login or Register to view this content.
*(ie as an Excel Keyword "Cells" means all cells on the defined sheet or the active sheet if the object container (eg "worksheets("sheet1").cells") is not specified))
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Dear Whizbang,
Its not working the way I wanted as I need to type all the names and then press the command button to get all the Sheets at one go whereas I want it to be added one by one if possible..
Like I type Apples now and click on the command button to get a New Sheet after the Main Sheet - with the name Apples and then after sometime I type in Bananas in the Cell C3 then press button and get only a New Sheet called Bananas....
Ok, here are some examples for you. The attached sheet does much more than you asked. I liked the idea of dynamically controlling sheets from one master sheet, so I played around with it. The attached sheet will allow you to add and/or rename a specified sheet, like you asked, but you can also have it run down the list and add and/or rename as needed. Also, the button on the Main Sheet reflects the sheet # being added/renamed.
I ran into some trouble when I was deleting and adding sheets, so I wrote some code that goes through and re-syncs the sheetnames with the codenames.
So, anyway, take what you can from the attached.
Below is the bare-basics of what you asked for. If there is a sheet already named what is given, it will cause an error. Also, it will not rename any sheets.
*** Edit ****Please Login or Register to view this content.
Oh yeah, the attached workbook assumes that you have Tools->Options->Security->Macro Security->Trusted Publishers->"Trus access to Visual Basic Project" checkbox checked. If it is not checked, the re-syncing of sheet.codenames and sheet.names will cause an error when Main Sheet is activated.
Last edited by Whizbang; 10-29-2009 at 03:29 PM.
Thanks a lot Whizbang, you actually over-exceeded my expectations..I liked the idea of having two command-butttons for Adding each sheet or at one-go...
But the problem arises when I tried to delete the Sheets and due to my weakness in VBA cant really repair that...
Can the Sheets be deleted via a button...This is just bcos of your splendid efforts...that I though of getting it done with a command button or maybe having a cheque-box or Dro-down having an option of delete in column D next ...to each Sheet Name in the Main SHeet..
What Say , is it possible?
Last edited by shg; 10-30-2009 at 06:13 PM. Reason: deleted spurious quote
Not only is it possible, but I'll get to work on it right away. I had the same thought after my last post and have given it a little thought. At the very least it will function the same as the single add, where you enter the number of the sheet and hit the "Delete" button.
I'll see what I can do about testing the sheet for "Delete" values when you update all.
As for the sheet I attached, what were the errors you were getting when deleting the sheets manually? I thought I had it set to work just fine. The one stipulation is that you have to clear the values from the Main Sheet before you delete the actual tab, otherwise when you activate the main sheet, it'll just re-create the sheets you just deleted.
You are right I had not flushed the values and was getting the Regukar END DEBUG error, sorry but since I am no good in VBA at all not able to even check or handle the errors or even explain them..
But honestly very satisfied with your efforts as this gives complete control to the user from the Main Sheet...
CAn something also be worked out whereby we reduce the no of command buttons , but do not know how, Maybe some toggle-switches I dont know how but since you are good in VBA maybe you can come up with something...
Last edited by shg; 10-30-2009 at 06:12 PM. Reason: deleted spurious quote
I ran into a small issue with deleteing pages (The whole thing falls apart!) and so I will have to take this up again on Monday.
I can get it to delete the pages fine. But, the issue comes when you delete the line in the Main Sheet. Then attempt to click the "Update All" button again. You might have to settle for having no formulated column B, meaning once a sheet is given a Sheet number, it sticks for good.
As a matter of fact, that's the direction I am going to take this, unless you have a strong need to have the sheets numbered sequencially?
Can we delete the Name and the Sheet at the same time to surpass the error, unfortunately Im not in a position to gauge the difficulties in VBA but just a suggestion may be by clearing contents of the selected Sheet Name and by Deleting the Sheet at the same time..
Sequential Numbering is just for logical purpose and no other reason..CAn the Sheet Numbers be made to be equal to the values in Column A i.e "Sheet "& A2
Last edited by shg; 10-30-2009 at 06:12 PM. Reason: deleted spurious quote
e4excel,
Please refrain from quoting whole posts. It's just clutter.
Thanks.
Entia non sunt multiplicanda sine necessitate
Dear Shg,
I am sorry for the inconvenience , going forward I shall take care of that and use quotes only for the relevant portion..
Warm Regards
e4excel
The errors comes not during the deleting, but upon the next update. I already solved this, but won't be able to upload it until monday, as the file is on my work PC.Originally Posted by e4excel
I am going to change the resync subroutine monday morning to handle the sheet numbers (codenames) so they appear sequencial in the Main Sheet. There is no real need for it, but I like seeing the number line up. Plus it makes it easier when you are choosing the row to delete by number.
Anyway, you'll see the finished product Monday.
NO problemo...!
Just enjoy your weekend..
Warm Regards
e4excel
Ok. Got all the bugs I could find worked out. I don't have any notes in the code to help you follow it. If you want, I'll take the time at some point this week to add notes. But I figured you'd want a working copy to look at and learn from in the mean time.
What this file does:
*Add a sheet or sheets (one at a time or all at once)
*Rename existing sheets dynamically (one at a time or all at once)
*Delete existing sheets (one at a time or all at once)
*Automatically adjusts sheet.Codenames to be sequencial.
How to run:
To add a new sheet: In column C, go to first blank line and enter a name for the new sheet. In cell G1, enter the row number for the new sheet (or type "All") and click the button
To rename a sheet:In column C, go to desired row and edit the name. In cell G1, enter the row number for the sheet to be renamed (or type "All") and click the button
To delete a sheet: Enter an x (Or anything but a null value) into column D of the desired row. In cell G1, enter the row number for the sheet being deleted (or type "All") and click the button
Last edited by Whizbang; 11-02-2009 at 11:31 AM.
Dear Whizbang,
Thanks for your efforst and I followed the instructions verbatim however its still showing the nasty Error Window END DEBUG...
I did try updating the Rown Number but its giving an error, I leave it upon you sa I will be on a small tour till the Weekend and wont be able to access or check the final results only till Monday of next week...
Thanks a lot...God Bless...
Warm Regards
e4excel
Do you mind telling me what you were trying to do at the time of the error? Add, delete, update? When you click "Debug" what is the highlighted code?
Did you go to Tools->Options->Security->Macro Security->Trusted Publishers and select the "Trust access to Visual Basic Project" checkbox? If not then the Excel won't let you update the sheet CodeName.
These two lines...
ThisWorkbook.VBProject.VBComponents(shts.CodeName).Name = "Sheet" & SheetCount
If SheetTest = False And Sheets("Main Sheet").Range(SheetsAddRename.Address).Offset(0, 1).Value = "" Then
I have selected the Right options in Security...
See ya on Sun/Mon ?
I don't know if I fixed your error, but I fixed AN error dealing with the code you provided. What happened was that I had used the same variable name in two different sub routines. Ordinarily this wouldn't matter, except that one sub was called from the other, causing a conflict in the variable. So, I renamed it and it appears to be working just fine.
I also fixed a small glitch that occurred when you were deleting multiple sheets at once. The sheets were deleting just fine, but the values in the Main Sheet would sometimes remain, if the two sheets were listed consecutively. I added a test to see if any "x" remain in the delete column and, if so, run DeleteRows again.
Hi Whizbang..! I am back..and I checked the file...
Liked the idea of having a Cross " X" next to the Sheet name which needs to be individually deleted..
However it still goes in the END DEBUG mode...
After I put X a new Sheet was added and then the added Sheets were Deleted but not without displaying the Error Windows..First..
...thread moved to Programming Forum (from Worksheet Functions)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I am really not sure what you mean. Do you mind clarifying?
Are you able to add sheets?
Are you able to rename existing sheets?
Are you able to delete existing sheets?
If the answer is "no" to any of these, then please explain in detail the state that your workbook was prior to clicking the button (number of existing sheets, what information was displayed on the Main Sheet, etc). Then provide as much detail as to the state of your workbook after you clicked the button and the error appeared (Did the sheets get added/renamed/deleted? What did the Main Sheet look like? etc.)
1. Yes I am able to Add Sheets, but I still get the error Error 9' - Subscript out of Range.
2. No I am not able to Rename Sheets however a new sheet gets added with the New Name...Please Login or Register to view this content.
No updatation happens but a New Sheet still gets added with the Name which was supposed to be a New name for the Existing Sheet..
3. Deletion does not happen when I type X in the Column D and click on the button.. only the cells in the Column B & D get cleared...
As per the Forum Rules have encoded the Relevant PortionsPlease Login or Register to view this content.
Last edited by e4excel; 11-10-2009 at 05:00 AM. Reason: As per the Forum Rules have encoded the Relevant Portions
I am still working on this. I am able to reproduce your errors. The problem I am having is that when I run the code line by line, everything works just fine. The problem occurs when the macro is run all at once.
I just can't figure it out. Why would it run fine line by line, but not all at once?
No problems Whizbang Take your time..
Thanks to Andy Pope for giving me the bit of code that I needed.
See this thread: http://www.excelforum.com/excel-prog...-not-open.html
See the attached for a working example.
Last edited by Whizbang; 11-12-2009 at 03:00 PM.
Thanks a lot Whizbang...
You are absolutely amazing as you were so perseverant in getting this solved..! You did not hang up till this was solved which shows a lot of about your character...
Philanthrophy at its best...
I would also like to thank Andy in helping out Whizbanh who could help me out..
I think it works out very well.
Warm Regards
e4excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks