PDA

View Full Version : Switch to tab if already created



Djblois
03-06-2007, 11:15 AM
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
03-06-2007, 11:35 AM
Worksheets(myInput).Activate

Djblois
03-06-2007, 11:48 AM
thank you xld sometimes it is so easy. lol I wish I was an expert at VBA

Djblois
03-06-2007, 12:14 PM
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
03-06-2007, 12:31 PM
So add a MsgBox and test the result.

Djblois
03-06-2007, 12:33 PM
xld,

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

Bob Phillips
03-06-2007, 01:01 PM
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
03-06-2007, 01:06 PM
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
03-06-2007, 02:03 PM
Then you will just have to make myInput a global variable, and in the code behind that button, activate the sheet.

Djblois
03-06-2007, 02:47 PM
How do I make it a global variable, I know how to make it public but not global.

Bob Phillips
03-06-2007, 03:04 PM
Same thing mate, just terminology.

Aussiebear
03-07-2007, 02:43 AM
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
03-07-2007, 07:47 AM
You never know my luck!

Djblois
03-07-2007, 09:37 AM
XLD,

I already have it declared publically

Public myInput As String

Djblois
03-13-2007, 07:59 AM
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
03-13-2007, 08:08 AM
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
03-13-2007, 08:21 AM
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