Can an event macro be called?

[jacksonworld]

I have a simple question for someone out there.

Can an event macro be somehow entered into a module rather than a worksheet, and thus be called like other macros?

[Zack Barresse]

Simple answer? Yes.

There could be a few problems though. They are in different modules, so they may be referenced a little different, but essentially they are the same. If you posted your code we may be able to help tweak it so it will work ok.

[jacksonworld]

Fantastic.

I have 2 pieces of code in mind. Here is the first. Hopefully, I can learn to change the second piece. Thanks

Private Sub Worksheet_Change(ByVal Target As Range) 
On Error GoTo ws_exit:
    Application.EnableEvents = False
    With Target
        If .Column = 4 Then
            Select Case .Value
            Case Is = "Added"
                .EntireRow.Interior.ColorIndex = 35
            Case Is = "Increased"
                .EntireRow.Interior.ColorIndex = 20
            Case Is = "Deleted"
                .EntireRow.Interior.ColorIndex = 38
            Case Is = "Decreased"
                .EntireRow.Interior.ColorIndex = 40
            End Select
        End If
    End With
ws_exit:
    Application.EnableEvents = True
End Sub

[xCav8r]

You might find this helpful...

http://www.cpearson.com/excel/codemods.htm

[jacksonworld]

Thanks xCav8r. The link was helpful in understanding the different macros, although I would still love my code to be reworked if possible.

[Bob Phillips]

Fantastic.

I have 2 pieces of code in mind. Here is the first. Hopefully, I can learn to change the second piece.

Firstly, you have to make it Public to be able to access it elsewhere.

Then you call it prefixed by the sheet name, codename not Excel name, and pass the target range to the event code, e.g.

Sheet5.Worksheet_Change Range("A10")

[Zack Barresse]

If you just wanted to transfer it straight over, you could adapt it like so ...

Option Explicit

Sub ChangeEvent_OLD()
    Dim Target As Range
    Set Target = Selection
    If Target.Cells.Count > 1 Then Exit Sub
    On Error GoTo ws_exit:
    Application.EnableEvents = False
    With Target
        If .Column = 4 Then
            Select Case .Value
            Case Is = "Added"
                .EntireRow.Interior.ColorIndex = 35
            Case Is = "Increased"
                .EntireRow.Interior.ColorIndex = 20
            Case Is = "Deleted"
                .EntireRow.Interior.ColorIndex = 38
            Case Is = "Decreased"
                .EntireRow.Interior.ColorIndex = 40
            End Select
        End If
    End With
ws_exit:
    Application.EnableEvents = True
End Sub

As Target is a reference used in change events and not in modules, you just need to explicitly reference it and you can use it once again.

This does require one to have the cell selected. This can be altered to look at column 4 every time this runs (not necessarily selected) if you'd like.

[jacksonworld]

Thanks firefytr, but I am still experiencing some trouble.

I selected the desired cell range and ran your code, yet nothing changed. Am I forgetting to do something?

Ideally, I would like it to look at column 4 every time this runs without making a cell selection, although it is not much of an issue.

Thanks again. I appreciate the assistance.

[Zack Barresse]

Hmm, works for me. Let's try the following ...

Option Explicit 
 
Sub ChangeEvent_OLD() 
    Dim Target As Range, c As Range
    Set Target = Range("D1", Cells(Rows.Count, "D").End(xlUp))
On Error Goto ws_exit:
    For Each c in Target
            Select Case .Value
            Case = "Added"
                .EntireRow.Interior.ColorIndex = 35
            Case = "Increased"
                .EntireRow.Interior.ColorIndex = 20
            Case = "Deleted"
                .EntireRow.Interior.ColorIndex = 38
            Case = "Decreased"
                .EntireRow.Interior.ColorIndex = 40
            End Select
    Next
ws_exit:
End Sub

Note that no sheet is explicitly designated, so that means you must be on the sheet you wish the code to perform on; or explicitly reference the sheet in the Range's.

[jacksonworld]

I managed to get the first code working, but only when I selected one cell at a time.

The second code is displaying a compile error.

We are so close. And when I say we, I mean firefytr.

[geekgirlau]

There's a "With" missing:

Option Explicit 

Sub ChangeEvent_OLD()
Dim Target As Range, c As Range
Set Target = Range("D1", Cells(Rows.Count, "D").End(xlUp))
On Error GoTo ws_exit:
For Each c In Target
With c
Select Case .Value
Case Is = "Added"
.EntireRow.Interior.ColorIndex = 35
Case Is = "Increased"
.EntireRow.Interior.ColorIndex = 20
Case Is = "Deleted"
.EntireRow.Interior.ColorIndex = 38
Case Is = "Decreased"
.EntireRow.Interior.ColorIndex = 40
End Select
End With
Next
ws_exit:
End Sub

[xCav8r]

What's with the Is keyword in the select case?

[geekgirlau]

If you type "=" in the Case, the "Is" is added automatically. Personally I would just have

Case "Added"

but as long as it works ...

[jacksonworld]

Perfect. Thanks for all your help firefytr, and thank you geekgirlau.

[xCav8r]

No kudos to the peanut gallery? Jeesh!

[jacksonworld]

Sorry. Thanks to xCav8r for the insightful comments.

[xCav8r]

heehee...victory for the peanut gallery (who had nothing to do with the resolution of this problem)!!!!!!!!!

You rock, son of worldly Jack!!!!

[jacksonworld]

I realise I am being cheeky now, but using my newfound knowledge, I am attempting to convert another event macro... and failing tremendously.

Any chance someone can have a play with this one?

I will paste it as it was originally so as not confuse anyone with my topsy turvy attempts.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sh As Worksheet
    If Not Intersect(Target, Me.Range("F2:F5000")) Is Nothing Then
        With Target
            On Error Resume Next
            Set sh = Worksheets(.Value)
            On Error GoTo 0
            If Not sh Is Nothing Then
                sh.Activate
            End If
        End With
    End If
End Sub

[Bob Phillips]

Totally mystified as to why you would do this, but it ain't hard

Public Sub ChangeEvent()
Dim sh As Worksheet
Dim target As Range
    Set target = ActiveSheet.Range("F2:F5000")
        With target
            On Error Resume Next
            Set sh = Worksheets(.Value)
            On Error GoTo 0
            If Not sh Is Nothing Then
                sh.Activate
            End If
        End With
    End If
End Sub

[jacksonworld]

Thanks xld. But I am receiving a compile error on the second last line.

The reasoning behind this macro is that I am using heaps of worksheets and thus compiling a summary page. The linking to worksheets will be perfect (when I get it working ).