PDA

View Full Version : [SOLVED] Can an event macro be called?



jacksonworld
08-02-2005, 04:39 PM
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
08-02-2005, 04:53 PM
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
08-02-2005, 05:02 PM
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
08-02-2005, 05:24 PM
You might find this helpful...

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

jacksonworld
08-02-2005, 07:44 PM
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
08-03-2005, 03:09 AM
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
08-03-2005, 08:58 AM
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
08-03-2005, 04:44 PM
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
08-03-2005, 05:28 PM
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
08-03-2005, 05:49 PM
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
08-03-2005, 07:38 PM
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
08-03-2005, 07:39 PM
What's with the Is keyword in the select case? :devil:

geekgirlau
08-03-2005, 08:43 PM
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
08-03-2005, 09:05 PM
Perfect. Thanks for all your help firefytr, and thank you geekgirlau. :thumb

xCav8r
08-03-2005, 09:07 PM
No kudos to the peanut gallery? Jeesh! ;)

jacksonworld
08-03-2005, 09:11 PM
Sorry. Thanks to xCav8r for the insightful comments. :)

xCav8r
08-03-2005, 09:23 PM
heehee...victory for the peanut gallery (who had nothing to do with the resolution of this problem)!!!!!!!!! :rotlaugh:

You rock, son of worldly Jack!!!! ;)

jacksonworld
08-03-2005, 09:36 PM
I realise I am being cheeky now, but using my newfound knowledge, I am attempting to convert another event macro... and failing tremendously. :dunno

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
08-04-2005, 01:32 AM
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
08-04-2005, 03:35 AM
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 :)).

xCav8r
08-04-2005, 05:58 AM
He just forgot to delete the end if. Delete it! :whip

:yes

Bob Phillips
08-04-2005, 06:50 AM
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 :)).

Have you considered workbook events?

jacksonworld
08-04-2005, 04:24 PM
Still no success.

The reason I want to include it in a module, is that in the particular spreadsheet I am applying this macro, I am already using many other macros. So if I can dump them all together in one module, it would make things much easier.