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

 

[Eric Penfold]

The macro has a case true for specific workbooks.
Sorry but it`s still not working after the changes.
Problem was the workbooks took ages to work but when I saved them as xlsx instead of xlsm they worked a lot faster. With using Personal workbook.

 

[RoryA]

Sorry but it`s still not working after the changes.

Sorry but I need to see what you changed.

 

[Eric Penfold]

The code below has changed

Option Explicit
Private Sub xlAppEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim oWbk   As Workbook
    Set oWbk = Sh.Parent
   
    Select Case True
       
        Case UCase(oWbk.Name) Like UCase("2023 Alton Back OrderT") & "*"
           
            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
                   
                    Call Group_OrderNos(Sh)
                   
                    Call Fill_NSI_Cells(Sh)
                   
                    Call Duplicate_Delete(Sh)
                   
                    Call Number_To_Text_Macro(Sh)
                   
                    Call Format_Cells(Sh)
                   
                End If
               
                If Target.Column = 10 Then
                   
                    Call BO_Drop_DownList(Sh)
                   
                    Call BO_Reason(Sh)
                   
                End If
            End If
           
           
        Case UCase(oWbk.Name) Like UCase("2023 Coventry Back Order") & "*"
           
            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
                   
                    Call Group_OrderNos(Sh)
                   
                    Call Fill_NSI_Cells(Sh)
                   
                    Call Duplicate_Delete(Sh)
                   
                    Call Number_To_Text_Macro(Sh)
                   
                    Call Format_Cells(Sh)
                   
                End If
            End If
           
            If Target.Column = 10 Then
               
                Call BO_Drop_DownList(Sh)
               
                Call BO_Reason(Sh)
               
            End If
           
        Case UCase(oWbk.Name) Like UCase("2023 Balisdon Back Order") & "*"
           
            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
                   
                    Call Group_OrderNos(Sh)
                   
                    Call Fill_NSI_Cells(Sh)
                   
                    Call Duplicate_Delete(Sh)
                   
                    Call Number_To_Text_Macro(Sh)
                   
                    Call Format_Cells(Sh)
                   
                End If
               
                If Target.Column = 10 Then
                   
                    Call BO_Drop_DownList(Sh)
                   
                    Call BO_Reason(Sh)
                   
                End If
               
        End Select
       
End Sub

 

[RoryA]

That needs to be in the same module as the other code, which it appears it is not currently.

 

[Eric Penfold]

Fantastic works fine now.

 

[Eric Penfold]

Sorry seems i can only use the event once need to use it multiple time on the active sheet. How is this possible?

 

[RoryA]

I don't know what you mean. The event will respond whenever applicable unless you disable events and forget to re-enable them, or an unhandled error causes your application variable to be reset.

 

[Eric Penfold]

Sorry as you say I forgot to enable the events