Highlighting active cell
Posted by Charlie Carroll on July 27, 2001 10:16 PM
Since some of my users complained that they weren't always sure where the cursor was and I couldn't change their setups, I pursued highligting the active cell so long as there wasn't any conditional formatting in it (which would have taken precedence anyway). Also needed to turn it back to colorless unless the interior color was changed from whatever highlight color I used during processing in the active cell. Working with a programmer, we developed the macro below. I decided I wanted to put it in my own personal.xls. Did so and found it working fine in personal.xls but not carrying over to new sheets. Made the obvious change to "Public" from "Private" and got compile errors. Turned it back to "Private" and now find it still works fine in personal.xls only but that it puts a power blue (the color (20) I happened to choose in A1 on new sheets but doesn't move the highligted cell. I believe this is a "dumb pet trick" which would be very popular with most users and would like some help on figuring out what we have done wrong.
Public address1 As Integer
Public address2 As Integer
Private Sub Workbook_Open()
ActiveCell.Interior.ColorIndex = 20
address1 = ActiveCell.Row
address2 = ActiveCell.Column
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
ActiveCell.Interior.ColorIndex = 20
If address1 <> 0 And address2 <> 0 Then
ActiveSheet.Cells(address1, address2).Interior.ColorIndex = xlNone
End If
address1 = Target.Row
address2 = Target.Column
End Sub
Posted by Dax on July 28, 2001 10:21 AM
If I understand you correctly then you want this macro to run for any workbook that the user has open. As the macro is, it will only run in the workbook where the macro is contained and this is logical as you're working with workbook level events, i.e. those events fired by something happening in the workbook. What you need is to utilise application level events and you can do that like this:-
In Personal.xls in the VB editor click Insert, Class Module. Then paste this code:-
Public WithEvents XL As Excel.Application
Public address1 As Integer
Public address2 As Integer
Private Sub XL_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveCell.Interior.ColorIndex = 20
If address1 <> 0 And address2 <> 0 Then
ActiveSheet.Cells(address1, address2).Interior.ColorIndex = xlNone
End If
address1 = Target.Row
address2 = Target.Column
End Sub
Private Sub XL_WorkbookOpen(ByVal Wb As Workbook)
ActiveCell.Interior.ColorIndex = 20
address1 = ActiveCell.Row
address2 = ActiveCell.Column
End Sub
Now, in a standard module enter this code:-
Dim x As New Class1
Sub InitialiseApp()
Set x.XL = Application
End Sub
Now the only thing to do is execute the InitialiseApp macro. You could do this in the Workbook_Open procedure of Personal.xls.
You will now find that the highlighting macro works in any Excel workbook. Obviously your users will need to have the same code in their personal.xls (or any other workbooks code). In the past I've created an addin and then asked users to install the addin before using my application.
HTH,
Dax.
Posted by Charlie Carroll on July 28, 2001 5:40 PM
Dax: Thank you very much. The xla is a great idea. Actually I will include the code in the "this workbook" parts of any applications I send out. I just wanted to be able to use it in all of my workbooks and to be able to make it available to others if and when they want it. You have done this - thanks!
