CODEWITHSUNDEEP
excelvbacell
BobG999999
BobG999999

Reputation: 1

How do I use the contents of a selected cell in an Excel Macro?

I want a user to be able to select a cell with a sheet name in it, and then have a single macro button go to that worksheet. How do I get VBA to use the contents of the selected cell?

I have one setup to go back to the "Summary" worksheet, but want to let users go quickly to a selected sheet without having to create a different macro for each sheet as the user will be adding sheets to the workbook over time.

Sub Return_to_Summary()

' Return_to_Summary Macro


         Sheets("Summary").Select

End Sub

Upvotes: 0

Views: 125

Answers (1)

Dave Lett
Dave Lett

Reputation: 88

You might also want to add in some validation or error checking.

Public Sub Return_to_Summary()
Dim sName As String
Dim oSht As Worksheet
Dim bFound As Boolean
bFound = False

sName = ActiveCell.Text
For Each oSht In ActiveWorkbook.Sheets
    If oSht.name = sName Then
        bFound = True
        Exit For
    End If
Next

If bFound = True Then
    Sheets(ActiveCell.Text).Activate
Else
    MsgBox "This worksheet (" & sName & ") cannot be found. Check the spelling and capitalization."
End If
End Sub

Upvotes: -1

Related Questions