• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Tab Creation by List

EE Professionals,

I have a simple list that when a paticular selection is made (by industry), I want Excel to "go to" the appropriate "Tab".  There should also be a "Return" button in each spreadsheet that returns you to the Selection Tab.

Hopefully this is not complex.

Thank you in advance,

B.
Simple-Tab-Selection.xlsm
0
Bright01
Asked:
Bright01
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try this code.

Right-click on the selection tab and select "View code"
paste the code in this code window.

This will let you select a sheet from cell K5. You can change this by changing the two instances of K5 in the code below.

You can do the same to all the sheets. This way you will have the ability to go to any other sheet on the workbook.

If it must be a button on all the other sheets you can add a button and double click it in design mode and enter this line of code
Sheets("Selection").Activate

Saqib
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("K5").Address Then
On Error Resume Next
Sheets(Target.Value).Activate
On Error GoTo 0
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("K5").Address Then
wslist = ""
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then wslist = wslist & "," & ws.Name
Next ws
wslist = Right(wslist, Len(wslist) - 1)
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=wslist
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
End Sub

Open in new window

0
 
Bright01Author Commented:
ssaqibh,

PERFECT!  Works like a charm.

B.
0
 
Bright01Author Commented:
Quick, accurate, and works!

Much thanks,

B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now