Personal.xlsb loading after selected working workbook

[DaveCG]

When I first decided to use a personal.xlsb with Excel 2016 in XLSTART to store my macro it would load before I opened the workbook I wanted to work on so it was hidden and the workbook I needed to work on loaded last and so it was active. Fine. Recently, behavior has changed and now personal.xlsb loads second, thus hiding the workbook I need to work on and forcing me to navigate to it. Not a really big deal, I know, but irritating. I tried hiding the personal.xlsb, but when it auto-loads thereafter it throws a 1004 runtime error. It appears that the macro I've saved in the personal workbook can't run hidden. So hiding is not an option.
Is there any way that when I load a workbook that it waits for the personal workbook to load first so that the workbook I need to edit is always on top? Is there any other way of achieving the same thing?

 

[MARK858]

It appears that the macro I've saved in the personal workbook can't run hidden. So hiding is not an option.

It might help to post the macro including stating the line that it is erring out on.

 

[DaveCG]

It might help to post the macro including stating the line that it is erring out on.

Well here's the Macro FWIW:

Sub Auto_Open()
    Dim c As Range
    Set c = Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlPart)
End Sub

When I set the personal.xlsm file to be hidden, save and close, then open a different excel file, I get:
"Run-time error '1004':
Method 'Cells' of object '_Global' failed"

and when I click "Debug", this portion of the above code is highlighted:

Set c = Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlPart)

All the code does is change the default "Look In" setting in the "Find" tool from "Formulas" to "Values".
I don't really care if the personal file is hidden or not, I simply want the file I'm going to be editing to be displayed without my having to navigate to it. As I said - it's not a really big issue - simply an annoyance.

 

[Fluff]

Hi & welcome to the board.
Try this. It needs to go in the Thisworkbook module of your Personal wbk

Private Sub Workbook_Open()
    Dim c As Range
    Set c = ThisWorkbook.Sheets(1).Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlPart)

End Sub

 

[MARK858]

As I am reading it you are telling it to open the personal workbook when it is already open as that is why it goes in in the start up folder then search the cells in the personal workbook which it can't as it isn't active as it is hidden.

Might be better to explain what you actually want the code to do.

I might be wrong though as on my phone so can't test.

 

[DaveCG]

Thank you for your reply.
Unfortunately, that doesn't seem to work. When I save & open the macros list, its now empty where before "Autpen" was listed before. And when I look in the "Look in" drop down of the "Find" utility, it's gone back to "Formulas" again.

 

[MARK858]

Try the link below to Tom Urtis post number 7

https://www.mrexcel.com/forum/excel-questions/47577-change-find-default-formula-value.html

Note that this was for the old Excel versions and so you need to adapt it to the locations on the ribbon in versions later than 2007.

or the easiest way is to assign the macro to your QAT.

Sub FindValues()
 Application.Dialogs(xlDialogFormulaFind).Show , 2
End Sub

then use that button rather than your normal find button.

 

[DaveCG]

I'm sorry, but that doesn't do what I need it to do. Actually, my original code works just fine:

Sub Auto_Open()
 Dim c As Range
    Set c = Cells.Find(What:="", LookIn:=xlValues, LookAt:=xlPart)
 End Sub

I simply want the personal file to not get in front of the file that I open to work on. Actually, I've been doing it so long now, I'm actually getting used to immediately navigating to the right file. I was hoping I could find a way to simply hide the personal file without getting a 1004 error, but it looks like I'll just work with what I have.
I appreciate you help