+ Reply to Thread
Results 1 to 8 of 8

Unwanted buttons showing up in new sheet created with a Macro and Template sheet

  1. #1
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Ok, so I have a worksheet which I have created a few macros within. This worksheet is going to function as a template for others to copy and make additional worksheets.

    I figured it would us some time to create a macro that does this for us and put a button on the template sheet that copies the template into the same worksheet and makes renames it to "RENAME ME".

    Worked fine, however, when I run the macro by selecting the "Create New Sheet from Template" button, I am getting unwanted buttons showing up right above the orginal buttons, both in the template, and the new sheet.

    Any ideas on how I can correct this??? Thanks!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Hi,
    When you copy entire sheets and do a simple paste, shapes are copied and pasted too.
    Try to change your macro to Paste Special and only paste Values or Formulas but not ALL.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Quote Originally Posted by MarvinP View Post
    Hi,
    When you copy entire sheets and do a simple paste, shapes are copied and pasted too.
    Try to change your macro to Paste Special and only paste Values or Formulas but not ALL.
    That is part of my problem. I am not experienced enough in VBA to be able to modify my Macros to do what I want them to do. I actually used the macro recorder. I started recording and simply right clicked on my template worksheet (which has numerous macros that I made for the sheet) and clicked the Move or Copy selection, then selected the worksheet, checked the Create a Copy box, and made a copy of the workbook. I then renamed the new tab "RENAME ME" and changed its color.

    Here is the code from my Macro if it helps:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Hi -
    Your code adds some buttons to the "Template - In Town" sheet then adds a new sheet and copies the first to the new one. Then it renames the new sheet to "Rename Me"

    Here is a small addition to your code that will remove the buttons before it adds them. This should keep them from multiplying like rabitts.
    Please Login or Register  to view this content.
    We really need more of the problem to see how creating a new worksheet helps. Also I don't see how any of these buttons are assigned to macros. I believe the creator of the Template, expected you to run it once and save it to another workbook name and not to save the template itself.

    It is just hard to tell what the code does in the big picture, much less how the Template fits in as well as the new worksheet.

  5. #5
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Quote Originally Posted by MarvinP View Post
    Hi -
    Your code adds some buttons to the "Template - In Town" sheet then adds a new sheet and copies the first to the new one. Then it renames the new sheet to "Rename Me"
    Not really sure why it would be adding additional buttons to the "Template - In Town" sheet. Maybe if I explained how I got here it would help. Basically, I used the record macro feature and simply right clicked on the "Template-In Town" sheet, selected move or copy, selected the template sheet, and checked the create a copy box. I then renamed the new sheet to "RENAME ME" and changed the tab color and pressed Stop recording. Unfortunately, my knowledge of writing macros is limited to this record macro feature (maybe someday I will learn more), so I am not sure how or why it would want to remake the buttons again.


    We really need more of the problem to see how creating a new worksheet helps. Also I don't see how any of these buttons are assigned to macros. I believe the creator of the Template, expected you to run it once and save it to another workbook name and not to save the template itself.

    It is just hard to tell what the code does in the big picture, much less how the Template fits in as well as the new worksheet.
    I tried the code you provided but I am getting a runtime error 438.

    To help with the big picture, I have attached the workbook. Basically, I am just trying to make an Job bidding sheet at my company a little more user friendly by adding some drop down lists, buttons linked to macros, etc. Previously, they would use the same template (less the validation lists, vlookups, etc that are there now) and would simply copy a new template and rename it to start a new bid. The original template is kept in the same workbook as are all of the bids for each year. I plan on keeping the template within the workbook, but just wanted to create a button that would save the time it takes to create a copy of the template.

    To summarize, I would like to add a button on the very top of the "Template-In Town" worksheet, that says "Create a New Bid Using Template" or something along those lines which is attached to a Macro, which just copies the template in a new sheet, renames it to "RENAME ME" and changes the tab color from red to no color.

    Thanks so much for your patience and I appreciate all the help!
    Attached Files Attached Files

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Hi fsurf02,
    Now I understand the problem. You have a great idea to produce a standardized worksheet for making bids or invoices, etc. You decided you needed VBA to create this and recorded a macro from the template sheet. You're looking for the next step to clean up your VBA code so it is useful. This is a great idea and many companies have this ability. The problem is that you need to varaiableize your macro code to fit your template.

    Specifically, when you add a Material and follow the Macro, it inserts a row and then shifts all rows down. When this happens it messes up the rest of the macros for adding other things.

    This is the first time I have ever suggested this, but IMHO you will need to hire a VBA programmer to work through your recorded code and make your process work.

    The other alternative it to learn enough VBA to do it yourself. You are looking at a few day job for a good programmer or a few weeks to develope, test, redesign, and get a final product.

    Perhaps another of the Forum Gurus has a similar application that they have put together and can give you as an example.

  7. #7
    Registered User
    Join Date
    02-14-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    Quote Originally Posted by MarvinP View Post
    Hi fsurf02,
    Now I understand the problem. You have a great idea to produce a standardized worksheet for making bids or invoices, etc. You decided you needed VBA to create this and recorded a macro from the template sheet. You're looking for the next step to clean up your VBA code so it is useful. This is a great idea and many companies have this ability. The problem is that you need to varaiableize your macro code to fit your template.

    Specifically, when you add a Material and follow the Macro, it inserts a row and then shifts all rows down. When this happens it messes up the rest of the macros for adding other things.

    This is the first time I have ever suggested this, but IMHO you will need to hire a VBA programmer to work through your recorded code and make your process work.

    The other alternative it to learn enough VBA to do it yourself. You are looking at a few day job for a good programmer or a few weeks to develope, test, redesign, and get a final product.

    Perhaps another of the Forum Gurus has a similar application that they have put together and can give you as an example.

    Thanks for the recommendation. Unfortunately, it is a rather small business and we cannot afford to do such a thing. I was just hoping to brush up a few of the worksheets given knowledge I have with excel (albeit limited). I was able to figure out my inital problem with copying the new template with the button (and eliminating the additional buttons).

    I do see what you were talking about with my macros to add a line. Someone in another thread showed me a pretty helpful macro to do what I was trying to do in a different manner. If you are interested, the code is as follows:

    Please Login or Register  to view this content.
    Thanks again for the help.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unwanted buttons showing up in new sheet created with a Macro and Template sheet

    It isn't a single macro problem. The macros need to vary based on which row you are moving thing to. The design of your bid sheet, although very human readable may not be the best for Excel to deal with. How you create a blank workbook for others to enter data is also a concern. How much of the "Template" goes along with the workbook.

    All the above combined together, to get a process that works for your company. Each company is a little different. Some have a few parts while others have thousands. Does the hours worked or cost of materials need to be shown and calculated. So many things to consider.

    I really think you bit off a little more than you were ready for. If you have a standard in-house excel format, you should post it with a single question, like how to I get grand total, and see what we can do for you. This example, like the one you posted above would help us make suggestions how Excel might work for you.

    We can help but it is a growing process.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1