Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cleaning out a workbook (2000 sp-3/ 98SE)

    The attached workbook is pretty well stripped bare. But I can't get rid of the "macros" associated with the option buttons. I'm convinced these referenced macros don't exist, but how to get rid of the references and prevent the Enable Macros? prompt appearing each time such a workbook is opened?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    Right-click the worksheet tab and select View Code. There is an empty Worksheet_SelectionChange event procedure. If you remove this procedure, you'll no longer get the macro warning.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Many thanks Hans. That was driving me crazy. I saw that "empty" code in the wb from which that sheet was copied (to the new empty wb) but I didn't think it would also copy across in the VBA module. On reflection, since it belongs to the sheet, I guess that's appropriate. Now the problem would appear to be how to prevent it being carted across, or using VBA from within the original wb to subsequently remove it. I think the best might be to just delete it from the original - it obviously serves no purpose in its present form.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> again. You've saved me a couple of aspirins!

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    If you create a new sheet and copy the cell contents over, instead of copying the sheet, you should not have this problem

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    I found the method I used was the most convenient to copy over TextBoxes and other Shapes that are part of the original sheet. If there's a better method, which would grab everything on the sheet and copy it, I'd be interested in using it though.

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    Some of the downsides of copying the sheet as a whole (just to make sure you and other loungers are aware of some of them):
    You get the code (as you noted)
    You can get links to the original workbook from any names in the book that are not on the sheet
    Any cells with more than 255 chars in the cell will be truncated

    If you use a line like:
    <pre>Worksheets(2).cells.copy Worksheets(1).cells(1)</pre>


    it should copy the shapes as well as the cell contents.

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    Thanks Steve. I gave that a quick whirl, but found that the print settings were not carried over. There appears to be a lot of customization been done here, so it may not be worth trying to copy all that across too. Unless there is an easy method?

    Alan

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    Neither way seems "perfect": both have limitations and problems. It comes down to what you want to carry over and the easiest way around the "quirks".

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning out a workbook (2000 sp-3/ 98SE)

    Yep <img src=/S/yep.gif border=0 alt=yep width=15 height=15> - it looks like horses for courses.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Alan

Posting Permissions

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