[VBA] "Pick" a Worksheet Tab and save it to a variable

[PeteWright]

Hi all,
I've been deeply searching for this, but couldn't find an answer anywhere.
There is like a gazillion of topics, but none of the posted codes work for me.

So what I need is a user input (like prompt or something - or just a Mouse Pointer) to "pick" a Worksheet Tab from the bottom tabs and save it's reference to a variable.

The only thing that comes close which I found is the Application.InputBox "object" which unfortunately doesn't seem to handle all possible user inputs but a few (e.g. String, Boolean, Range) and can't cope with a selected tab.

This is one of my many code snippets which only gives me the error "Excel found a problem with one or more Formula References in this worksheet."

Sub Zero()
   Debug.Print Application.InputBox("Select a Worksheet Tab", "Select Tab", , , , , , 8)
End Sub

Any ideas on this?

 

[Domenic]

How about something like this...

Sub Zero()

    Dim userRange As Range
    Dim userSheet As Worksheet
    
    On Error Resume Next
    Set userRange = Application.InputBox(Prompt:="Click on a cell to select a worksheet", Title:="Select Worksheet", Type:=8)
    If userRange Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
    
    Set userSheet = userRange.Parent
    
    MsgBox "User selected worksheet:  " & userSheet.Name
    
    
End Sub

Hope this helps!

 

[PeteWright]

How about something like this...

Sub Zero()

    Dim userRange As Range
    Dim userSheet As Worksheet
   
    On Error Resume Next
    Set userRange = Application.InputBox(Prompt:="Click on a cell to select a worksheet", Title:="Select Worksheet", Type:=8)
    If userRange Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
   
    Set userSheet = userRange.Parent
   
    MsgBox "User selected worksheet:  " & userSheet.Name
   
   
End Sub

Hope this helps!

@Domenic that works!
I have already seen and tried this method, but thought of selecting a tab rather then a cell.

It's only a personal thing, but I'd like to have the user select a tab (only click on a tab) not a cell which requires two steps (click on tab, click into cell).
Is this possible too?

 

[Domenic]

Maybe one possibility might be to use the SheetActivate event handler. When clicking on a tab, the sheet would be activated, and the SheetActivate event handler would be triggered. Then it could assign the sheet to a variable. The code would be placed in the code module for ThisWorkbook...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox Sh.Name
End Sub

Note, however, clicking on a tab whose sheet is already activated would not trigger the event. But adding a new worksheet to your workbook would trigger it.

 

[Domenic]

Actually, have you considered using a UserForm instead? The UserForm would contain a ListBox where the sheets would be listed, and the user would be prompted to select a sheet.

 

[PeteWright]

Hi @Domenic
Unfortunately your code throws an error when I press the [Cancel] Button or [Esc] Button:
Run-time error '424': Object required

That's my biggest problem now. I don't want that error to be shown if I cancel or hit Escape.

Any ideas how to fix that?

EDIT:
Actually every code snippet I found somewhere on the web fails because Excel can't handle the error.
Phew, will be hard to find something that works

 

[Domenic]

Which code are you referring to?

 

[PeteWright]

Which code are you referring to?

I'm referring to a slightly modified version of your code:

Sub Zero()
    Dim userRange As Range
    On Error Resume Next
    Set userRange = Application.InputBox(Prompt:="Click on a cell to select a range", Title:="Select Cell", Type:=8)
    If userRange Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
    MsgBox "User selected Range:  " & userRange
End Sub

But any other code fails, because upon cancellation the error is thrown before it can be handled.

 

[Domenic]

Oh I see, okay. It looks like you have your error trapping set to break on all errors. Instead, set it so that it breaks only on unhandled errors...

Visual Basic Editor >> Tools >> Options >> General >> Error Trapping >> click/select Break on Unhandled Errors >> OK

Now, when you press Cancel or Esc, it should simply exit the sub.

Hope this helps!

 

[PeteWright]

Yay! That does it! Many thanks!

One more question: If I set the error trapping to unhandled errors only, is this method "safe"?
Let's say I write some VBA code, set up the "Graphics" on my Workbook/Worksheet(s) and give the file to someone else who isn't familiar with VBA or even how to show the developer tab or open the VBA editor and therefore probably has error trapping set to brake on all errors...

This person will maybe get frustrated when errors show up.

So. How can I make my code fool-proof (if even possible in this specific case)?