Solved: Writing code by code

[jproffer]

I want to add a worksheet change event, but I want to make it optional to the user (It's an activecell highlighter, basically). I can make it optional with an IF statement:



[VBA]If ActiveSheet.Range("IV65536").Value = "X" Then
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = 6

Set OldCell = Target
Else
End If

End Sub[/VBA]

Two problems though:

1) An if statement won't work in this case because this is part of an add-in and I'd like to make this an option in the active workbook, whatever that may be.

2) I can't for the life of me, figure out how to change the code so that if I "run over" a cell that already has a color-fill other than yellow (or that is yellow for that matter), it will be restored to the original color when the cell is exited.

The only way I can see to solve problem one, is to write the code into the active workbook code module on the fly. Any other ideas? If not, how would one go about adding that code, by code?

Naturally the activeworkbook would change and I suppose I should remove the code when that happens (making the user "re-choose" that option each time they change workbooks).

Any thoughts or help is greatly appreciated. The second problem is sort of less important...but it's still a problem. I would imagine it would keep peace among users if the highlighter doesn't remove all their color formatting.

Thanks in advance

[Jan Karel Pieterse]

You can create a class module that will handle application level events to do this. Chip Pearson has a tutorial on class modules www.cpearson.com.

[mikerickson]

When you create the class module for the Application level event, you can add a property boolean property Enabled to the class.

Since its going to be going in an add-in, a cell in that workbook could be used to store the Enabled value between runnings of Excel, without mucking up the users workbooks.

[jproffer]

Is an application level event similar to a workbook level event? Like:

[VBA]Private Sub Application_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)[/VBA]

Just a first guess. I really don't have any idea.

[Bob Phillips]

It is, but they don't come built-in to the application, you have to craft them. Read the page Jan Karel pointed you to.

[Jan Karel Pieterse]

See attached file...

[jproffer]

That looks like it will put me well on the way. I'll have to study that for a couple.....years, lol. No, but I'll take a look tonight after work.

Many Thanks. I'm sure I'll still have some questions.

[jproffer]

OK, first question (of many, I'm sure). Modified the attachment Jan posted to this:

[VBA]
Private Sub XLApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Enabled Then
Target.Interior.ColorIndex = 6
End If
End Sub[/VBA]

That code requires that the target actually be changed to color the cell to yellow. I tried

[VBA]Private Sub XLApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
[/VBA]

and

[VBA]Private Sub XLApp_SelectionChange(ByVal Sh As Object, ByVal Target As Range)
[/VBA]

Neither of which work. What I'm after is when the cursor is moved TO a cell, the cell changes to yellow, and when the cursor leaves it returns to white (or actually, to it's original color...but thats another problem...one thing at a time).

Thanks again for your help.

[Jan Karel Pieterse]

This code in the class module seems to do the trick:

[vba]Option Explicit
Public WithEvents XLApp As Application
Public Enabled As Boolean
Private moPrevRange As Range
Private mlPrevColor As Long
Private Sub Class_Terminate()
Set XLApp = Nothing
Set moPrevRange=Nothing
End Sub
Private Sub XLApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not moPrevRange Is Nothing Then
moPrevRange.Interior.ColorIndex = mlPrevColor
End If
Set moPrevRange = Target.Cells(1, 1)
mlPrevColor = moPrevRange.Interior.ColorIndex
moPrevRange.Interior.ColorIndex = 6
End Sub
[/vba]

[jproffer]

What can I say but that. Thank you so much, that works perfectly as far as I can tell. Naturally, I'll test it further, but I'm sure it will be fine.

I owe ya big time. Maybe someday I'll know enough to actually repay the help.

Until then



Thanks again.