How can I use event`s with a Personal Workbook macros

[Eric Penfold]

I have a situation where I am trying to use a Personal workbook macros but the event is trigged by the current open workbook. How is this possible?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    If Sh.Name <> "Summary" And Sh.Name <> "Trend" And Sh.Name <> "Supplier BO" And Sh.Name <> "Diff Depot" _
        And Sh.Name <> "BO WO" And Sh.Name <> "Diff Depot" Then
        
        
        If Target.Column = 1 Then
            If Sh.Range("AA1") = "" Then
                Sh.Range("AA1") = 1
                If DoubleClick = True Then
                    Sh.Range("AA1") = ""
                    Exit Sub
                Else
                    
                    Call Group_OrderNos
                    
                    Call Fill_NSI_Cells
                    
                    Call Duplicate_Delete
                    
                    Call Number_To_Text_Macro
                    
                    Call Format_Cells
                    
                End If
            End If
        End If
        
        If Target.Column = 10 Then
            If Sh.Range("AA1") = "" Then
                Sh.Range("AA1") = 1
                Call BO_Drop_DownList
                Call BO_Reason
            End If
        End If
    End If
    
End Sub

 

[RoryA]

Are you trying to trap the events in one or more other workbooks from within the personal macro workbook, or simply call code that is stored in the personal macro workbook from another workbook's event code?

 

[Eric Penfold]

Are you trying to trap the events in one or more other workbooks from within the personal macro workbook. Yes trying to do this. I would like all macro`s off current workbook onto the
personal workbook will this mean that i could use current workbook as xlsx instead of xlsm?

 

[RoryA]

Yes, it would but why is that important? (the answer may affect whether this approach is going to help you)

 

[Eric Penfold]

Just that i find because the workbooks without the Personal workbooks are far to big and take ages to open, run macros and save. Now that others use them means that they get frustrated by the speed of macros working then stop using the coded workbooks?

 

[RoryA]

If you use code in your personal macro workbook, that won't work for anyone else. Is that going to be an issue?

It sounds to me like you need to rethink your code if it's that slow.

 

[Eric Penfold]

Could they not save the personal book onto their Machine? It`s still worth having a personal workbook for me to save a lot of time. I`ve been through the code to make it faster but it`s still to slow.

 

[Jaafar Tribak]

Could they not save the personal book onto their Machine? It`s still worth having a personal workbook for me to save a lot of time. I`ve been through the code to make it faster but it`s still to slow.

Alternatives to personal workbooks are addins (xla\xlam) and they are more flexible... I guess addins can be saved in a shared drive so they can be accessed by other users.

Note that placing the code in the personal workbook or in an addin will help you with the size\repetitiveness of the code in the client workbooks as well as with stopping the users from getting frustrated and refusing to use the client workbooks but, I don't think it will help with the slow issue when opening\saving if the workbooks are too heavy.

 

[Eric Penfold]

If the current workbook is to be xlsx then will it save faster?

 

[Jaafar Tribak]

If the current workbook is to be xlsx then will it save faster?

I am not exactly sure about that... But one thing is sure though is that any big sized workbook will be slower (xlsx, macro enabled or otherwise)