an update to vba routine for highlighting the current cell 3

[electricpete]

In the previous thread below, I had posted a vba routine to highlight the current cell.
thread770-485560

Recently I copied that vba code into a new spreadsheet and whenever I changed cell (activating the macro) vba threw a type mismatch error at the line "Set mySheet = Target.Parent"
Target was a range, so what's the problem?

The problem was a previous declaration Dim mySheet As Sheet1. I had been under the impression that Sheet1 was some kind of generic declaration type for a sheet. It worked fine for me in the past, and I never thought much about the 1 at the end.

Poking around in my locals window after the error occurred revealed that the "type" of Target.Parent was "object / sheet6". Initial reaction WTF is a sheet 6 type.

Then it dawns on me (duh) every sheet has a tab name you assign but it also has a number that excel assigned to the tabs originally (Sheet1, Sheet2, Sheet3) before you renamed them. It's easy to forget about that number because you only routinely see the tab name. But that original sheet number apparently remains relevant to vba long after you change the tab name. And you can see both designations for each sheet if you look in the project explorer panel within the vba window.

Finally understanding my previous gross conceptual error, my first impulse to correct the problem was to change that declaration to Dim mySheet As Sheet6. And indeed that worked, but that approach would potentially require a customization for every sheet you copy this code into. A better way is to just let it be a variant and everything works fine. I could probably delete the declaration altogether, but even without option explicit that seems like bad programming practice, and also I wanted to remind my future self about this lesson I learned. So I just changed it to Dim mySheet As Variant and now it works fine in any sheet number without customization.

If anyone is interested, here is the new code.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Current Cell row/column highlighter
' update 10/26/22 - change the sheet declaration to variant
' previous update 072121 - uses conditional formatting, and "union"
' Usage note - if for some reason a cell remains highlighted
' ... which is not the current cell, then fix it by
' ... clicking on that cell and then clicking on any other cell
' may have to adjust Sheet1 -> SheetN to match whatever is shown on LHS of project explorer for this sheet (ex sheet3)

Static lastRow, lastCol ' Holds the cell coordinates from last call to sub

Dim mySheet As Variant ' Needs to be variant or else customized to sheet1, sheet2 etc
Dim thisRange, lastRange As Range
Dim thisFC, lastFC As FormatCondition
Set mySheet = Target.Parent

' Clear highlighting from last (previous) cell from last call to this function:
If lastRow <> "" Then ' don't proceed if empty values (when initially open workbook)
Set lastRange = Union(mySheet.Rows(lastRow), mySheet.Columns(lastCol)) ' builds a range that highlights both cell and column of last cell
With lastRange
For Each lastFC In .FormatConditions
If lastFC.Type = xlExpression And (lastFC.Formula1 = "=ROW()>0") Then
lastFC.Delete
End If
Next lastFC
End With
End If

' Highlight current cell:
Set thisRange = Union(mySheet.Rows(Target.Row), mySheet.Columns(Target.Column)) ' builds a range that highlights both cell and column of current cell
Set thisFC = thisRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=ROW()>0")
With thisFC
.SetFirstPriority
With .Interior
.Color = vbYellow
.Pattern = xlSolid
End With
End With

' Save lastRow and lastCol for the next call...
lastRow = Target.Row
lastCol = Target.Column

End Sub

I'll bet many of you would've figured that error out in a heartbeat. But I'm embarassed to say it took me a few hours over several days to figure out what was going on with that type error until the breakthrough when I finally looked in locals to see the actual data type associated with the parent of the variable target. So I'm posting it here just in case anyone else is using my previous code (and is also as dumb as me about the excel object model) then I'll save you the trouble. And for those who would've figured it out in a heartbeat, you are free to have a chuckle at my expense.


=====================================
(2B)+(2B)' ?

 

[SkipVought]

But that original sheet number apparently remains relevant to vba long after you change the tab name

The Worksheet object has 3 properties that identifies a specific Worksheet:

Index-The value that represents the index number of the object within the Worksheet Collection. Can be changed by moving the Worksheet relative to other Worksheets, manually or via VBA
Name-The visible Tab Name, can be changed manually or via VBA
CodeName-The value that you see to the right of (Name) in the Properties window is the CodeName of the Worksheet. At design time, you can change the CodeName by changing this value manually. You cannot programmatically change this property via VBA.

So, the original Worksheet Name (Sheet1, Sheet2...) is just the text that a person can change and is lost forever when a Worksheet Name property is changed.

If the Worksheet having Name Sheet1 is moved, for instance from index position 1 to index position 2, the Worksheet Name remains Sheet1, but the Worksheet Index changes from 1 to 2 and obviously, Worksheet Name Sheet2 would then have Worksheet Index of 1.

In both of the two instances of change above, the Worksheet CodeName would remain unchanged.

The correction to your original code would have been...

Dim mySheet As [s]Sheet1[/s][b]Worksheet[/b]

Your original code Set MySheet to a specific instance of the Worksheet Object.

The Variant type is like a wildcard.

Like your hat-tip to The Bard of Avon.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[electricpete]

The correction to your original code would have been...

Dim mySheet As [s]Sheet1[/s]Worksheet

Yes, much better. I knew variant was a way to let excel figure out the type for me, but it relies on excel making the right decision, which sort of defeats the purpose of a type declaration. I think worksheet will do what I was originally thinking sheet1 would do. (I'll try it out when I have access to excel again tomorrow).

I knew the specifics of the excel object model better once upon a time, but I guess I'm getting older and it has grown a bit fuzzy after not mucking around with vba for awhile. I suddenly got dragged back into it when this macro didn't work for me at the worst possible time (when I really needed it to work).

Like your hat-tip to The Bard of Avon.

Thanks. It's quite the name drop for classic literature buffs, but I can't claim to be one (I'm not that sophisticated). I'm familiar enough with the works of George Boole though.


=====================================
(2B)+(2B)' ?

 

[SkipVought]

Well we all have areas of interest that grab our attention. I'm positive, as the proton bragged to the surrounding otherwise endowed trons, that I miss many, perhaps most of the signals surrounding me. But I occasionally discover a few that are like tasty morsels upon which moments of rumination delight. And yours was one!
I was fortunate to have met a guy, almost 3 decades ago, who had worked at Microsoft with Excel VBA. At Vought Aircraft in 1993, I had Excel 5.0 and was trying to replicate in a spreadsheet what I had designed on the mainframe: to display in gantt chart form the manufacturing times for each bill of material item in order to visually understand the long pole in the tent from material purchase, fabrication component assembly and final assembly.
Anyhow, this guy helped me greatly to understand how to use VBA and there were many others along the way.
I don't remember studying boolean logic in college back in the early 60s but I do remember circa 1968 at Grumman taking a course in gate logic, so George got me.

One other rather important fact about variable declarations: A declaration without a data type
(As [tt]Dim a, b As Long[/tt])
is Assumed (As [tt]a[/tt]) to be a Variant. EACH DECLARATION MUST HAVE AN EXPLICIT As DATA TYPE Assigned.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

Further to Skips comment, I went through and added types for each variable, including the two with a Static declaration. I also updated a few of the commets, and indented the loops (which, in my opinion, makes the code much more readable, and is especially a good idea if you ever want to convert VBA to Python, which requires loops to be indented).

A couple of general comments:
- If you are concerned about getting all the right types for your variables, it is a good idea to step through the code with the "Locals" window displayed. That shows the type for each variable, and its value (as soon as it has one).
- You are too hard on yourself regarding your coding skills Pete. I wouldn't have even known where to start with this routine!

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Current Cell row/column highlighter
' update 10/26/22 - change the sheet declaration to worksheet
' previous update 072121 - uses conditional formatting, and "union"
' Usage note - if for some reason a cell remains highlighted
' ... which is not the current cell, then fix it by
' ... clicking on that cell and then clicking on any other cell


Static lastRow As Long, lastCol As Long ' Holds the cell coordinates from last call to sub

Dim mySheet As Worksheet ' Needs to be Worksheet
Dim thisRange  As Range, lastRange As Range
Dim thisFC  As FormatCondition, lastFC As FormatCondition
Set mySheet = Target.Parent

' Clear highlighting from last (previous) cell from last call to this function:
If lastRow <> 0 Then ' don't proceed if empty values (when initially open workbook)
    Set lastRange = Union(mySheet.Rows(lastRow), mySheet.Columns(lastCol)) ' builds a range that highlights both cell and column of last cell
    With lastRange
        For Each lastFC In .FormatConditions
            If lastFC.Type = xlExpression And (lastFC.Formula1 = "=ROW()>0") Then
                lastFC.Delete
            End If
        Next lastFC
    End With
End If

' Highlight current cell:
Set thisRange = Union(mySheet.Rows(Target.Row), mySheet.Columns(Target.Column)) ' builds a range that highlights both cell and column of current cell
Set thisFC = thisRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=ROW()>0")
With thisFC
    .SetFirstPriority
    With .Interior
        .Color = vbYellow
        .Pattern = xlSolid
    End With
End With

' Save lastRow and lastCol for the next call...
lastRow = Target.Row
lastCol = Target.Column

End Sub

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

I should have checked properly before posting!

Having declared LastRow as a long, the line where it is checked needs to be changed to:

If lastRow <> 0 Then ' don't proceed if empty values (when initially open workbook)

(Posted code now edited)

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[skeletron]

Appreciating this discussion fellas. I ran into a similar issue as OP and it took me some time to wrap my little head around. Thanks for spreading some knowledge.