How do I ask the user to click on a sheet and have that shee
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How do I ask the user to click on a sheet and have that shee

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Louisville, Ohio
    Posts
    248
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How do I ask the user to click on a sheet and have that sheet name be stored in a variable that I can later use in a statement like...

    Let MyVariable = the worksheet name that the user selected.

    Worksheets!MyVariable.Select


    'Then do some processing with with this worksheet

    Any point in the right direction would be appreciated.

    Thanks,

    David


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This is my idea, not necc the best
    The following code will prompt the user to click 'Ok' and select a sheet.
    If none is chosen after a few seconds, the prompt will re-appear.
    If a sheet is selected, the loop will end and the Public variable SheetNameToSave will store the value of the selected sheet for you to use in your code elswhere.

    Dim SheetNameToSave as a public variable in a standard module.

    There are sexier ways of using a counter, but this will do for this macro. Adjust the counter's step to more or less to adjust the delay between prompts.

    Sub PickSheet()
    Dim Cntr
    SheetNameToSave = ActiveSheet.Name
    ReTry:
    MsgBox "Please click 'Ok' and select a sheet"
    Cntr = 0
    Do Until SheetNameToSave <> ActiveSheet.Name
    Cntr = Cntr + 0.005
    DoEvents
    If Cntr > 100 Then GoTo ReTry:
    Loop
    SheetNameToSave = ActiveSheet.Name

    End Sub

    Have a Nice Day!
    Tom

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Louisville, Ohio
    Posts
    248
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TsTom,

    Your idea worked and also gave me other ideas how I could accomplish my needs.

    Thanks for your time and help.

    David

  4. #4
    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

      
    Knowing full well that the problem is solved, I thought that I would add an inputbox, point & click method.

    Code:
    Sub ShtName()
    Dim z As String, n As Range
    On Error GoTo errorhandler
    Set n = Application.InputBox(prompt:="Click on a range on target worksheet", Type:=8)
    z = n.Worksheet.Name
    'Worksheets(z).Select 'to select the sheet that was clicked on
    Exit Sub
    errorhandler: MsgBox ("Unable to determine your 'selected' range. Please try again")
    End Sub
    Have a nice weekend.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-05 11:06 ]

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