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]

Please ignore last Post above
This highlights as Red say`s only goes in object modules

Private WithEvents xlAppEvents As Application

 

[Eric Penfold]

I`ve put the above in the Workbook macro still no luck.

 

[Jaafar Tribak]

I`ve put the above in the Workbook macro still no luck.

Private WithEvents xlAppEvents As Application

As I said earlier, that line goes in the ThisWorkbook Module of your Personal.xlsb, not in the client (Macro) workbook(s)

 

[Eric Penfold]

This code work fine. Because it`s in the correct module now.

Option Explicit

Private WithEvents xlAppEvents As Application

Private Sub Workbook_Open()
    Set xlAppEvents = Application
End Sub

Private Sub xlAppEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You Clicked\Selected Range : " & Target.Address(, , , True)
End Sub

But what is wrong with the code below because for some reason it won`t fire up the event code?

Private Sub MyApp_SheetSelectionChange(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 Sh.Target.Column = 1 Then
                   
                    Call Group_OrderNos(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Fill_NSI_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Duplicate_Delete(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Number_To_Text_Macro(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Format_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                End If
               
                If Target.Column = 10 Then
                   
                    Call BO_Drop_DownList(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call BO_Reason(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                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(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Fill_NSI_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Duplicate_Delete(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Number_To_Text_Macro(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Format_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                End If
            End If
           
            If Target.Column = 10 Then
               
                Call BO_Drop_DownList(Workbooks(oWbk.Name).Sheets(Sh.Name))
               
                Call BO_Reason(Workbooks(oWbk.Name).Sheets(Sh.Name))
               
            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(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Fill_NSI_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Duplicate_Delete(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Number_To_Text_Macro(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call Format_Cells(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                End If
               
                If Target.Column = 10 Then
                   
                    Call BO_Drop_DownList(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                    Call BO_Reason(Workbooks(oWbk.Name).Sheets(Sh.Name))
                   
                End If
               
        End Select
       
End Sub

 

[RoryA]

What is MyApp?

Also, this:

If Sh.Target.Column = 1 Then

should just be:

If Target.Column = 1 Then

and code like this:

Call Group_OrderNos(Workbooks(oWbk.Name).Sheets(Sh.Name))

should just be:

Call Group_OrderNos(Sh)

 

[Eric Penfold]

I`ve made the changes to code like you said.
When I open a workbook and click in a cell it shows "MSGBox" message when I click the cell. But when I change the cell value it won`t fire the code?

 

[RoryA]

You don't have any events to respond to changing a worksheet, only to selecting different cells. You also didn't answer my question abut MyApp - what is it meant to be?

 

[Eric Penfold]

MyApp is supposed to be linked to xlAppEvents in this code.

Private WithEvents xlAppEvents As Application
Private Sub Workbook_Open()
    Set xlAppEvents = Application
End Sub
Private Sub xlAppEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You Clicked\Selected Range : " & Target.Address(, , , True)
End Sub

 

[RoryA]

You need to use xlAppEvents not MyApp, since that's the variable name. For a change event, you need xlAppEvents_SheetChange rather than xlAppEvents_SheetSelectionChange

 

[RoryA]

Also, just to add, you should really use an add-in for this (if this is in fact the right way to go, which I doubt) not your personal macro workbook, since distributing that would overwrite any personal macro workbook the user might have. Somebody did that to me once and let's just say they were very lucky that I back up my macro workbook regularly...