Get all references from other cells to a specific cell

Copper Contributor

Hello everybody,

 

i am working on a makro to change column height for merged cells - This works good so far.

But now i do have another cell, that has a reference for the same content (same file, but in another sheet) by formula (e.g. "=B5"), that needs the same makro to be run over it to adjust the height.

And i do not only have 1 of these "source" cells but lot of it.

 

In my current makro i am working with

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Best case would be to to the work within the same Sub.

I was also thinking about something like a reference-table but i asssume the performance would be to laggy.

 

Now my question is: Is there any way, maybe something like Target.Item(1).Reference(1..n) for such case?

Also in my mind is now, how does Excel internal know if i do have many of such formula-referencing cells ("=B5"), that it does not have to check every cell within the whole sheet (→1048576Zx16384S) to be updated.

 

Thank you in advance for information and help.

6 Replies
For better understanding please share the entire VBA code and the cell references you want to change their height.

Thank you for your replay.

I think the VBA code itself is not so relevant. In addition it is company internal use.

My post is more like a basic question, if possible, and if yes, how?

 

Anyway for better understanding a summary:

I do have within 1 excel-file 3 sheets with slightly different content. They are all forms. Now we want to make the input easier for the user so the user does not always have to copy,paste information.

Lets say

 

In Sheet 1:

The user can insert in B2 - D30 different text/information/values (e.g. his name, company, telephone, amount of pieces, costs, etc...). In between there are explanations written down, the user can not change.

 

In Sheet 2:

There are about 20 cells with reference, e.g.

B2=Sheet1!B2

D10=Sheet1!C5

C70=Sheet1!B16

...

 

In Sheet 3:

There are about 10 cells with references, e.g.

B3=Sheet1!B4

B9=Sheet1!D5

F1=Sheet1!C17

...

 

The mapping can be 1:1 or 1:n.

The main task is, that for each of the referenced values within Sheet 2+3 the makro should also be run to do the work after the user inserts/changes values within Sheet1.

 

(Please do not hang on this code. It is only an example and not the real code. But the concept is identical.) Within the general/comprehensive Workbook-Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Len(Target.Value) > 5 Then
       Target.WrapText = True
    End If
End Sub

 

Now i do not want to create a makro program to check for each individual cell everytime. I want a general code that tells me which cell in Sheet2+3 is referencing to 1 specific cell in Sheet1.



I hope the task is now clear. Please let me know what you do think about this.

You have mentioned that "They are all forms". I guess that you are talking about the User Forms for data entry !!
If yes then data entry through User Form needs VBA code,,, since you are already using then unless you will not share existing VBA macro code with me/us,,, how could I and others suggest that where you want to improve it !!

No Rajesh, i am sorry, but "Forms" was my description/translation for "excelsheet for the user", means no user-forms in VBA.

So, one last try: does any developer has an answer?

How does Sheet#1 know if some value in Sheet#2 has changed?

 

Thank you in advance.

@Christof_20  In VBA you a Range object has the following properties:

.Dependents

.Precedents

.DirectDependents

.DirectPrecedents

I think those properties should help you with your challenge.

I see your actual question is how sheet1 knows a value on sheet2 changed but you need to think it in reverse and say when value on sheet2 changes (OnChange event) then it checks its Dependents.

 

[[EDIT]] I just checked on these properties and they don't seem to be giving me the address(es) for corresponding cells on other sheets.  I'm not sure why and if I'm making a mistake but I see it giving the precedents and dependents from the same sheet but not a different sheet.  hopefully I'm just missing something.