vba to get worksheet name by clicking on the tab name

[sparky2205]

Hi folks,
I'm trying to allow the users select a worksheet name by using the mouse to click on the worksheet tab.



If I use...

Dim sName as String
sName = Application.InputBox("Enter the Sheet Name", Type:=2)

...I get "Excel found a problem with one or more formula references in this worksheet" because the worksheet name in the Inputbox is "='Final Inspection Summary'!". I presume the "=" is causing the problem.


I can get the name using...

Dim sheet as Worksheet
Set sheet = Application.InputBox("Enter the Sheet Name", Type:=8).Parent

But this requires that the user click on the worksheet tab and then click on a cell within the worksheet.

Is there any way to get the worksheet name just by clicking on the tab i.e. name?

It's not that the extra click would be so onerous on the user. But I would anticipate a lot of "This doesn't work" comments by users just clicking on the tab.

 

[DataBlake]

Hi folks,
I'm trying to allow the users select a worksheet name by using the mouse to click on the worksheet tab.



If I use...

Dim sName as String
sName = Application.InputBox("Enter the Sheet Name", Type:=2)

...I get "Excel found a problem with one or more formula references in this worksheet" because the worksheet name in the Inputbox is "='Final Inspection Summary'!". I presume the "=" is causing the problem.


I can get the name using...

Dim sheet as Worksheet
Set sheet = Application.InputBox("Enter the Sheet Name", Type:=8).Parent

But this requires that the user click on the worksheet tab and then click on a cell within the worksheet.

Is there any way to get the worksheet name just by clicking on the tab i.e. name?

It's not that the extra click would be so onerous on the user. But I would anticipate a lot of "This doesn't work" comments by users just clicking on the tab.

The reason you are getting that error is because you're not specifying a range like $A$1 on the sheet.

is the user going to change sheets anytime when this macro is not active?
you could have it where you run your first sub with this at the end

MsgBox "Please Select Your Sheet"

and then in your workbook or sheet module(s) you have
where it will run a macro that will grab the current sheet's name as a variable and then run the second part of your code (which i've named subNAME)

Private Sub Worksheet_Activate()
Dim sName as String
sName = ActiveSheet.Name

call subNAME
     End Sub

so essentially you run your first macro. select the sheet, and this will automatically start the second part of the macro after grabbing the current sheet name.
depending on your use of sName you may want to declare it as public

 

[sparky2205]

Thanks for this. I like it. Very clever. I think I understand you correctly. But I am having some issues i.e. the worksheet_activate event doesn't appear to be triggered. It's probably something in my setup so I'll post my full code.


In a module:

Sub CopySheet()

MsgBox "Select the worksheet to copy"
End Sub

In the same module:

Dim sInterimName As String
Dim iCountName As Integer
Dim ws As Worksheet
    ScreenUpdating = False
    iCountName = 0
 If sName = "" Then
        Exit Sub
    End If
    
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(sName) Then
            iCountName = iCountName + 1
        End If
    Next ws
    
    If iCountName = 0 Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If
    
    ThisWorkbook.Unprotect
    Sheets(sName).Copy after:=Sheets(sName)
    sInterimName = sName & " (2)"
    
    ThisWorkbook.Protect Password:="", structure:=True
                   
    Worksheets(sInterimName).Protect Password:="", _
        userinterfaceonly:=True, _
        AllowFiltering:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingRows:=True, _
        AllowFormattingColumns:=True, _
        AllowInsertingRows:=True
        
        ScreenUpdating = True
        
End Sub

I've tried the following in the same module and in ThisWorkbook:

Private Sub Worksheet_Activate()

   Dim sName As String
   sName = ActiveSheet.Name
    Call SubName
End Sub

When I run CopySheet() from a screen button I don't get errors but nothing happens. My sheet isn't copied.

 

[DataBlake]

Thanks for this. I like it. Very clever. I think I understand you correctly. But I am having some issues i.e. the worksheet_activate event doesn't appear to be triggered. It's probably something in my setup so I'll post my full code.


In a module:

Sub CopySheet()

MsgBox "Select the worksheet to copy"
End Sub

In the same module:

Dim sInterimName As String
Dim iCountName As Integer
Dim ws As Worksheet
    ScreenUpdating = False
    iCountName = 0
 If sName = "" Then
        Exit Sub
    End If
    
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(sName) Then
            iCountName = iCountName + 1
        End If
    Next ws
    
    If iCountName = 0 Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If
    
    ThisWorkbook.Unprotect
    Sheets(sName).Copy after:=Sheets(sName)
    sInterimName = sName & " (2)"
    
    ThisWorkbook.Protect Password:="", structure:=True
                   
    Worksheets(sInterimName).Protect Password:="", _
        userinterfaceonly:=True, _
        AllowFiltering:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingRows:=True, _
        AllowFormattingColumns:=True, _
        AllowInsertingRows:=True
        
        ScreenUpdating = True
        
End Sub

I've tried the following in the same module and in ThisWorkbook:

Private Sub Worksheet_Activate()

   Dim sName As String
   sName = ActiveSheet.Name
    Call SubName
End Sub

When I run CopySheet() from a screen button I don't get errors but nothing happens. My sheet isn't copied.

sorry for the late reply.
so a few things to note:

Private Sub Worksheet_Activate()

   Dim sName As String
   sName = ActiveSheet.Name
    Call SubName
End Sub

this sub has to be put in a specific workbook's module which is above normal modules in the VBA developer screen
also:
Call SubName needs to change to the name of the 2nd half of your sub
I'm checking out your code and will give you a more specific answer in my next post

 

[DataBlake]

this sub has to be put in a specific workbook's module which is above normal modules in the VBA developer screen

and what i mean by that is put it in every sheet under the workbook.
just note that you will not be able to switch sheets without running the 2nd part of the macro
which is why i asked if they will be changing sheets at all outside of the macro.

So step one is declaring a global/public variable that is sName
so in module1 (where your non-sheet based macros)

Public Dim sName As String

Then the next step is running your 2nd part of your macro whenever you switch sheets

Private Sub Worksheet_Activate()


   sName = ActiveSheet.Name
    Call SubName
End Sub

in every sheet that the user has the ability to select during the macro
change SubName to whatever you want to name the second part of your macro

the next step is splitting your main macro in two
the first half is before you need sName defined

sub Macro1
THIS IS YOUR SUB THAT DOES NOT NEED SNAME AND WILL END UP ASKING FOR SNAME
MsgBox "Select the worksheet to copy"
End Sub

the second half where you need sName after user clicks on a different sheet

Sub SubName()

Dim sInterimName As String
Dim iCountName As Integer
Dim ws As Worksheet
    ScreenUpdating = False
    iCountName = 0
 If sName = "" Then
        Exit Sub
    End If
    
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(sName) Then
            iCountName = iCountName + 1
        End If
    Next ws
    
    If iCountName = 0 Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If
    
    ThisWorkbook.Unprotect
    Sheets(sName).Copy after:=Sheets(sName)
    sInterimName = sName & " (2)"
    
    ThisWorkbook.Protect Password:="", structure:=True
                   
    Worksheets(sInterimName).Protect Password:="", _
        userinterfaceonly:=True, _
        AllowFiltering:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingRows:=True, _
        AllowFormattingColumns:=True, _
        AllowInsertingRows:=True
        
        ScreenUpdating = True
        
End Sub

the only issue i see is maybe the global variable can't be set within a private sub, but try that

 

[DataBlake]

the only issue i see is maybe the global variable can't be set within a private sub, but try that

yeah that ended up being the case when i went to test it
So i found another way about this just let me write it up real quick. ignore everything i've typed this far

 

[DataBlake]

Okay so this should work to reduce your users from trying to run the macro, selecting the sheet, and then complaining that it didnt work, although it has the same amount of results as writing into the input box "& select cell A1" because its not as clean as you may have wanted it

You're going to want to create a userform named "fstop"
On the userform put a label with the caption "Select a worksheet and press continue"
Also add a command button with the caption "continue"

after you've done this copy this into the userform code

Private Sub CommandButton1_Click()
Unload fstop
End Sub

then copy the following code into a module

Sub YourCode()

    Dim TxtRng  As Range
    Dim sName As String
    Dim ws As Worksheet
    sName = ActiveSheet.Name
    
    Set ws = ActiveSheet

FIRST PART OF YOUR MACRO
 
Call Pauser

    sName = ActiveSheet.Name
    ws.Select
    
    SECOND PART OF YOUR MACRO
 
End Sub

Sub Pauser()
Application.ScreenUpdating = True
fstop.Show
Do Until fstop.Visible = False
DoEvents
Loop
Application.ScreenUpdating = False
End Sub

so again replace the parts specified with your code
"Call Pauser" is where it will ask the user to switch worksheets, grabs the name of the current worksheet, saves that as sName, and then returns to your original worksheet to execute the rest of your macro.

 

[sparky2205]

Hi BlakeSkate,
thanks very much for your work on this. You really have gone the full nine yards.
I will test this approach but it may take me some time.
I've had to move on with what I'm doing. So for now I'm having the users type in the sheet names rather than selecting them.
I don't have any experience with userforms so I'm looking forward to seeing exactly how your approach works.
I like to learn new things. I was also thinking about trying out userforms for another little project where I'm hoping they will be what I'm looking for. So this may well turn out to be a very fortuitous post.
I will let you know how I get on with my testing.
And again, thanks very much for your input. Much appreciated.