Run-time error '1004' Select method of Worksheet class failed

Thank you for that. I think I know a bit more now as to why I'm getting this error. The workbook is password protected and read-only set on it. After renaming the file in the folder, then when you open it for the first time after that. You have to enable editing, then enter the password. After that a second workbook open with the same name. book 01 is [Protected View] and book 02 normal. That's when I get the error. Could it be that it's looking at both books due to them having the same name?

I think I fixed it by copying something I found online but I don't understand it really how or why it's now fixed but it works.
I am new to VBA and code, thank you for your time I appreciate all the help and feedback.

'Fix below 
Private Sub Workbook_SheetActivate(ByVal Wsh As Object) 
On Error Resume Next 
Wsh.Range("F6").Select 
End Sub 

Select allOpen in new window

You cannot open two workbooks with the same name

Hi, Roy.
I know you can't and I don't but Excel does not close the protected workbook untell the normal one is open.
So by the <On Error Resume Next> I'm just skipping this sub and goinh to the next one this is ok for me as its not that critical.

Private Sub Workbook_Open()
    'New Fix Below
    On Error Resume Next
    Sheets("QUOTE SETUP").Visible = True
    Sheets("QUOTE SETUP").Select
    Range("F6").Select
End Sub

Select allOpen in new window

It looks like you have two WorkBook Open events in one workbook. You cannot have the same event in one workbook, the open event should be in the workbook that it refers to.

Thanks, as I'm new to VBA and coding, how do I fix this and not skip it?
It was just an observation that maybe if the code is not defined to the workbook that's opening it doesn't know what book it applies too, due to they have the same name hence the error.

In code you can refer to the workbook that contains the code as ThisWorkBook. ActiveWorkBook is the workbook that is currently active.

You can of course declare a variable for any workbook

Dim oWbk as WorkBook

Set oWbk=Workbooks.Open "ABC.xls"

Select allOpen in new window

The above code sets a variable to the workbook that it opens. You then refer to it like this

oWbk.Sheets(1)

Select allOpen in new window

If you explain what you are doing then it might be easier to provide a more complete answer.

Hi, Roy.

Thanks for the help, I will try and explain what I'm trying to do.
Thank you for your time.

I am working on a quoting sheet to make my life a little bit easier.
The sheet is like a template but not saved as one it is a "*.xlsm."
It is password protected and set up as read-only.

How it works is, you would copy the file to the folder you are setting up as a new quote.
Then remain the file in the folder as the quote ref: number. with a description of what will be quoted.

When you open the workbook.
1. Enter the password
2. Enable Editing as the first time it opens with the new name it's in "Protected View" mode.
     The "Protected View" comes up as we have to save the file to a mapped drive shared storage location.
3. Select the "No" as it then asking you if you want to open in read-only.


At that point, I want it to open on the quote setup tab "Sheet8.Range ("F1").
As this is the sheet that I have a button click to open a userform.

This should be really easy, but it's not as the file name changes I can't define it in the code.
It's the "Protected View" that I think maybe the problem.
As I only get an error when that comes up and that is only when the workbook is opened for the first time with the new name.
After you open it once and you save it as that name then, the next time you open it you don't get "Protected View" and it works fine.
But if you then rename the file in the folder NOT just save as new name. Then it happens again.

Private Sub Workbook_Open()
    Dim Wbk As Workbook
    Set Wbk = ThisWorkbook
    'On Error Resume Next
    'When Workbook is opened unhide and go to quote setup tab.
    If Sheet8.Visible = fuse Then
        Wbk.Sheet8.Visible = Ture
        End If
    With ThisWorkbook
    Sheet8.Range("F1").Select '<<<<<Error 
    End With
End Sub

Select allOpen in new window

You don't really need to set the Wbk variable in that code.

I suspect Sheet8 does not exist in that workbook. In the attached example I have added a Function to check if the exists.

It might be best if you could attach examples of the workbooks

The author has marked it helpful

You cannot use that Select ike that, to avoid the error it should be

Sheet8.Select
    Range("A1").Select

Select allOpen in new window

I've just noticed that your code in the With statement doe not necessarily refer to Sheet8 of the ThisWorkBook. It needs a slight modification

 With ThisWorkbook
''/// note . added before Sheet8
    .Sheet8.Range("F1").Select '<<<<<Error 
 End With

Select allOpen in new window

In fact it would be much better to use this code.

 

Application.Goto ThisWorkbook.Sheet8.Range("F1"), scroll:=True

Select allOpen in new window

Also, note that the With Statement is not necessary in either code snippet because you only use ThisWorkBook once in that particular code.

Yes, thank you.
I go let go at work so I have stopped working on this project now.
Maybe one day I find something to use VBA again as it was really cool and exciting to learn.
Thanks for all your help Roy.

Pleased to help