select instruction is not able to work with a sheet

[Guest]

Hi

I'm using second time this site, I'm not sure how it's working, from first
post I still don't get any comment.

This issue is when a excel file is opened, we have an instruction like
sheet("Setup").select inside workbook_open event, and in some excels this
works fine, when it does'nt work a message "error 57121 defined by the
object" comes.

Reading a Microsoft information, I changed the instruction creating first an
object for the especific sheet and then using the same select instruction, it
works fine but still I don't know this is the right way.

Thanks in advance for your comments

 

[Guest]

Hi

Not sure what it is that you are trying to do, and having to assume that
when you open the workbook you want a certain sheet to be the active sheet?

within the Workbook_Open event
you could use any of the following

Sheets("Setup").Activate
ActiveWorkbook.Worksheets("Setup").Activate

Try not to use select, and to be honest in most cases there isnt a need to
use activate either, if you have set reference to an object you are able to
work with it without Selecting/Activating

HTH

 

[Guest]

Try something like this

Sub Test()
Dim myWS As Worksheet
Dim mySheet As String
Set myWS = ActiveSheet

mySheet = "Sheet4" '<~~~change as needed
Set myWS = Nothing

On Error Resume Next
Set myWS = Sheets(mySheet)
On Error GoTo 0

If Not myWS Is Nothing Then
myWS.Select
Else
MsgBox ("Worksheet " & mySheet & " does not exist in this workbook")
Sheets(1).Select 'Selects the first sheet in the workbook
End If

End Sub

HTH,
Barb Reinhardt