Worksheets Select
 

I am trying to select a worksheet by an object name:

Sheet 1 user selection of worksheets = Vnumber

Sub SheetExists()
Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
Set Vnumber = Range("F7")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
End If
Next Sh

MsgBox Vnumber
Sheets = Vnumber.Select (*this does not work)*
Range("A1").Select

I have also tried
Sheets(Vnumber).Select
Sheets.Name = Vnumber.Select

Thanks

Ashle

--
Ashley Fran
-----------------------------------------------------------------------
Ashley Frank's Profile: http://www.excelforum.com/member.php...fo&userid=2648
View this thread: http://www.excelforum.com/showthread.php?threadid=39754

Worksheets Select
 

try to chance: If Sh.Name = Vumber Then to If Sh.Name = Vnumber


"Ashley Frank" skrev:


I am trying to select a worksheet by an object name:

Sheet 1 user selection of worksheets = Vnumber

Sub SheetExists()
Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
Set Vnumber = Range("F7")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
End If
Next Sh

MsgBox Vnumber
Sheets = Vnumber.Select (*this does not work)*
Range("A1").Select

I have also tried
Sheets(Vnumber).Select
Sheets.Name = Vnumber.Select

Thanks

Ashley


--
Ashley Frank
------------------------------------------------------------------------
Ashley Frank's Profile: http://www.excelforum.com/member.php...o&userid=26488
View this thread: http://www.excelforum.com/showthread...hreadid=397549

Worksheets Select
 

Hi Ashley,

See my in line comments:

Sub SheetExists()
Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object

Since Vnumber is a cell value, it should not be declared as Object; I would
change this to variant.

Set Vnumber = Range("F7")

Set is used to assign an object to an object variable. Hee you are
assigning a cell value to the Vnumber variable. Drop the Set.

Exist = False
For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
End If
Next Sh

Your For ...Next loop assigns a value to the Exist boolean variable but does
nothing with it. Also, since a sheet can only be located once in your loop,
when it is found, exit the loop with a Exit For statement to avoid
unnecessary loops.

MsgBox Vnumber
Sheets = Vnumber.Select (*this does not work)*

Sheets represents a collection which comprises all sheet types. VBA,
understandaby, splutters here. You provide a more appropriate syntax
later...

Range("A1").Select

This line is fine but is unnecessary and has no relevance to the intentions
underying your procedure. It can safely be deleted.

I have also tried
Sheets(Vnumber).Select

Subject to the above, this should now work as intended.

Sheets.Name = Vnumber.Select

The Sheets collection does not have a Name property. In any event, an
equality expression cannot represent the object required by the Select
statement .

Perhaps the following is near to what you wanted:

'========================
Sub SheetExists()
Dim Sh As Worksheet
Dim Exist As Boolean
Dim Vnumber As Variant

Vnumber = Range("A1").Value
Exist = False

For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
MsgBox Vnumber
Sh.Select
Exit For
End If
Next Sh

If Not Exist Then
'sheet not found
'do something, e.g.:
MsgBox "Sheet not found, Check A1 value"
End If
End Sub

'<<=====================


Since there is a frequent need to check for the existance of a given sheet,
you might find it convenient to add a function for this purpose. For
example:

Function SheetExists(sName As String) As Boolean
On Error Resume Next
SheetExists = CBool(Len(Sheets(sName).Name))
End Function

If stored, perhaps in your Personal.xls macro workbook, a function like this
could be called on demand from a procedure, e.g:

Sub TestIt()
MsgBox SheetExists(ActiveSheet.Range("A1").Value)
End Sub


---
Regards,
Norman



"Ashley Frank"
wrote in message
news:Ashley.Frank.1u3wyc_1124615129.4971@excelforu m-nospam.com...

I am trying to select a worksheet by an object name:

Sheet 1 user selection of worksheets = Vnumber

Sub SheetExists()
Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
Set Vnumber = Range("F7")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
End If
Next Sh

MsgBox Vnumber
Sheets = Vnumber.Select (*this does not work)*
Range("A1").Select

I have also tried
Sheets(Vnumber).Select
Sheets.Name = Vnumber.Select

Thanks

Ashley


--
Ashley Frank
------------------------------------------------------------------------
Ashley Frank's Profile:
http://www.excelforum.com/member.php...o&userid=26488
View this thread: http://www.excelforum.com/showthread...hreadid=397549

Worksheets Select
 

Thanks.

I eventually got it as

Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object, Sheet_name As
String

Sheets("Start").Select
Set Vnumber = Range("J11")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vnumber Then
Exist = True
MsgBox Vnumber

End If
Next Sh

If Exist = True Then
Sheet_name = Sheets("Start").[J11].Value
Sheets(Sheet_name).Select
Range("A1").Select
Exit Sub
Else
Set NewSheet = Worksheets.Add
NewSheet.Name = Vnumber
End If

End Sub

Just about the same as what you were suggesting. Clearly it is "value"
that is tthe key.


--
Ashley Frank
------------------------------------------------------------------------
Ashley Frank's Profile: http://www.excelforum.com/member.php...o&userid=26488
View this thread: http://www.excelforum.com/showthread...hreadid=397549