help on merged cells...

[JackkG]

Hi all,


I have a code which tracks precedents and dependents of a single cell in a sheet. But when I select multiple cells or get to try it on merged cells the code malfunctions. Though the merged cell has only one formula but it sees it as multiple cell.


So, is there any way to distinguish between merged cells and multiple cells selection to track precedents and dependents?


Thanks

Cross-posted here:
http://www.excelforum.com/excel-programming-vba-macros/1087928-how-to-distinguish-between-merged-cells-want-help-on-this.html#post4100529

[JackkG]

You can access the updates and attached file here:

http://www.excelforum.com/excel-programming-vba-macros/1087928-how-to-distinguish-between-merged-cells-want-help-on-this.html#post4100544

[Paul_Hossler]

could you attach the file here also?

I only hang out in vbaexpress.com

[JackkG]

Here you go..

[Paul_Hossler]

I marked some suggested changes with ---------------------------------------




If Selection.MergeCells Then '---------------------------------------------------------
Set rCellToTrace = Selection.Cells(1, 1)
ElseIf Selection.Cells.Count > 1 Then
MsgBox "Please choose one cell at a time."
Exit Sub
Else
Set rCellToTrace = Selection
End If


There's a few quirks -- e.g. if a cell has no dependents, the user form is still being populated. Not sure if that's your intention or not

[JackkG]

Hi Paul,

Thanks for your help. Much appreciated. A quick review of your code. When we place the cursor on cell C5 which is calling (=A5+B5), but instead of displaying Sheet1!$A$5 and Sheet1!$B$5 in the dialogbox References, "LinksList", it shows just Sheet1!$C$5 twice...any suggestions as to how to fix this?

Also got to check if cell has no dependents, why its still being populated, i'll have a look at it.

Thanks!

[Paul_Hossler]

Thanks for your help. Much appreciated. A quick review of your code. When we place the cursor on cell C5 which is calling (=A5+B5), but instead of displaying Sheet1!$A$5 and Sheet1!$B$5 in the dialogbox References, "LinksList", it shows just Sheet1!$C$5 twice...any suggestions as to how to fix this?


I have to look at it

I only looked at the Merged Cells issue

[JackkG]

Hi Paul,

got the solution, you got to make a small change in the code [stMsg = r.Parent.Name & "!" & r.Address] instead it should be [stMsg = r.Parent.Name & "!" & Selection.Address] in both FindDependents(r As Range) and FindPrecedents(r As Range) sub routine.

Thanks a lot for your help.

:)