Passing ROW() from Table Cell calls VBA function for every Table Row instead of the single row

pizzaboy

Board Regular
Joined
Mar 23, 2015
Messages
56
Office Version
  1. 2019
Platform
  1. Windows
When a cell in a Table row changes, I need to pass in the row number so my custom function can make multiple amendments to that row.

I have this formula set in one of the cells so when [@[REGION_ASSIGNED]] changes, the row number is passed in (for that single row).
Code:
=ChangeFlag("DATA",ROW(),[@[REGION_FLAG_IMAGE]],[@[REGION_ASSIGNED]])


Everything works fine EXCEPT, ROW() seems to cause EVERY row in the table to call the function (instead of the one row that is expected).


Any ideas?

Cheers
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Here is a method to identify which row changed

Place this in the SHEET module (will not work in standard module)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Me.ListObjects(1).ListColumns("REGION_ASSIGNED").DataBodyRange

    If Not Intersect(Rng, Target) Is Nothing Then
        MsgBox Target.Row, vbOKOnly, "Row changed :"
    End If
End Sub


If there is more than one table on the sheet then
Set Rng = Me.ListObjects("NameOfTable").ListColumns("REGION_ASSIGNED").DataBodyRange
 
Last edited:
Upvote 0
Kinda there Yongle...

When I amended the code to verify why it wasn't working:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    'Dim Rng As Range
    'Set Rng = Me.ListObjects(TB_VIDEOS_DATA).ListColumns("REGION_ASSIGNED").Range.Column
                'ActiveWorkbook.Worksheets(WS_VIDEOS_DATA).ListObjects(TB_VIDEOS_DATA).ListColumns("REGION_ASSIGNED").DataBodyRange


    If (Target.Column = Me.ListObjects(TB_VIDEOS_DATA).ListColumns("REGION_ASSIGNED").Range.Column) Then
        Call Videos_Data.LanguageChanged(Target.row)
    End If
    
End Sub

Appears the Change() event is only triggered when a cell is manually changed, NOT when its affected by a calculation of another cell...

[REGION_ASSIGNED]=IF([@[REGION]<>"",[@[REGION],...)

So If I change the contents of [@[REGION], the Change() event occurs. When the result is assigned to [@[REGION_ASSIGNED], the event does not trigger.
 
Last edited:
Upvote 0
Yes - the trigger must be the cell where the change is made

Which cell (or range) in which sheet is being amended manually to cause a different value to be returned in column REGION_ASSIGNED ?
 
Last edited:
Upvote 0
Theres quite a few cells overriding the value thats why I wanted to just trigger the change at that one cell instead of performing lots of checks (too difficult to maintain)...

Thanks anyway mate! At least I got some insight into another way I could get this to work.
 
Last edited:
Upvote 0
Why is it difficult to maintain?
- is the range of cells always changing?

Presumably you are not changing the formula in the table on an ongoing basis
- if rows are being inserted above "trigger cells" then use a named range to define "trigger range" ( named ranges auto- adjust and so can be used in VBA to get around things like this)

If you need more help, then provide additional details
thanks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top