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