Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Ouput Worksheet Problem (Excel 2002/2003)

    Hi

    Hans very kindly supplied me with the attached code, which appearded to work fine, however if select more than one sheet I get a run run-time error 1004 Application defined or object defined error.

    I am not sure where to start looking to correct this. wsT.Shapes(i).Delete (Code Stops Here)

    Any Ideas Please

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    The code works OK when I try it, so we'd need to see (a stripped down copy of) the workbook.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    Hi Hans

    I not sure how I could strip it down because it's 15Mgb I will see what I can do.

    NB If I only select one sheet at a time it works fine it's only it I select more than one, it appears to delete the buttons on the 1st sheet and cannot delete the buttons on then next sheet.


    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    You might create a new workbook with just two worksheets and a few buttons, as long as it demonstrates the problem.

    (As I wrote, the code works correctly when I try it on a workbook in which multiple sheets have forms controls such as buttons.)

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    Hi Hans

    I could not get the workbook small enough to post, however as you suggest it does work, because when I stripped it down, your code of course worked perfectly.

    So it must be something I have added to the problem sheets after the code was written. I will have to try and solve this myself. (if I live long enough) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    Hi Hans

    I have had another thought on this!

    There are 3 offending worksheets named, "Customer Price List", "Pricing Form", "Product List", would it be possible to adapt your code to omit these 3 sheets from the visible list?

    Hope I am not asking to much here. <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    The UserForm_Initialize procedure contains

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    Me.lbxSheets.AddItem ws.Name
    End If
    Next ws

    Change this to

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    Select Case ws.Name
    Case "Customer Price List", "Pricing Form", "Product List"
    ' Don't add to list box
    Case Else
    ' Add to list box
    Me.lbxSheets.AddItem ws.Name
    End Select
    End If
    Next ws

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Ouput Worksheet Problem (Excel 2002/2003)

    Hi Hans

    That is excellent, Thank you so much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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