Oct 13 2020 01:38 AM
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.
Oct 13 2020 01:50 AM
Oct 14 2020 02:01 AM
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.
Oct 14 2020 05:31 AM
Oct 14 2020 05:41 AM
No Rajesh, i am sorry, but "Forms" was my description/translation for "excelsheet for the user", means no user-forms in VBA.
Nov 12 2020 05:25 AM
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.
Nov 12 2020 06:18 AM - edited Nov 12 2020 06:50 AM
@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.