select instruction is not able to work with a sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top