Trouble setting a column range to a name in VBA

[AusSteelMan]

Hi all,

I need to set a name to a range (Col T in sheet1) so I can use it in an Intersect statement.

I am sure it is an easy fix but I can't see it.

Extracts from my (latest attempt) code:

Dim FailureGroupCodes As Range
 
FailureGroupCodes = Sheets("Sheet1").Range("T:T")

I have tried several things, but feel like I am guessing now.

This question is further to my previous question:
http://www.mrexcel.com/forum/showthread.php?t=422319
I started a new thread since 17 pages of new threads were posted in about 13 hours!!! Sorry if this was the wrong thing to do. But at least this post has a new set of tags...

Here is all the code in case it helps
Notes:
1. Sheet1 is actually called "MAE14-COG Release"
2. In the main body of code that adds comments to cells from a lookup table, "SelectedCell" used to say "T2" and the code worked perfectly when using cell T2. I am trying to expand to any cell in Col T

Private Sub worksheet_change(ByVal Target As Range)
Dim x As String
Dim y As Long
Dim FailureGroupCodes As Range
Dim SelectedCell As String
    FailureGroupCodes = Sheets("MAE14-COG Release").Range("T:T")
    SelectedCell = ActiveCell.Address
 
 
    If Not Intersect(Range("SelectedCell"), Range("FailureGroupCodes")) Is Nothing Then Exit Sub
 
 
 
        If Not Intersect(Target, Range("SelectedCell")) Is Nothing Then
            y = Sheets("LookupData").Range("B65536").End(xlUp).Row
            Range("SelectedCell").ClearComments
            Range("SelectedCell").AddComment.Visible = False
            x = Application.WorksheetFunction.VLookup(Cells("SelectedCell"), Sheets("LookupData").Range("A2:B" & y), 2, False)
            Range("SelectedCell").Comment.Text Text:=x
            Range("SelectedCell").Comment.Shape.TextFrame.Characters.Font.Size = 12
            Range("SelectedCell").Comment.Shape.TextFrame.AutoSize = True
            MsgBox "Updated"
        End If
    'End If
End Sub

I am trying to make it so the code only applies to cells changed in column T. As shown in the previous post, I got the code to work for all of Col T, but it meant that the whole sheet would react to the code - bad result.

Regards,
Darren

 

[pgc01]

Hi Darren

This is a simple example of how react to a change in a cell in column T:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("T")) Is Nothing Then
    MsgBox "I'm cell " & Target.Address
End If
End Sub

Change some cells in the worksheet, including cells in column T and check what happens.

As you see, you need neither FailureGroupCodes nor SelectedCell.

 

[AusSteelMan]

Thanks very much pgc01. Tried your code and then adapted it to suit my needs.

Here is my final code that works a gem!!

Private Sub worksheet_change(ByVal target As Range)
'
'Thanks to the good people of MrExcel.com for their help
'ravishankar and pgc01 in particular for their guidance
'
Dim x As String
Dim y As Long
Dim SC As String
 
    SC = ActiveCell.Address
 
    If Not Intersect(target, Columns("T")) Is Nothing Then
    y = Sheets("LookupData").Range("B65536").End(xlUp).Row
    Range(SC).ClearComments
    Range(SC).AddComment.Visible = False
    x = Application.WorksheetFunction.VLookup(ActiveCell, Sheets("LookupData").Range("A2:B" & y), 2, False)
    Range(SC).Comment.Text Text:=x
    Range(SC).Comment.Shape.TextFrame.Characters.Font.Size = 12
    Range(SC).Comment.Shape.TextFrame.AutoSize = True
    'MsgBox "Updated"
    End If
End Sub

I found I needed SC, since using ActiveCell did not quite work the same way. Using Active cell caused a run-time error.

I am very grateful for pgc01's and ravishankar's help, especially since it was not just the answer, but some hints so i could work out the answer.

Darren

 

[AusSteelMan]

Of course if had used "ActiveCell.Address" not just "ActiveCell" it would have worked.

I'm learning.

Darren

 

[pgc01]

Hi Darren

I hope you don't mind my commenting your code but I see 1 error.

You use ActiveCell as if the cell that is being changed is the active cell but that's not always so. You could have selected another cell in the meanwhile. You should use Target instead, Target is the cell that changed and that caused the Change Event to fire

I see also that you used the number 65536 for the number of rows in one column. That's not always true, so it's better to use the Worksheet.Rows.Count as it will work ok in any version.

This is an example of how I'd do it (not tested)

Private Sub worksheet_change(ByVal Target As Range)
Dim x As String
Dim y As Long
 
    If Not Intersect(Target, Columns("T")) Is Nothing Then
        With Worksheets("LookupData")
            y = .Range("B" & .Rows.Count).End(xlUp).Row
        End With
 
        With Target
            .ClearComments
            .AddComment.Visible = False
            x = Application.WorksheetFunction.VLookup(Target, Worksheets("LookupData").Range("A2:B" & y), 2, False)
            .Comment.Text Text:=x
            .Comment.Shape.TextFrame.Characters.Font.Size = 12
            .Comment.Shape.TextFrame.AutoSize = True
        End With
    End If
End Sub

Also

I'd not use for ex. y as the name of the variable. y refers to a row so i'd use something like lRow or lLastRow

 

[AusSteelMan]

pgc01,

I certainly do not mind comments and constructive critisism. In fact I appreciate it very much.

I tested the updated code you provided and it works. I have seen With statements before and like how the code looks much neater and readable, so thanks for updating my code. I am sure I will learn more and more from help like this.

I understand you comment about ActiveCell and am now using Target. However, I think in this example it would have worked because the code is activated when I choose a valve from the validation dropbox. Am I right in this assumption, or can I still have another cell selected in some cases?

Additionally, I renamed x and y. They are meaningful descriptions now (x=CommentText and y=LookupRangeEnd)

Thanks again,
Darren

 

[pgc01]

I understand you comment about ActiveCell and am now using Target. However, I think in this example it would have worked because the code is activated when I choose a valve from the validation dropbox. Am I right in this assumption, or can I still have another cell selected in some cases?

It can happen. For ex., let's assume a validation dropdown in T1 with one of the values equal to "V1".

As you know you can select the value from the dropdown but you can also type it.

Type V1 in the cell but instead of confirming with Enter, press the Left key or click on S1 with the mouse.

What happens is that the cell changed was T1 but the active cell is S1.

Hope it's clear.

 

[AusSteelMan]

Yes it is clear, and thanks for providing a reason.

I tried it in this book and it did not happen. The cell comment updated correctly, but I understand the difference between Target and ActiveCell better now.

Thanks,
Darren