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?

 

[rlv01]

You cannot use the ThisWorkbook object in any macro you are putting in your personal macro workbook. You have to rewrite those macros to use ActiveWorkbook instead.

In general, you cannot use the event code subroutines (like Sub Workbook_Open) in the personal macro workbook. Usually the event code is in another workbook and calls a subroutine stored in the Personal Macro Workbook.

So let's say you have this macro in your personal macro workbook:

Sub HW()
    MsgBox "Hello World!!!"
End Sub

But you want to run it in the Workbook_Open event from ANOTHER workbook. You add this code to the other workbook's Workbook_Open event.

Private Sub Workbook_Open()
    Run "personal.xlsb!HW"
End Sub

 

[zelarra]

Ok. Understood.

So, I have a question.

Why do I have ThisWorkbook and all the events you see in the screenshot in the personal macro workbook?

What's the point if it's going to give me an error and it's better to call the code in the personal macro workbook from the workbook?

Thanks a lot.

 

[rlv01]

Ok. Understood.

So, I have a question.

Why do I have ThisWorkbook and all the events you see in the screenshot in the personal macro workbook?

It is your workbook, so you would have a better idea about how it came to be there. My uninformed guess would be that someone who misunderstand how to use the personal macro workbook copied that code from another workbook and pasted it there. The point is not that nothing can be there or that the personal macro workbook cannot reference ThisWorkbook, it is that because of the special nature of the personal macro workbook it is a big mistake to do so.

What's the point if it's going to give me an error and it's better to call the code in the personal macro workbook from the workbook?

Thanks a lot.

That would depend on what you are trying to accomplish with the code. However you cannot just copy event code from a regular workbook into the personal macro workbook. You need to write a regular macro that will be stored a your personal macro workbook and called from the Workbook_Open event from ANOTHER workbook

Sub Std_WB_Open()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim SheetCountStr As String, Sheetname As String
    Dim SLen As Long
    
    Application.OnKey "%{F10}", ""
    Application.OnKey "%{F12}", ""
    
    Set WB = ActiveWorkbook
    
    SheetCountStr = CStr(WB.Worksheets.Count)
    SLen = Len(SheetCountStr)
    Sheetname = "Sheet" & Right(String(SLen, "0") & "1", SLen)
    
    Set WS = WB.Worksheets(Sheetname)
    
    WS.Activate
    WS.Range("A1").Select
End Sub

 

[jkpieterse]

ThisWorkbook handles events from -well- this workbook: the workbook containing the code.

If you want to run code when any workbook is opened, you should handle events from the Excel application. To do that, add this to your Personal.xlsb ThisWorkbook module:

Option Explicit

Private WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
    Set xlApp = Application 'This brings the xlApp events to life
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    'Write the code here that needs to run when any workbook opens
    MsgBox "You opened a workbook, this proves it works! The workbook just opened is:" & vbNewLine & Wb.FullName
End Sub

After doing this, you can find other events belonging to the Excel application by selecting xlApp in the left-hand drop-down and clicking the right-hand drop-down.

 

[zelarra]

Ok. Thanks. It works!! That's the code:

Option Explicit

Private WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()

    Set xlApp = Application

End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

    Dim NumberSheets As String
    Dim LenNumberSheets As Variant
    Dim SLen As Long
    Dim SheetName As String
    Dim strNameSheet As Object
    Dim Ws As Worksheet

    Application.ScreenUpdating = False

    Application.WindowState = xlMaximized

    Application.OnKey "%{F10}", ""

    Application.OnKey "%{F12}", ""

    Select Case Right(Wb.FullName, Len(Wb.FullName) - InStrRev(Wb.FullName, "."))

        Case "xlsm"

            NumberSheets = CStr(ActiveWorkbook.Worksheets.Count)

            SLen = Len(NumberSheets)

            SheetName = "Hoja" & Right(String(SLen, "0") & "1", SLen)

            ActiveWorkbook.Worksheets(SheetName).Activate

            Application.Goto Range("A1"), True

            Range("A1").Select

        Case Else

            Exit Sub

    End Select

    Application.ScreenUpdating = True

End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

    MsgBox "Vas a cerrar este libro"

End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

    If Sh.Type = -4167 Then Application.Goto Range("A1"), True

End Sub

Now I get another problem. I have to record a video.

 

[zelarra]

Dropbox

www.dropbox.com

Dropbox

www.dropbox.com

Dropbox

www.dropbox.com

Dropbox

www.dropbox.com

I am attaching a video, in avi and mp4, in case you have problems viewing it; and two images showing the problem.

When I activate the personal macro book, I get a screen jump: first through the personal macro book, and then through the book that I am actually opening.

Is there any way to fix this jump?

Thank you very much.

 

[jkpieterse]

I'm afraid there isn't much you can do.

 

[zelarra]

Ok, so thank you very much for your held and time.

 

[zelarra]

That's the modified code:

Option Explicit

Private WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()

    Set xlApp = Application

End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

    On Error GoTo errLbl

    Dim NumberSheets As String
    Dim LenNumberSheets As Variant
    Dim SLen As Long
    Dim SheetName As String
    Dim strNameSheet As Object
    Dim Ws As Worksheet

    Application.ScreenUpdating = False

    Application.WindowState = xlMaximized

    Application.OnKey "%{F10}", ""

    Application.OnKey "%{F12}", ""

    Select Case Right(Wb.FullName, Len(Wb.FullName) - InStrRev(Wb.FullName, "."))

        Case "xlsm"

            NumberSheets = CStr(ActiveWorkbook.Worksheets.Count)

            SLen = Len(NumberSheets)

            SheetName = "Hoja" & Right(String(SLen, "0") & "1", SLen)
            
            Wb.VBProject.VBComponents(SheetName).Activate

            Application.Goto Range("A1"), True

            Range("A1").Select

        Case Else

            Exit Sub

    End Select

    Application.ScreenUpdating = True

errLbl_Exit: Exit Sub

errLbl:

    MsgBox Err.Number & ": " & Err.Description
    Exit Sub

End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

    Application.ScreenUpdating = False

    Application.ActiveWorkbook.Save
    
    Application.ScreenUpdating = True

End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

    If Sh.Type = -4167 Then Application.Goto Range("A1"), True

End Sub