VBA Reference to Relative Cells

[AcornNut]

I have a code that, when a cell is selected it calls a module to run that code. I'm using the Select Case code to accomplish this. It's working great, but the problem is, sometimes I have to insert or delete rows on the page, which changes the cell reference of the cell I want to select. Is there a code that will keep the cell reference regardless of where the cell moves to (ie. relative reference)? In the example below, if I click B198, it runs the module for "FillOrder" just fine. But if I have to delete a row before it, then that cell moves to B197. How can I get this code to continue to reference that new location? Or is there another way to do this?

Example

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Resp As Long
Dim g As String

 If Not Intersect(ActiveCell, Range("B9", "B459")) Is Nothing Then
    Select Case ActiveCell.Address(0, 0)
           Case "B198"
             FillOrder
    End Select
 End If

 

[dreid1011]

I have a code that, when a cell is selected it calls a module to run that code. I'm using the Select Case code to accomplish this. It's working great, but the problem is, sometimes I have to insert or delete rows on the page, which changes the cell reference of the cell I want to select. Is there a code that will keep the cell reference regardless of where the cell moves to (ie. relative reference)? In the example below, if I click B198, it runs the module for "FillOrder" just fine. But if I have to delete a row before it, then that cell moves to B197. How can I get this code to continue to reference that new location? Or is there another way to do this?

Example

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Resp As Long
Dim g As String

 If Not Intersect(ActiveCell, Range("B9", "B459")) Is Nothing Then
    Select Case ActiveCell.Address(0, 0)
           Case "B198"
             FillOrder
    End Select
 End If

Is there a value in another cell on that same row that you can look for to identify the row with?

 

[AcornNut]

Is there a value in another cell on that same row that you can look for to identify the row with?

Unfortunately, no. The data for the entire row moves with the cell in question. It's an inventory form, so the entire row moves with the specific item in that cell.

 

[dreid1011]

Unfortunately, no. The data for the entire row moves with the cell in question. It's an inventory form, so the entire row moves with the specific item in that cell.

I figured it would do that. What I am asking is if there are any values in that row that are unique to that row, even after it moves?

 

[Skyybot]

I figured it would do that. What I am asking is if there are any values in that row that are unique to that row, even after it moves?

Yeah. Like the word Total, or something like that?

 

[Akuini]

Is there a code that will keep the cell reference regardless of where the cell moves to (ie. relative reference)?

Put the cell in a named range, say "myRange1" & then use the named range in the code.

If Not Intersect(ActiveCell, Range("B9", "B459")) Is Nothing Then
    Select Case ActiveCell.Address(0, 0)
           Case Range("myRange1").Address(0, 0)
             FillOrder
    End Select
End If

 

[AcornNut]

I figured it would do that. What I am asking is if there are any values in that row that are unique to that row, even after it moves?

Each row does have a unique name (the item being inventoried). It may be worth mentioning that each item calls it's own unique module to fill the order since the names of the items change in each row.

 

[AcornNut]

Put the cell in a named range, say "myRange1" & then use the named range in the code.

If Not Intersect(ActiveCell, Range("B9", "B459")) Is Nothing Then
    Select Case ActiveCell.Address(0, 0)
           Case Range("myRange1").Address(0, 0)
             FillOrder
    End Select
End If

Thanks. I tried using your method, but it still only selects the absolute cell, say "B198". Right now, when I click on "B198", which is tp inventory scissors, it runs a module that autofills an order form to order scissors. But when I add another item, say cups, before the scissors, then the scissors move to "B199", but I can't click on "B199" and run the scissors module. but "B198" (which is not scissors now) still runs the scissors order form.

 

[Skyybot]

@AcornNut , Dumb question here; Why are you adding Before and not After?

 

[AcornNut]

@AcornNut , Dumb question here; Why are you adding Before and not After?

Not a dumb question. The inventory form is laid out in a way that one can start at one end of the room and go in order around the room so they're not constantly walking back and forth. As such, if a new item is added, it may be placed in a location in the middle of the room, thus in the middle of the inventory form. Make sense? Same could be said if an item is discontinued. I would delete that item from the form causing the rest of the items to "move up" a row on the spreadsheet.