Use Worksheet name variable by clicking on worksheet tab

[fddekker]

I have a macro that compares two sheets (in a workbook that might contain 10 sheets). Currently, the user enters the names of the two sheets in two input boxes. Is there a way that the user need only to click on a sheet tab and the sheet names are used in the macro? (this would eliminate any misspellings)</SPAN>

Thanks in advance.</SPAN>

Francois</SPAN>

 

[Norie]

Why not create a small userform with a listbox which lists all the worksheet names and a command button?

The user selects the 2 worksheets to compare from the listbox and clicks the button to run the macro.

The macro would need to be adjusted to get the sheet names from the listbox or accept them as arguments.

 

[dmt32]

If you just want to select two tabs & pass names to a variable then something like this may work for you.

Place this code in the ThisWorkBook code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Len(sharr(0)) = 0 Then
        sharr(0) = Sh.Name
    Else
        sharr(1) = Sh.Name
    End If
End Sub

And this code will need to be incorporated in your existing code in a standard module. Note the Public variable sits at top of code page outside the procedure.

Public sharr(2) As Variant
Sub CompareSheets()
    Dim Dspace As Variant
    
    If Len(sharr(1)) = 0 Then Exit Sub
    
    Dspace = Chr(10) & Chr(10)
    msg = MsgBox("Compare The Following Sheets:" & Dspace & _
                 "Sheet: " & sharr(0) & Dspace & _
                 "     With" & Dspace & _
                 "Sheet: " & sharr(1), 36, "Compare")
    If msg = 6 Then
    
        'rest of your code
        'to do stuff
    End If
    Erase sharr
End Sub

Not properly tested but in therory you just select the two tabs you want & then use the array values in your procedure.
Dave