Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Skipping (XP)

    I have a challenge with a line of code that is quite simple: selecting a sheet within the workbook.

    Example:
    Sheets("Main").Select

    When I step through or run the code the sheet is never selected.

    Any ideas as to why? I tried rebooting (warm boot) and this did not resolve the issue.

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    Odd, works fine for me. Are you sure the active workbook (!) has a sheet named "Main"? You might need to specify what workbook you want activated first.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Skipping (XP)

    Check to see if you do not have a space before or after the word "Main" in the tab of the workbook. Maybe delete the worksheet name in the sheet tab and re-type "Main" again (just to eliminate any "hidden" characters, and then run the code again to see if it solves the problem.
    Regards,
    Rudi

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Code Skipping (XP)

    Did you turn screenupdating off?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    I resolved the issue by placing a reference to the workbook in the line of code.

    Example:
    Set oWB = ActiveWorkbook.Name

    oWB.Sheets("Main").Select


    Thanks to everyone who responed.
    John

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    Rudi,

    I came across this post while looking for solution to my problem. I would like to have some code that would check the name on the worksheet tab and replace it after stripping any spaces out. This could be either just the active sheet or one that would cycle through all sheets.
    Any ideas?

    Thanks,
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    John,

    Thanks! That will make my life much easier. For some reason, the users change the tab names by inserting spaces (even though they were told not to). This will take care of it.

    Thanks again.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Code Skipping (XP)

    Like this? It operates on whichever sheets you have selected (easy enough to select all).

    Sub SetSheetNameNoSpace()
    Dim oSheet As Worksheet

    Application.ScreenUpdating = False
    For Each oSheet In ActiveWindow.SelectedSheets
    With oSheet
    If .ProtectContents = False Then _
    .Name = Replace(.Name, " ", "")
    End With
    Next oSheet

    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    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: Code Skipping (XP)

    What is the issue with spaces in the names? They work just as well as names without spaces....

    Steve

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Code Skipping (XP)

    I was gonna say the same. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Except IIRC they make =INDIRECT() references a bit tricky.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    If you want to avoid trouble with users renaming your worksheets, use the codename in your code instead of the tab name.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Code Skipping (XP)

    Further to Jan Karel's comments on using Codename, see <!post=this thread,51,358>this thread<!/post>, especially towards the end.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    Steve, Jan and John

    Sorry for the delay in responding - I took a long weekend -away from work.

    Thanks for the replies and suggestions.
    I have some code that cycles through all the sheets that have a chart embedded and updates the charts.
    This worked great until it hit a sheet that had a space in the tabname. I am not sure why it died, but it did. It took forever to figure out what was causing it.
    I as able to adjust the code to check for spaces in the tab name and remove them on the fly.
    Works great now.

    Thanks again (I really do LOVE this site).
    Chuck Reimer
    I'm from the Government and I'm here to help...

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Code Skipping (XP)

    Perhaps if you post the code lines which error out, we can find another solution.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Skipping (XP)

    John,

    No doubt there are many diffrerent ways to do it!
    I am attaching the txt file of the code that fails. At the bottom of the text file, I marked where it fails and what the error message is.
    The workbook is too big to send, however if need be, I can chop it up and send a smaller section.
    The workbook, as it is now, does work. But, you may have a better (faster, shorter, etc) method.
    The sheets each have one chart. The macro goes through each sheet and updates the chart, based on the new data that has been entered into the table that is on the same sheet. The code that failed (becuase of a space in the tabname) was supposed to replace the datatable series that showed 1 2 3 4 etc, with Jan Feb Mar Apr (as shown in the table on the sheet).
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

Page 1 of 2 12 LastLast

Posting Permissions

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