CODEWITHSUNDEEP
excelvba
Andrew Martin
Andrew Martin

Reputation: 5731

Efficient alternative to using Target.Address

I'm currently using Target.Address to identify when a cell is double clicked and run a piece of code to take the user to a relevant sheet showing the information contained in that cell.

So for example, if a cell says that 3 systems haven't done some sort of action, if a user clicks on that cell they get taken to what those 3 systems are.

Currently, I'm doing this like so:

If Target.Address = "$B$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(9))
ElseIf Target.Address = "$C$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(0))
ElseIf Target.Address = "$D$20" Then
    Win2KTrackerIncompleteFilter (strEngUnits(1))
etc

I've put the majority of the code in one small function, so this seems to be doing the job okay. However, if I were to insert a new row above row 20 (from the above example), all of these references would be pointing to the wrong place. I thought I could handle this by removing the absolute references (the $ sign) but that just breaks the mechanism.

Can anybody advise how I could either a) rewrite the code to make it more efficient and b) protect the code so new rows can be inserted and the code will remember which rows/columns it was pointing to and update itself accordingly.

Upvotes: 7

Views: 3308

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

I usually do this using named ranges, along with the Not Intersect(...) Is Nothing technique, which is a bit more robust than simply looking at the Address property.

First, name your cells e.g. as shown here:

enter image description here

If you insert more rows/columns, the names will follow the cells as they get pushed around, so you won't have to change the code each time.

Then, to test for which cells is being clicked, you can use Not Intersect(...) Is Nothing:

If Not Intersect(Target, Range("System1Report")) Is Nothing Then
    Win2KTrackerIncompleteFilter strEngUnits(9)
ElseIf Not Intersect(Target, Range("System2Report")) Is Nothing Then
    Win2KTrackerIncompleteFilter strEngUnits(0)
ElseIf Not Intersect(Target, Range("System3Report")) Is Nothing Then
    Win2KTrackerIncompleteFilter strEngUnits(1)
End If

Why do this instead of just checking if Target.Address = Range("System1Report").Address? In some circumstances (in my case, most of the time), you may want to have named ranges that include many cells. In that case, that named range's address won't match a single cell's address. Hence checking whether they intersect is more robust.

Upvotes: 3

user2140173
user2140173

Reputation:

Select a cell on a spreadsheet and give it a name

For example B3 is now named myCell

enter image description here

and edit your script a little to use the named ranges like this

Dim namedRange As Name
If Target.Address = Me.Range("myCell").Address Then
       Win2KTrackerIncompleteFilter (strEngUnits(9))
ElseIf ...
    ...
End If

So now even if you insert new rows the name will update its .RefersTo property automatically and you will not have to touch the script.

Upvotes: 7

Related Questions