what type of sheet

[lior03]

hello
it all began when i wanted to create udf that would tell me wheter a worksheet exists.
[VBA]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
[/VBA]
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
[VBA]
Function prototype(shtname) As String
prototype = TypeName(shtname)
End Function
[/VBA]
can any one tell me what wrong with my functions?.

[rory]

I would guess you want:
[VBA]Function prototype(shtname) As String
prototype = TypeName(Sheets(shtname))
End Function [/VBA]

[Norie]

With the original code you'll only be looking at worksheets.

Charts aren't included in the Worksheets collection.

[p45cal]

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:
[vba]For Each ws In ActiveWorkbook.Worksheets[/vba] to
[vba]For Each ws In ActiveWorkbook.Sheets[/vba] it might do what you want?

[mdmackillop]

You can avoid looping
[VBA]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

[/VBA]

[mikerickson]

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]

what is wrong with
[VBA]
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

[/VBA]

[rory]

InputBox is returning a string, not an object. You should declare sheetname as a string and use Sheets(sheetname).Activate, not sheetname.Activate

[lior03]

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

As has been mentioned before, this only looks at worksheets:
[VBA] For Each ws In ActiveWorkbook.Worksheets
ListBox1.AddItem (ws.name)
Next ws[/VBA]
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]

You

[rory]

You can use

[rory]

You can use something like:
[VBA] 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
[/VBA]

[lior03]

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?
[VBA]
Private Sub UserForm_Activate()
ListBox1.Value = ""
UserForm10.Caption = "navigator" & " - " & ActiveWorkbook.FullName
ListBox1.Value = ActiveSheet.name
End Sub

[/VBA]
thanks

[rory]

You can't use the Value property if your listbox is Multiselect; that's why I had this line in my code:
[VBA]If Sheets(n) Is ActiveSheet Then .Selected(n - 1) = True [/VBA]

[Norie]

Why do you need/want to activate multiple sheets?

What are youy actually trying to do?