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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.