PDA

View Full Version : what type of sheet



lior03
09-03-2007, 08:54 AM
hello
it all began when i wanted to create udf that would tell me wheter a worksheet exists.
Function sheetme(shtname) As Boolean
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.name = shtname Then
sheetme = True
End If
Next
End Function

then i found that my founction could not deal with charts.so i added another udf to tell me wheter i look at a worksheet or a chart sheet

Function prototype(shtname) As String
prototype = TypeName(shtname)
End Function

can any one tell me what wrong with my functions?.

rory
09-03-2007, 08:55 AM
I would guess you want:
Function prototype(shtname) As String
prototype = TypeName(Sheets(shtname))
End Function

Norie
09-03-2007, 10:36 AM
With the original code you'll only be looking at worksheets.

Charts aren't included in the Worksheets collection.

p45cal
09-03-2007, 02:21 PM
With the original code you'll only be looking at worksheets.

Charts aren't included in the Worksheets collection. ...but are included in the Sheets collection. So if you change your line:
For Each ws In ActiveWorkbook.Worksheets to
For Each ws In ActiveWorkbook.Sheets it might do what you want?

mdmackillop
09-03-2007, 02:36 PM
You can avoid looping
Sub test()
MsgBox SheetExists("Chart1")
End Sub

Function SheetExists(sh) As Boolean
Dim i
On Error Resume Next
Set i = Sheets(sh)
If Not IsEmpty(i) Then SheetExists = True
End Function

mikerickson
09-03-2007, 02:40 PM
This function should do what you want.

Function sheetExists(sheetName As String) As Boolean
On Error Resume Next
sheetExists = (ThisWorkbook.Sheets(sheetName).Name = sheetName)
On Error GoTo 0
End Function

lior03
09-04-2007, 06:13 AM
what is wrong with

Sub sheetlocator()
On Error GoTo err
Dim sheetname As Object
sheetname = InputBox("enter vsheet name")
If sheetme(sheetname) = True Then
sheetname.Activate
End If
Exit Sub
err:
MsgBox " sheet do not exists", vbOKOnly + vbInformation, "wrong"
End Sub

rory
09-04-2007, 06:53 AM
InputBox is returning a string, not an object. You should declare sheetname as a string and use Sheets(sheetname).Activate, not sheetname.Activate

lior03
09-05-2007, 03:32 AM
Private Sub CommandButton1_Click()
Dim i As Integer, sht As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.selected(i) = True Then
sht = ListBox1.List(i)
End If
Next i
Sheets(sht).Activate
End
End Sub
Private Sub CommandButton2_Click()
UserForm10.hide
End Sub
Private Sub UserForm_Activate()
ListBox1.Value = ""
UserForm10.Caption = "navigator" & " - " & ActiveWorkbook.FullName
ListBox1.Value = ActiveSheet.name
End Sub
Private Sub UserForm_Deactivate()
UserForm10.Caption = ""
End Sub
Private Sub UserForm_Initialize()
ListBox1.Value = ""
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem (ws.name)
Next ws
End Sub
Private Sub UserForm_QueryClose(cancel As Integer, CloseMode As Integer)
cancel = True
UserForm10.Caption = "The Close box won't work! Click cancel!"
End Sub

the code above is a userform for a list of all sheets in a workbook.how can i get the userform open on the activesheet being highlight in the lostbox?
where do all chart sheet diaappeared?
thanks

rory
09-05-2007, 03:39 AM
As has been mentioned before, this only looks at worksheets:
For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem (ws.name)
Next ws
you need to declare ws as Object and look at the Sheets collection.

Is your listbox a multiselect one? (I am guessing it is from your code)

rory
09-05-2007, 03:42 AM
You

rory
09-05-2007, 03:42 AM
You can use

rory
09-05-2007, 03:44 AM
You can use something like:
Dim n As Integer
For n = 1 To Sheets.Count
With ListBox1
.AddItem Sheets(n).Name, n - 1
If Sheets(n) Is ActiveSheet Then .Selected(n - 1) = True
End With
Next n

lior03
09-05-2007, 03:52 AM
hello rory
i change my ws to object.i keep getting error 380.ithappen everytime i open another wb and trying to get ws list .may be my activate event is wrong?

Private Sub UserForm_Activate()
ListBox1.Value = ""
UserForm10.Caption = "navigator" & " - " & ActiveWorkbook.FullName
ListBox1.Value = ActiveSheet.name
End Sub


thanks

rory
09-05-2007, 04:06 AM
You can't use the Value property if your listbox is Multiselect; that's why I had this line in my code:
If Sheets(n) Is ActiveSheet Then .Selected(n - 1) = True

Norie
09-05-2007, 06:08 AM
Why do you need/want to activate multiple sheets?

What are youy actually trying to do?