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

 

[Skyybot]

I'm having trouble understanding why the physical location of an item, and their order on the Worksheet have any relation. I may have a fruit inventory where apples are next to oranges, and then lemons, and then bananas. If apples are on the first line of my Worksheet and bananas are on the second line, I just skip the second line until I get to the bananas.

 

[AcornNut]

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

1) It saves from having to hunt for 1 item among over 450 line items; 2) Because some items need to be in bags that are ready to be grabbed and used in other areas of the building at a moment's notice. As such, there are some of the same items that are kept in different locations. Doing it the way we do it helps prevent someone from having to physically go to each location and add up a single line item which they have to find among 450+ lines.

 

[Skyybot]

Would you be able to use a simple Find function?

 

[AcornNut]

I was hoping to find something a little simpler. Plus, I have a code that works exactly the way I want it to, except when items get added or deleted. I was looking for a modification to what I had instead of writing a new code (truth: codes that loop are my nemesis. LOL)

 

[Skyybot]

Can you show me the FillOrder code?

 

[dreid1011]

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.

I am curious as to why Akuini's suggestion is not working for you. When I test it, it works as I assume is expected. I delete rows above, then test and it still runs the test code I am using. I insert rows above, then test and it still runs the test code. Perhaps give it another try? But my code is slightly different than Akuini's so maybe that is the issue?

Try this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B9:B459")) Is Nothing Then
    Select Case Target.Address
        Case Range("myRange1").Address
            FillOrder
            'MsgBox "Hello!"
    End Select
End If
End Sub

 

[AcornNut]

I am curious as to why Akuini's suggestion is not working for you. When I test it, it works as I assume is expected. I delete rows above, then test and it still runs the test code I am using. I insert rows above, then test and it still runs the test code. Perhaps give it another try? But my code is slightly different than Akuini's so maybe that is the issue?

Try this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B9:B459")) Is Nothing Then
    Select Case Target.Address
        Case Range("myRange1").Address
            FillOrder
            'MsgBox "Hello!"
    End Select
End If
End Sub

Thanks. Still doesn't work, but I get a "expected a function, not module" error. Not sure why. Copied it exactly.

 

[dreid1011]

Thanks. Still doesn't work, but I get a "expected a function, not module" error. Not sure why. Copied it exactly.

Where did you put it? And what name did you name B198?

 

[AcornNut]

Can you show me the FillOrder code?

You got me thinking about maybe modifying the module instead of the select case code. I figured since I'm actually selecting a cell I could use the ActiveCell and offset it in order to copy the necessary info to fill the order. Then I can just lock the cells I don't want selected. Seems to work so far.

Sub fillorder()
    
Application.ScreenUpdating = False
    
    ActiveCell.Offset(0, -1).Copy
        Sheets("Supply Usage Form").Range("B49").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
    ActiveCell.Copy
        Sheets("Supply Usage Form").Range("B49").End(xlUp).Offset(0, 2).PasteSpecial xlPasteFormulasAndNumberFormats
      UserForm2.Show

Application.ScreenUpdating = True

End Sub

 

[Akuini]

I figured since I'm actually selecting a cell I could use the ActiveCell and offset it in order to copy the necessary info to fill the order. Then I can just lock the cells I don't want selected. Seems to work so far.

Glad you figured it out.