Making Target.Address work after Iinserting a Line

Question

I am using:

If Target.Address = "$C$56" Then

    Range("C56").Value = "X"

    Range("D56").Value = ""

    Range("E56").Value = ""

ElseIf Target.Address = "$D$56" Then

    Range("C56").Value = ""

    Range("D56").Value = "X"

    Range("E56").Value = ""

ElseIf Target.Address = "$E$56" Then

    Range("C56").Value = ""

    Range("D56").Value = ""

    Range("E56").Value = "X"

in a VBA subroutine.  This works find until I insert a line in the associated spreadsheet.  The addresses in VBA do not update to the added line and the code stops working.

I have read about assigning a named range and somehow referencing the various cells within the range as a way to fix this issue, but no matter what I have tried, it does not work.  Can anyone help?

Thanks,

Answer 1

BTW

The video in the link below will help you to understand the Intersect method

https://www.youtube.com/watch?v=qTKk0TorMZo

Answer 2

Leo:

Ref, "... I do not know what If Not Application.Intersect(Target, myRange) Is Nothing Then does.  Could you explain?"

It means if the active cell (target) is within the range i.e C56:E59 then...do whatever

The following macro will work as per your requirements

''''*************************************************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range

Set myRange = Range("Metal_Options")

''' If the active cell is within the myRange

If Not Application.Intersect(Target, myRange) Is Nothing Then

    If Target.Cells.Count > 1 Then Exit Sub  '' Select only one cell

        Range("C" & Target.Row & ":E" & Target.Row).Value = ""   ''' Clear the values

        Target.Value = "X"   ''' Insert X in the active cell

End If

End Sub

'''***********************************************************************************

Regards

Answer 3

Hi Leo

Although it is not clear what event you are using to execute the macro,

Try the code lines below

Dim myRange As Range

Set myRange = Range("C56:E56")

If Not Application.Intersect(Target, myRange) Is Nothing Then

        myRange.Value = ""

        Target.Value = "X"

End If

Answer 4

You could define named ranges like this:

And use code like this:

If Target.Address = Range("C_Cell").Address Then

    Range("C_Cell").Value = "X"

    Range("D_Cell").Value = ""

    Range("E_Cell").Value = ""

ElseIf Target.Address = Range("D_Cell").Address Then

    Range("C_Cell").Value = ""

    Range("D_Cell").Value = "X"

    Range("E_Cell").Value = ""

ElseIf Target.Address = Range("E_Cell").Address Then

    Range("C_Cell").Value = ""

    Range("D_Cell").Value = ""

    Range("E_Cell").Value = "X"

End If

Answer 5

Jeovany:

I am programming spreadsheet cells to act like radio buttons since I had a difficult time trying to get them to work in groups in my spreadsheet.

I have four components with each having three possible selections of which the user can select only one.  I have the components in different rows and the selection options in three columns So the idea was to create code to put an X in the cell that the user clicked and make the two other cells in the same row be blank.  The sample code above is for the first row of cells.

I am triggering this using Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Everything worked fine until someone inserted a couple of lines above my selection range.  Then the addressing no longer worked as the row numbers changed in the spreadsheet but did not carry over into the VBA code.

I have read online that using a named range could alleviate this issue although I have not discovered how this might work.  I created a range in my spreadsheet for cells C56:E59 and named Metal_Options.  I then want to treat each row as a group.  If the user clicks on any cell in the row, it will have it's value set to "X" and then the other two cells in that row will have their values set to "".  To do this there would have to be some way to reference each of the 12 cells in the range.  I thought I might be able to identify specific cells within that range using Offset(), something like this:

If Target.Address = Range("Named_Range").Offset(0, 0).Address Then

    Range("Named_Range").Offset(0, 0).Value = "X"

    Range("Named_Range").Offset(0, 1).Value = ""

    Range("Named_Range").Offset(0, 2).Value = ""

but this did not work either.

Lastly, I do not know what If Not Application.Intersect(Target, myRange) Is Nothing Then does.  Could you explain?

Thanks,