Switch to tab if already created

[Djblois]

I am using this code to test if a tab with the same name was created:


If SheetExists(myInput) Then
PivotTableOptions.Hide
DoEvents
Error008.Show
GoTo CreateTab
End If

Function SheetExists(Sh As String, Optional WB As Workbook) As Boolean
Dim oWs As Worksheet
If WB Is Nothing Then Set WB = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not WB.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

It works great, only now I want to give the user the option of switching to that tab with that name to see if it is what they need. Is this possible?

[Bob Phillips]

Worksheets(myInput).Activate

[Djblois]

thank you xld sometimes it is so easy. lol I wish I was an expert at VBA

[Djblois]

xld,

I can't get it to work. I put that code behind a button on the error008 form that I have created. I don't always want it to switch only if the user wants to switch to that tab.

[Bob Phillips]

So add a MsgBox and test the result.

[Djblois]

xld,

What do you mean? MyInput works when testing to see if that name was already added.

[Bob Phillips]

What I mean is that if you want the user to decide whether they want to switch to that worksheet, you have to ask them.

[Djblois]

Oh XLD, I created a button on my error008 form that when they hit it, it is supposed to switch to the form.

[Bob Phillips]

Then you will just have to make myInput a global variable, and in the code behind that button, activate the sheet.

[Djblois]

How do I make it a global variable, I know how to make it public but not global.

[Bob Phillips]

Same thing mate, just terminology.

[Aussiebear]

Same thing mate, just terminology.

You know Bob, you have the makings of a very good Aussie. :devil2:


We would use the same terminology "Same thing mate". Are you just practising in case you end up here one day?

[Bob Phillips]

You never know my luck!

[Djblois]

XLD,

I already have it declared publically


Public myInput As String

[Djblois]

This is the code and it isn't working




Public myInput As String

myInput = PivotTableOptions.ReportName.Value
If SheetExists(myInput) Then
PivotTableOptions.Hide
DoEvents
Error00_00_02.Show
End If


then the code behind the button:


Private Sub CommandButton4_Click()
Worksheets(myInput).Activate
DoEvents
End Sub

[Bob Phillips]

That cannot be all the cose as the first snippet doesn't show the Sub signature etc.

Why don't you just post the wrkbook, we are working somewhat in the dark.

[Djblois]

I am trying but it is part of my add-in that would be way too many lines of code the first sub is:


Sub StartPivot()
Dim nameTest As Long
Set sb = New clsProgressBar
sb.Show "Please wait", "Running...", 0
CreateTab:
PivotTableOptions.Show
'Create sheet
Err.Clear
myInput = PivotTableOptions.ReportName.Value
'Test if user selected at least one data value
If i = 0 Then
MsgBox "You need to select at least one value to view!"
GoTo CreateTab
End If
'Test if user left name blank
If (myInput) = "" Then
MsgBox "You Need to give the Report a name!"
GoTo CreateTab
End If
'Test if another sheet with the same name exists
If SheetExists(myInput) Then
PivotTableOptions.Hide
DoEvents
Error00_00_02.Show
GoTo CreateTab
End If
TurnOffFeatures
Set pvt = Worksheets.Add(, Detail, 1)
pvt.Name = myInput
FirstProgress
finalRow = Detail.Cells(Rows.Count, "B").End(xlUp).Offset(-1, 0).Row
finalColumn = Detail.Cells(1, 256).End(xlToLeft).Column
Detail.Activate
Set pRange = Detail.Cells(1, 1).Resize(finalRow, finalColumn)
Set ptCache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set PT = ptCache.CreatePivotTable(TableDestination:=pvt.Range("A1"), TableName:="Test")
PT.RowGrand = False
PT.PrintTitles = True
PT.NullString = "0"
PT.DisplayErrorString = True
End Sub

that is the first full sub