VB command to direct person to specific worksheet
VB command to direct person to specific worksheet
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VB command to direct person to specific worksheet

  1. #1
    Guest

    Default

     
    What is the command in a VB macro to get a spreadsheet opened on a specific cell in a specific sheet each time?


    Andy

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If i understand your question correctly:

    Put this in a module:

    Private Sub"your macros name"()
    Worksheets("your worksheets name").Select
    Range("your cell").Select
    End Sub

    Jim

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sometimes I like referencing a worksheet by name, which the post above handles, and sometimes by its physical location in the workbook. E.g., if you always want to go to the second worksheet (no matter what the name) try:

    worksheets(2).select

    and so forth.

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-27 19:41 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Advice Nate with my example if the sheet
    name was changed Andy would be up the creek!

    Jim

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've seen it happen James, and vice versa, where you always want a sheet no matter where the location...Referencing worksheet #'s does get a little trickier when hidden sheets are involved (i.e., you think you're working with the 2nd sheet and it's really the third), but I'll save that for another post. Have a good one James.

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-27 19:39 ]

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 19:36, NateO wrote:
    I've seen it happen James, and vice versa, where you always want a sheet no matter where the location...Referencing worksheet #'s does get a little trickier when hidden sheets are involved (i.e., you think you're working with the 2nd sheet and it's really the third), but I'll save that for another post. Have a good one James.

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-27 19:39 ]
    Try using the Sheets Code name for situations
    like this....ie instead of Sheet(2) which is the 2nd sheet where as Sheets2.Acivate activates the Sheet code named Sheet2 and not the 2nd sheet.
    The Sheets code name you will see in the project explorer to the left of the sheet tab name you cannot change the sheets code name except through code or manually changing while in the VBE.

    Have a look @ project explorer you should see
    something like

    Sheet1(Sheet1) or Sheet1(Tabname)

    the name to the left will not change...this
    is how you should access the sheet if you want to be bable to change the Tab name without effecting the code.
    So...........

    Works ALL the time no matter what you change the Tab name to.
    Sub test()
    Sheet1.Activate
    End Sub


    Works all the time unless the user changes the sheet tab name.
    Sub test2()
    Sheets("Sheet1").Activate
    End Sub

    Works all the time IF you want to Access the 1st sheet ONLY irrespective of the sheet name
    Sub test3()
    Sheets(1).Activate
    End Sub


    Ivan

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Nice, I'll give this a try:

    On 2002-02-27 20:11, Ivan F Moala wrote:

    Works ALL the time no matter what you change the Tab name to.
    Sub test()
    Sheet1.Activate
    End Sub
    So this works whether you change the name of the sheet or its physical location in the workbook, nice.

    I had actually created a routine to shift all hidden worksheets to the last sheets of the file which allowed me to identify their order (based on what you see), but wasn't as hot when one unhides them (didn't necessarily want to rearrange the workbook).

    Thanks for the insight. Cheers,

    Nate

    [ This Message was edited by: NateO on 2002-02-27 20:52 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com