Personal Macro Book

[zelarra]

I want to put all the codes that I use on a regular basis in my personal macro book, and work from there.

The ultimate goal is that if I have, for example, 50 different spreadsheets on my computer, and I make a change to any code, I don't have to modify it in all 50 sheets.

Based on that, the first one I'm passing affects ThisWorkbook. In this one, I have three different codes:

1. When I open the book, it maximizes, disables F10 and F12, and takes me to cell A1, positioning the sheet in said cell, of the sheet with the following criteria:

a. If there are less than 10 sheets, Sheet1.
b. From 10 to 99, Sheet01.
c. From 100 to 999, Sheet001.
d. Etc.
e. In case of error, it takes me to a sheet, whichever one, but not an error.

2. When I close the book, it saves the changes.

3. When I change tabs, it takes me to cell A1, positioning the sheet in that cell.

In points 1 and 2 I also have Application.ScreenUpdating set, so that the user does not see what is being done.

That's the code I have now:

Option Explicit

Private Sub Workbook_Open()
   
    Dim NumberSheets As Variant
    Dim LenNumberSheets As Variant
    Dim strNameSheet As Object
    Dim Ws As Worksheet
   
    Application.ScreenUpdating = False
   
    Application.WindowState = xlMaximized
   
    Application.OnKey "%{F10}", ""
   
    Application.OnKey "%{F12}", ""

    NumberSheets = CVar(Sheets.Count)
   
    Sheets(ActiveWorkbook.VBProject.VBComponents("Hoja" & String(Len(NumberSheets) - 1, "0") & "1").Properties("Index")).Select
   
    Application.Goto Range("A1"), True
   
    Range("A1").Select
   
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
       
    Application.ScreenUpdating = False
   
    ActiveWorkbook.Save
       
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
    If Sh.Type = -4167 Then Application.Goto Range("A1"), True
   
End Sub

However, I get this error:

Runtime error

Error in the "Select" method of object "_Worksheet"

Also, the event on closing or activating a sheet doesn't work for me.

Can anyone help me out?

 

[smozgur]

Ok. Thanks. It works!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.