Switch to tab if already created

[Djblois]

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

[VBA]If SheetExists(myInput) Then
PivotTableOptions.Hide
DoEvents
Error008.Show
GoTo CreateTab
End If[/VBA]

[VBA]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[/VBA]

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]

[vba]

Worksheets(myInput).Activate
[/vba]

[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.


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

[VBA]Public myInput As String[/VBA]

[Djblois]

This is the code and it isn't working

[VBA]

Public myInput As String

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

[/VBA]

then the code behind the button:

[VBA]Private Sub CommandButton4_Click()
Worksheets(myInput).Activate
DoEvents
End Sub[/VBA]

[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:

[VBA]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[/VBA]

that is the first full sub