Excel Relationship linking Macro

[learning]

Hi Experts,

I am looking for help which allows me to select cells and link them basis of field i would configure, or group them together.

1. When i click on the any cells of these group the the whole link should change color indicating me that they are linked to a group

2. When i click on the selected field, it should list all the names in the and print them.

The example is attached in the spread sheet.
Note: Please make it in general so that i can apply to my area of work.

Thanks in Advance.
Nandu

[StephenR]

This seems to be circular. What determines the names associated with Badminton?

[learning]

Hi StephenR Guru,
There is no patten as such, looking for option to manully choose the cells.
In my real application there are lots of cells which are dependent which might or might not be sequential or order, i would manually select them and see what is arrived. The excel example i presented is only a example. Any help is appreciated.

Cheers
-- Nandu

[foxguy]

learning;

Here's a file that does what you want (I Think) Copy of Example_data_relation.xls.
Note : I created a Named Range : "Requirement_2 : RefersTo = F4:I4
Let me know how it works.

[learning]

Hi Foxguy,

Thank you, unfortunately i see differenent colors, however the macro to enable the colors when "Clicked" is missing or i am not able to see.

A little more explanation will help get me solution i think. Apologies for not making it bit more clear.

Sorry about my example..
Requirement 1: In my real application the cells color should change on clicking the cell,
the application is each cell value when clicked will show a route, a path to the user as a guide so not it is not necessary that i have the same colors shown statically for a pattern.
I could as presented just change the color of all the cells, to unique to show a relationship. However, the relationship changes based on the cell i click, (For each cell i will select and use your formulae/macro). So it is relationship is defined by my selection.

Note 1: C4 when clicked may show change of colors to C4, D6, E10. and list them in the
result cell ..
Example 1:
User Action: Click C4,
Program does:
1. Change colors(selected by user, should not prompt, predefined) of
random cells selected by user earlier to create a group say ( C4, D6, E10)
2. List those cell values in the result box.

But when i click on D6, the relationship is not same, it could be
User Action: Click D6, Program shows up change of color ( user selected) (D6, D8, C8, F8)...
Requirement 2: It is just a visual representation, of listing all the values of the cells, related (as i randomly selected), in the cell or list of cells or drop down box ( any thing is fine).

Thanks every one for the help in advance.

-- Nandu

[foxguy]

Thank you, unfortunately i see differenent colors, however the macro to enable the colors when "Clicked" is missing or i am not able to see.

Sorry, didn't mean to upload the file with all the colors.
Try this file Copy of Example_data_relation 2-2.xls
When you click on "Badminton", "BMX", "Rugby", or "Boxing", it will then color the next cell you click with the same color, until you click back on original cell.
Example: (1) Click BMX, (2) Change Cell color, (3) Click G15, (4) Click BMX, (5) Click C6, (6) Click D10, (7) Click BMX to turn off color change.
Then tell me how close we are.

[learning]

Hi Guru Foxguy,

The example rocks,, This is very close to what i am looking for. Thanks a ton.

Please can i have small addition to this.
Can a cell remember the group the cell is linked to. Refer to the requirement 1 explained.
Small change in real world situation i realize after my first post is I do not have "BMX", "Rugby" etc differentiators..

I link each cell manually and the when clicked on cell it should be colored.
Example:
Note 1: C4 when clicked may show change of colors to C4, D6, E10. and list them in the
result cell ..
Example 1:
User Action: Click C4,
Program does:
1. Change colors(selected by user, should not prompt, predefined) of
random cells selected by user earlier to create a group say ( C4, D6, E10)
2. List those cell values in the result box.

But when i click on D6, the relationship is not same, it could be
User Action: Click D6, Program shows up change of color ( user selected) (D6, D8, C8, F8)...
Requirement 2: It is just a visual representation, of listing all the values of the cells, related (as i randomly selected), in the cell or list of cells or drop down box ( any thing is fine).

I guess a bit more complex, as one has to have cell based relation to group, so that click the cell, group of cells associated has to be displayed. Is this possible as extension.

Cheers
--- Nandu

[foxguy]

I link each cell manually and the when clicked on cell it should be colored.

How do you link cells?

Note 1: C4 when clicked may show change of colors to C4, D6, E10. and list them in the
result cell ..
Example 1:
User Action: Click C4,
Program does:
1. Change colors(selected by user, should not prompt, predefined) of
random cells selected by user earlier to create a group say ( C4, D6, E10)
2. List those cell values in the result box.

In Example above, what happens if the user clicks D6? would it still show C4, D6, E10?
In Example below, if user clicks D6 it would show D6, D8, C8, F8.
Does that mean that D6 can belong to 2 groups?
How can the program know which group to show?

But when i click on D6, the relationship is not same, it could be
User Action: Click D6, Program shows up change of color ( user selected) (D6, D8, C8, F8)...
Requirement 2: It is just a visual representation, of listing all the values of the cells, related (as i randomly selected), in the cell or list of cells or drop down box ( any thing is fine).

2. List those cell values in the result box.

Where is the result box?

Also, I think using a single click to trigger these actions might cause problems later. I believe that using double clicks would be better. It is a REALLY easy change if you agree.

[learning]

Hi shg,

Apologies for pasting whole quote, meant to answer questions but went about creating a new spreadsheet to create whole confusion actually.

[learning]

Hi Guru Foxguy,

I apologize, this is totally my mistake, i own up.

Please have a look at the second speadsheet, you will realize what i am trying to do.
My real time application is very close to it, I have project to work, features to work, people are assigned to work. People are assigned to multiple work. A feature is dependent on other features. This tool you give me will help me to click on the cell (which could be feature, module, project, resource assigned).

How to tell software the cell and linked cells relation ?
I have added a second sheet "How cells are linked", please tell me if this helps.

In that sheet, i would feeding data, cell and cells linked to it, the color to be displayed (if not then the default color). The cell clicked is highlighted to user so that he knows what cell relation he is watching.

This way there are no prompts to user at all.. Please let me know what do you think, advice me.

Cheers
-- Nandu

[foxguy]

Nandu;

Ok, we may be able to work this out after all.

Do you want to use my previous macros to build the lists on sheets("How cells are linked")?

This is how I would approach this problem.

If the cells that are listed in Column B are not ALWAYS on 1 sheet then I would create an invisible sheet to store the lists along with what sheet they are on.

1. Click a cell in sheets("How cells are linked").Columns("C").
2. Set the color of that cell (leave blank to use default color that is at the top of the column).
3. Double Click that cell to tell Excel that you are creating a list.
4. Double Click the cell (on any sheet) you want to have in Column A.
5. Double Click on the cells (on any sheet) you want to have listed in Column B.
6. Double Click on the cell in Columns C (from steps 1-3) to tell Excel you are done creating the list.

7. Double Click a cell in Column A, and Excel will move you to that cell and highlight the rest of the cells in that list.
8. Single or Double Click (you tell me which you prefer) on any uncolored cell to reset highlighting back to blank.

optional:
I would also create a name for the list to store in Column D, and also store the contents of all the cells (from steps 4-5) in Columns F:Z.
Put a menu button to hide/display sheets("How cells are linked").
Double click on a highlighted cell (from steps 4-5) to take you back to sheets("How cells are linked")

Do you really need Column A? I can easily make it that when you double click on a list, Excel will move you to the 1st cell in the list and highlight the rest of the list.

Let me know if that sounds like it will work. Shouldn't take long, now that I know what I'm doing.
I won't be around tomorrow, but I'll be back the next day.

[learning]

Hi Guru Foxguy,
Thank you very much. I am glad i am working with you, you have clearly understood me.

With those clicks which generates this list, hope it will take into account, viewers will click on the cells inadverdently and whole information becomes unreliable.

Also when this "how cells are linked" are created dynamically, will the data integrity stands when i reopen the spreadsheet.

Note: Though the cells are random and each cell creates a relation to be viewed, once relationship is established, changes are very less often. I may keep adding data, which i am happy to add in cells and relation manually( that list may grow enormously).
If i were to circulate dynamic cell linkage list you propose, i have a doubt, if it stands valid with viewers clicking it to move down and using mouse by mistake.
I really appreciate in thinking much ahead in creating much advanced controls but then i would request two sheets, one to tamper with clicks to see data and other i can enable the cell linkage creation as well. I think that would be unfair for me to ask, unless you guide me to piece of macro to be deleted or commented to play around after creation of data linkages.

Regarding reset, i think double click on plain cell( cell having no relation) would be great to clear out all colors.

Thanks a ton really.
Cheers
Nandu

[foxguy]

Nandu;

Before I leave for the day, I wanted to get answers from you, so they will be ready when I get back.

With those clicks which generates this list, hope it will take into account, viewers will click on the cells inadverdently and whole information becomes unreliable.

We can protect sheet("How cells are linked") with a password, so that no one else can use it. Anyone who wants to change the relationships, will have to have the password, so no one can accidentally change them.

Also when this "how cells are linked" are created dynamically, will the data integrity stands when i reopen the spreadsheet.

Yes, when they are created dynamically, they will be stored permanently in sheet("How cells are linked"), and then will still be there when you reopen the file (as long as you save changes).

The questions you ask seem to indicate that sheet("How cells are linked") does not currently exist in your workbook. That's good, I can design it myself to do everything I think you need.

If sheet("How cells are linked") does already exist in your workbook, then please upload an exact copy of your workbook. Make a copy and then delete all information that you don't want anyone to see and then upload it, so I can see the exact layout of sheet("How cells are linked").

If it doesn't exist, then this is how I propose we set it up so no one but you can accidentally destroy relationships. I think the only thing you might have to worry about is if someone moves data around on the sheet, but I think we can even protect against that.

Hide sheet("How cells are linked"). When the sheet is hidden, no one (not even you) can destroy or modify a relationship.
Make a temporary menu button (that appears when the workbook is activated, and goes away when the workbook is deactivated) that will hide/show the sheet with a password, so you can make changes to the relationships.
When it's hidden no one (not even you) can change the relationships.

Note: Though the cells are random and each cell creates a relation to be viewed, once relationship is established, changes are very less often. I may keep adding data, which i am happy to add in cells and relation manually( that list may grow enormously).

No need to do it manually. I'll set it up so that when you select a relationship, all cells in it will change color, and you will double click cells you want to add or remove from the list.

If i were to circulate dynamic cell linkage list you propose, i have a doubt, if it stands valid with viewers clicking it to move down and using mouse by mistake.

I know this is because you don't speak English really well.
I don't understand what "if it stands valid with viewers" means, but I think you're saying that you doubt viewers will move things on the worksheets, but I think we can protect against that also.

I really appreciate in thinking much ahead in creating much advanced controls but then i would request two sheets, one to tamper with clicks to see data and other i can enable the cell linkage creation as well.

I think you're asking for a practice sheet. You will be able to create your own sheets and tamper with them as much as you want. I'll set it up on the sheet("How cells are linked") that you can delete a list when you want, so you can tamper as much as you want, then just delete the list when you are done.
Nothing I set up will do anything to your data anywhere in the workbook. The only things it will change are the lists on sheet("How cells are linked"). The only thing it will change on your data sheets will be the cell colors, and you can always manually remove all cell colors at once (without changing any data) if you need to.

[learning]

Hi Guru Foxguy,

Thank you very much. After reading your message, it is very clear to me that all my concerns are taken care off. I am more than ok with this. Thanks a ton. My question of viewers tampering relationship status is to do with the fact that when they click or double the cells sheet, cell linkage may get activated, and thus relationship may alter. However you seem to have already taken care of this scenario. Many many Thanks.

Cheers
-- Nandu

[foxguy]

One more thing before I leave. Do you want other viewers to be able activate the cell colors? They won't be able to change the relationship, just see what it is. We would have to provide them a list of cells that activate the cell colors, but wouldn't be hard.

[learning]

Hi GuruFoxguy,

Thank you. Yes i would circulate it among all the team members so that they know to see the relationship, but they wouldn't able to change any thing except play around seeing the relationships.
If that is not too difficult please.

[foxguy]

I'm starting to work on your problem now.
I read back through the thread, and I don't see where you have indicated you know how to work in the VBE.

Please answer these questions, so I can determine the best/easiest way to get my work into you work.

1. Do you know how to write macros? And where to store them?
2. Do you have any macros in your workbook?
3. Do you know how to open the "ThisWorkbook module" ?
4. Do you know how to copy/move a worksheet?
5. How many sheets do you have in your workbook?

[learning]

Hi Foxguy Guru,

Thank you for message. Here are the answers to help me find the answers.

1. Do you know how to write macros? And where to store them?
-- I have attempted and could work enough to get any macros working, Hope one day i get some guru to teach me things atleast to make my life easier at work. Yes i know where macros exists, Alt+F8.

2. Do you have any macros in your workbook?
-- No there are no macros which exists or would be created already, and i want to keep it as you give me, may be try playing around to get make some improvements (not sure if i can get it working).
3. Do you know how to open the "ThisWorkbook module" ?
-- Not sure i have understood the question so probably i am not aware
4. Do you know how to copy/move a worksheet?
-- I guess i will do ctrl+a and ctrl+v or ctrl+x ctrl +v; apologies if i am not aware.
5. How many sheets do you have in your workbook?
-- Only One sheet this moment, with a option of adding another worksheet later for a another project.

Thank you in advance.
cheers
-Nandu

[foxguy]

Nandu;

Since there is only 1 sheet in your workbook, I'm going to plan on you copying your sheet into my workbook. That's easier on me.

Here's a file Relationships.xls to play with.
I have tried to give instructions on how to use it inside.
I have not created a menu button to show/hide the Links sheet. I'm waiting until we have it working right.
Don't copy your worksheet into it yet. Just play with it, and tell me of any problem.

[learning]

Dear Foxguy Guru,

Thank you very much. I tried all the tests, unfortunately looks like your formulae or macro are not in this sheet, there is no action on any clicks noticed. Also i tried to create a new list and want to put a color, the colors ( fill color, and font color ) turned out empty.
will you please confirm or advice me.

Thanks a ton.
Cheers
Nandu

[broro183]

kia ora,

...And we still had to work out all the bugs that I didn't anticipate. I don't spend a lot of time trying to work out bugs until we decide on the final version.

Foxguy, when I spotted these sentences my curiosity got the better of me & I had a look through your code

I couldn't spot any obvious errors but I didn't look to closely at the actual logic. The few general suggestions I have are:
- Try not to use specific words that VBA already uses with a defined meaning. The examples I spotted in your code were "Change" and "Doubleclick". I recommend modifying your chosen words - perhaps by dropping some vowels.
- The range affected by the Workbook_SheetBeforeDoubleClick macro could be limited using some form of "master range" in a test using application.intersect...
- To apply more of a "structured programming" approach, see if you can remove/decrease your use of "Goto". See this thread for some discussion: http://www.eggheadcafe.com/software/...nt-in-vba.aspx
- Here's another approach (http://www.excelguru.ca/node/91) for ensuring the user has macros enabled. This approach overcomes the fact that a msgbox is not shown if macros are disabled.

...Once you move your data over, then if we discover a bug, you'll have to send me your data or I'll have to walk you through how to debug it, and it may be quite complicated.

I like your second option of teaching Nandu (or anyone) - difficult, but rewarding


Rob

[foxguy]

Rob;

- Here's another approach (http://www.excelguru.ca/node/91) for ensuring the user has macros enabled. This approach overcomes the fact that a msgbox is not shown if macros are disabled.

I had complete forgotten about this. Thanks for reminding me. I'm putting it into my template right now, so I won't forget it again.

- To apply more of a "structured programming" approach, see if you can remove/decrease your use of "Goto". See this thread for some discussion: http://www.eggheadcafe.com/software/...nt-in-vba.aspx

I appreciate the advise. Where did you see a "Goto" that wasn't an "On Error Goto ..."?
I admit that I sometimes use a "Goto" in situations like this

Label1:
    If Not SomeTest Then
        Change Something Somewhere so that SomeTest will Pass Next Time.
        Goto Label1
    End If

But other than something really simple, I don't use it except in "On Erro Goto ...".

[broro183]

hi Foxguy,

Cool, I'm pleased it reminded you
I haven't seen that version before & I think that I'll be making use of it too.

...I appreciate the advise. Where did you see a "Goto" that wasn't an "On Error Goto ..."?

LOL
Try a [ctrl + F], I found 5 of them, in the post 28 file, that weren't "on error goto...". The one that caught my eye was "GoTo ReSet_Colors" which you've used to create a looping effect. You may be better off with a "Do ... until" loop or even a For... Next loop that incorporates an "exit for" (you could also have an "Exit Do") when the condition is met.

...But other than something really simple, I don't use it except in "On Erro Goto ...".

Yes, each occurence may for something really simple, but they annihilate a top to bottom (start to finish) "flow" by creating multiple exit points (w/o even considering the cases where goto causes a looping effect) and make debugging that much more difficult.

Ooopps!
I knew I had forgotten a couple of suggestions last time...
- For portability, many users may find it easier to change a "shLinks" worksheet variable in one place near the top of your code rather than by changing the code name of a sheet. Although... on the flip side, the code name of a sheet is probably less likely to be changed in error...
- Your "label1" in your last post reminded me of this. What do the "EF" & "HE" labels stand for in your code?
Could they be identified by a more descriptive phrase?


hth
Rob

[foxguy]

Yes, each occurence may for something really simple, but they annihilate a top to bottom (start to finish) "flow" by creating multiple exit points (w/o even considering the cases where goto causes a looping effect) and make debugging that much more difficult.

You're probably right about this. I'll have to rethink my reason for doing it this way.

- For portability, many users may find it easier to change a "shLinks" worksheet variable in one place near the top of your code rather than by changing the code name of a sheet. Although... on the flip side, the code name of a sheet is probably less likely to be changed in error...

You may have a good idea here. I've been thinking about this myself. I have a situation right now where I think I'm going to change the codename of a sheet. Just having one place to change it in code would be nice.

- Your "label1" in your last post reminded me of this. What do the "EF" & "HE" labels stand for in your code?
Could they be identified by a more descriptive phrase?

EF stands for End Function, and HE stands for Handle Error. I got tired of typing the long versions, so I shortened them. I suppose I could put a comment at the top of a module what they stand for.

[learning]

Hi Foxguy Guru,
If that is easy, there is no major cell as such, it is logical in the sense that all i have is group of cells with relationship to each other, each of the cell has a relation. The way i think is major is to do with major changes or cell with huge cell linkages. So if it makes easy, you are more than welcome to get rid of the major cell thing. In short Cell and Cells which are linked to it .. to be displayed in particular color or default color and the listing of the cells thus linked to be displayed in coloumn.

Thank you a lot really.

Cheers
-- Nandu

[foxguy]

Nandu;

I changed the program completely. You should find it a lot easier to work with.
Here's the new revision Relationships 3.xls.
I haven't done a lot of debugging, so don't try to crash it.
If this looks like what you want, then we'll start debugging it.

When you download it, be sure and do a File->Save As, so that it will save your changes.
You can copy your data sheet into it, and it should work on that sheet also.
This is not the finished product, so don't do a lot of work that you want saved.

[learning]

Hi Foxguy Guru,

Thank you very much.
As i am trying to use this, i am getting confused by the major list and lists which exists in the excel. Because in my real application, if i have to create lists, then there will be as many lists as cells. In this context, i am confused. Also as mentioned major cells is logical, which basically has lots of cells in relation.

I really apologize if i were to use this as i have enclosed the second sheet on 05-26-2010, 03:47 PM, "Example_Relation_cells.xls" somewhere the major cells and lists are making it bit more complex, though i see your point.

Cell is has list of relation, which i would your cool technique of double click ( in the create state) and in viewing state ( read only) i will click cell to see the relation as explained there.

Please could you enclose me spread sheet which is not realted to lists or major cells please. If that is easy.

Thanks a ton.
-- Nandu

[foxguy]

Scratch this message. Hit Submit too soon.

[foxguy]

Nandu;

I am giving up and starting over. I can't figure out what you want. So I'll start with the minimum and build from there. Here is one of your previous files changed to turn colors on/off Relation_cells-4.xls. After it is working the way you want, then we can work on building the lists easier, and also have it working on other worksheets.

You can't work on your data yet. Let me know how this works, and we will set it up to receive your data.

[learning]

Thank you Foxguy Guru,

Unfortunately, macros are missing ( i think). Please could you attach it again.

Cheers
-- Nandu

[foxguy]

Nandu;

Unfortunately, macros are missing ( i think).

The macros are attached to the file in the last message. I have downloaded them myself, and they worked fine. I am uploading it again. This time I have a check in it to see if your macros are turned on. Relation_cells-4.xls I have verified that it works properly if macros are turned on. If you don't have macros turned on, the file will tall you.

[learning]

Hi Foxguy Guru,

Thank you very much.
After trying for a while clicking on the "example sheet" E4, D6 etc, and using other spread sheets which has macros, could not get a clue as nothing happens. Then i did this

1. Clicked on the "How cells are linked" E4 link and presto it works .. now i can turn off in the "Example" sheet..

This is exactly what i am looking for, however is it possible to fix this bug please.
So that as a viewer, i would have access or look into "How cells are linked " sheet.
Is it possible to have the ability to enable the same functionality by clicking in the "Example" sheet rather than "How cells are linked" sheet please.

2. Once enabled, disabling is working good in the "Example" sheet.

I think this sheet is wonderful and would test this with my data. Thanks a ton.

[learning]

Correction:

So that as a viewer, i would have access or look into "How cells are linked " sheet.

Apologies for missing the the word as a viewer no access to "How cells are linked" and as i author i only can view the file or manipulate the content relationship.

This is really wonderful thank you ..

Cheers
-- Nandu

[foxguy]

Nandu;

I have updated the file so that you can create Lists in My Example sheet. Relation_cells-5.xls
I have downloaded it and tested it.
There may be a minor bug in it. Sometimes it doesn't recognize my double click so I have to double click again. But I think that is my mouse getting old.

[foxguy]

Nandu;

Here is the file that does what you want Relation_cells-6.xls.
Just Double Click on E4, D6, or D8. Double Click on empty cell to turn off colors.

Note; the macros will give you trouble in the future if you decide to do things differently.

Now you have to tell me how you want to create Relations, and how you want "How Cells are Linked" to be hidden.

[foxguy]

Is it possible to have the ability to enable the same functionality by clicking in the "Example" sheet rather than "How cells are linked" sheet please.

Here is a file with the Lists on "My Example" sheet. Relation_cells-5.xls I have downloaded and tested it. It works fine.

[learning]

Hi Foxguy Guru,

Thank you very much. I have tested your tool and it works very well.
If you still have any patience, i would be truly indebted and greatly thankful, to use your tool which you have put lots of efforts. Is it possible to remove some functionality.

1.Is it possible to move the double click in the lists column to the
actual cell (Example: I would prefer to double click on cell B4, to see the relations ( B4, A6,B6,C6,D6).
and do not let user see the cell relations author has created at all.
Because the problem is, for every cell which has a relation, there need to make column lists etc
which might make the sheet bit messy. I would be happy to create that list manually in the form of cell and relations in the rows as you can see in "How cells are linked"
So in my real application, i would only show beautifully ( i assume) arranged and use anothe sheet as presented in "How cells are linked".
2. To make it little simpler, after much thoughts of usage with tool given by you, is it possible to display colored cell relation for the cell which the user double clicked, the color is as picked up from the either default or selection of color in the same format you have programmed now in "My Example".
Which means, if i double click on any cell, cell relationship of that cell only will be displayed automatically reseting old selection.
3. The cell clicked should highlighted " How about Thick Borders" so the viewer knows relation he is viewing for. Thus making it very effective tool.
4. Regarding displaying the list of cells and thier relation in coloumn format for the cell user double clicked, if it is difficult, i would give it a miss now, No worries at all, it was optional as additional treat anyways.

A very big big thank you sir. You are very much closer to what i was looking for, but i must admit you were thinking much ahead interms of features. I guess this way in a couple of more versions, i may get a advanced tool to play with. I would profusely apologize if i am cutting short your beautifully thought over features.

[learning]

Hi Guru Foxguy,

Please have a look at the attached file " Simplified" spreadsheet. Please could you comment on that. Thanks a Ton.

Cheers
-- Nandu

[learning]

Hi Guru Foxguy,

This one is really the one i am looking for viewing as i would release it tomorrow with my data
please can you increase the loop so that i can keep adding cells and cell linkages manually for the moment. 2. A magic trick to me to let me know to hide the cell relationships.

Thanks a ton.
-- Nandu

[foxguy]

Nandu;

This one is really the one i am looking for viewing as i would release it tomorrow with my data

On your data sheet, ctrl+a, ctrl+c
On My 2nd Example, ctrl+a, ctrl+v

please can you increase the loop so that i can keep adding cells and cell linkages manually for the moment.

You can already do this. See file attached to previous message

2. A magic trick to me to let me know to hide the cell relationships.

1) Select Sheet
2) In menus Sheet->Hide

To show sheet
In menus Sheet-Unhide...

[learning]

Hi Guru Foxguy,
Also please let me know if this works when i change the name of the spreadsheet or worksheet "My example" etc to any other name, or the name of cell relation sheet.

Cheers
-- Nandu

[foxguy]

Nandu;
Here is the new file Relation_cells-7.xls

Also please let me know if this works when i change the name of the spreadsheet or worksheet "My example" etc to any other name, or the name of cell relation sheet.

You can change the tab name of the worksheet from "My 2nd Example" to any thing you want.
Right mouse click on the sheet tab and select "Rename"

You cannot add another sheet yet. The How Cells are Linked sheet needs to be organized differently to handle a different sheet.
You can change the data on My 2nd Example (ctrl-a & ctrl-c from your sheet and ctrl-a & ctrl-v onto My 2nd Example).

You can also change the tab name of "How Cells as Linked" to anything you want.

[foxguy]

Nandu;

This file puts thick borders only around the cell Double Clicked Relation_cells-7-2.xls

[learning]

Hi Guru FoxGuy,

I cannot stop thanking you. This is what i was looking for. I have every thing i want to present tomorrow as a first cut, i have to work on my data though a bit more.
Unfortunately after formatting to look cells better with thick borders, double clicked cell may have to be presented in a different way i guess. Seek your advice.

2. Please have a look at the data, I have put a colors which presents groups of data.
however, once user runs the show like double click on a cell, the colors are reset, that was my request to you. oops now i realize, that sheet cannot go back to the way as presented. Do you have any suggestions for this. What i mean is, if you open the sheet now you find colors which i want viewer to see always, however when he double clicks, ofcourse your tool shows the magic. Is there a way to let the viewer get to this sheet if he clicks on empty cell.
Though i could set the sheet to read only, but the presentation is disturbed already till he viewer reopen the file.

I can live with this, for the moment sir. i don't wish to trouble you. If this change takes a long time. Please advise.
PS: how to mark this question as solved.
Tons of Thanks with Smile.
-- Nandu

[foxguy]

Nandu;

2. Please have a look at the data, I have put a colors which presents groups of data.
however, once user runs the show like double click on a cell, the colors are reset, that was my request to you. oops now i realize, that sheet cannot go back to the way as presented. Do you have any suggestions for this. What i mean is, if you open the sheet now you find colors which i want viewer to see always, however when he double clicks, ofcourse your tool shows the magic. Is there a way to let the viewer get to this sheet if he clicks on empty cell.
Though i could set the sheet to read only, but the presentation is disturbed already till he viewer reopen the file.

Is this what you want ? Relation_cells-7-2-3.xls Double Click on B13.

PS: how to mark this question as solved.

In the message # 1 of this thread. Click "Edit", "Go Advanced", "Title"

[learning]

Hi Foxguy Guru,
Thank you. well what i want is exact behavior as 7_2, however on a double click on the empty cell the spread sheet should go back the colors i have initially presented. Double click on the cell with linkages will turn out with your magic script on again. The only change being, instead of resetting to no colors, if there is a way to remember all the formattings to go back to way i wanted it to present.

cheers
-- Nandu

[foxguy]

Nandu;

Thank you. well what i want is exact behavior as 7_2, however on a double click on the empty cell the spread sheet should go back the colors i have initially presented. Double click on the cell with linkages will turn out with your magic script on again. The only change being, instead of resetting to no colors, if there is a way to remember all the formattings to go back to way i wanted it to present.

from previous message

What i mean is, if you open the sheet now you find colors which i want viewer to see always

Like this? Relation_cells-7-2-4.xls. Double Click on B13.

When your colors are the way you want, run Macro "Save_Current_Colors". You can see the original colors on How Cell are Linked column "F".

Warning: Workbook remembers how cells are colored EVERY time workbook is opened.

[learning]

Hi Guru Foxguy,

I have uploaded the spreadsheet with plan data, when double clicked, and to reset back to original colors. The relationship can seen in sheet"How cells are linked".

Thanks a ton.
-- Nandu

[foxguy]

Nandu;

I don't understand. This is what I already did. I need to see how you want it to look after you Double Click F2 and B4.

I am providing a sample workbook. On each sheet I have instructions of what I want to see. You can erase all the cells and show me what YOU WANT TO SEE.Relationships 4.xls

I suggest giving each list a name (Examples : Badminton, BMX, Accounting, etc.). Then you can provide the list of names to viewers, and they can select which list(s) they want to see colored. The names can be as long or short as you want. They can be put into the menu, so viewers can see the list, or they can be put in Right Mouse Click, etc.

[learning]

Hi Guru Foxguy,

Sorry for the delay, I have enclosed the sheet as you have suggested me.. Most of it as you rightly said already exists, except some minor things as mentioned in the spreadsheet.

The default view, is the to be displayed when double clicked.

Please note: The naming of list so that user knows what list they want to see is bit confusion to me as viewer is not expected to know any relation until he clicks each of the cell to see how they are related.

Thanks a ton.

Cheers
-- Nandu

[foxguy]

Nandu;

For some reason I can not upload a file. I have been waiting for 3 days for it to clear up, but it still will not let me upload.

Here are 2 different ways to hide a worksheet.
1) Select the sheet and in the menus Sheet->Hide
When you want to unhide Sheet->Unhide
2) In the menus Tools->Options->View->Sheet Tabs.
I can put a menu button that does either one of these for you. You just need to decide where you want the button (a) Main Menu Bar, (b) In a sub menu, (c) Right Mouse Click menu, (d) buttons on the worksheets, (e) a macro that you run with Alt+F8, or just about any where you want it.

I was finally able to upload the file. Here it is without the Macro warning. Relationships 5.xls

[learning]

Hi Guru Foxguy,

Thank you very much, I have done some testing on this. Have you noticed a bug i find it here. Your script of Cell relationship works when i first click B13, which is the cell which is not part of saved cell list.
If i click F2 or any cell relation defined, which is also there in saved color. But once I click on the cell not in saved cell, the script of cell relation works as per my expectation flawlessly. The same issue once i reset to original color. I click F2 or any cell which is defined in saved cells, there is no action.
Apart from that at this stage, i think every thing i needed at this time is there. Please is it possible to resolve this bug.

Thanks a ton
-- Nandu

[foxguy]

Hi Nandu;

Hi Guru Foxguy,
I click F2 or any cell which is defined in saved cells, there is no action.

I have fixed this bug, but I'm having a problem uploading files right now. I believe it is my mouse, it keeps sending a double click to the computer and it sends me to the wrong place to upload a file. As soon as I fix it, I'll upload the file.

[foxguy]

Hi Nandu;

I have gotten my mouse to work temporarily. Here's the file Relationships 6.xls

[learning]

Hi Guru Foxguy,

Thank you for the latest one, i guess i got the wrong one copied. please find the attached crash report.

Bugs
1. It does not reset the color of the earlier double clicked cell to saved ones.
2. Cannot take the dotted line as format in the saved cells.
But it makes more sense for me to report any issues after i get the right spreadsheet of your work.
Thanks a lot
--Nandu

[foxguy]

Hi Nandu;

please find the attached crash report.

There was no crash report attached

1. It does not reset the color of the earlier double clicked cell to saved ones.

I do not understand. What cell was earlier double clicked. What did it do? How did you try to reset the color?

2. Cannot take the dotted line as format in the saved cells.

What dotted line? Do you mean the border was a dotted line? Do you want the borders of all the cells to be saved also?

I currently can not upload files. My computer may have to be repaired. My mouse and Enter key do not work properly.

[learning]

Hi Guru Foxguy,

Sorry. For some reasons, attachments did not go through. I am trying to attach again.

"
What cell was earlier double clicked. What did it do? How did you try to reset the color? "

-- step 1: Double click B13, then click F2, you will see cell linkage colors, double clicked for B13 are not reset to saved colors.

"Do you mean the border was a dotted line? Do you want the borders of all the cells to be saved also?"
-- Yes sir. i want to present a logical groups in the borders.

For the crash, if the attachments is not sucessful, double click on the empty cell, to see the crash

[foxguy]

Hi Nandu;

For the crash, if the attachments is not sucessful, double click on the empty cell, to see the crash

Fixed double click on empty cell.
When you double click an empty cell, you want to have all lists colored with the border you chose for each list. Is that right?
When you double click the master cell of a list, you want all other lists to be white, except the one you double clicked. Is that right?

What cell was earlier double clicked. What did it do? How did you try to reset the color? "
-- step 1: Double click B13, then click F2, you will see cell linkage colors, double clicked for B13 are not reset to saved colors.

Ok let's try this. Maybe this will help me understand what you want.
I am using Relations 6 (the last file I uploaded to the forum).
This is what I do and the results.

1) When the file opens, all the groups are colored except 1 (B13:E13 have white cell color and no borders).
My Action - (Results)
2) Double Click B13 - (B13:E13 are colored. B13 has thick border. All other cells - no changes)
3) Click F2 - (Nothing happens)
4) Double Click F2 - (F2, B4:H4, D8:H8, F11, H11 all change to white cell color, and borders stay the same. All other cells - no changes)
5) Double Click F2 again - (F2, B4:H4, D8:H8, F11, H11 all get colored.)

Now you tell me what you want to happen when you 1) Double Click B13, 2) Click F2, 3)Double Click F2, 4) Double Click F2 again.

"Do you mean the border was a dotted line? Do you want the borders of all the cells to be saved also?"
-- Yes sir. i want to present a logical groups in the borders.

Will all the cells in 1 group always have the same border style? Or will the master cell have a different border than the rest of the group?
Will you manually put the border on the cells in [How Cells Are Linked]!C5:C19? Or do you want to manually put the borders on [Cell Links]!cells and the program saves them (that way each cell can have a different border if you want)?

[learning]

Hi Guru FoxGuy,

Thank you for the time Guru.
Please find the answers in Royal Blue.

When you double click an empty cell, you want to have all lists colored with the border you chose for each list. Is that right?
-- Yes
When you double click the master cell of a list, you want all other lists to be white, except the one you double clicked. Is that right?
-- Any color option if possible to provide like grey etc would be treat to see.

1) When the file opens, all the groups are colored except 1 (B13:E13 have white cell color and no borders).
--- This is because colors are not assigned so Expected behaviour
My Action - (Results)
2) Double Click B13 - (B13:E13 are colored. B13 has thick border. All other cells - no changes)[/SIZE]
-- Not Expected Behaviour; All other cells should go be to a "if possible default color grey or something to show they are disabled, if difficult, white is fine" retaining the borders formatting
3) Click F2 - (Nothing happens)[/SIZE]
-- Single click so expected Behavior
4) Double Click F2 - (F2, B4:H4, D8:H8, F11, H11 all change to white cell color, and borders stay the same. All other cells - no changes)[/SIZE]
-- Not expected; Double click on F2 will show F2 cell relationship as defined. If already selected, no change. Here it is assumed user double clicks on cell he wants to see the relationship.Rest every cell should be to default color "grey or color of choice" if difficult then white is fine, retaining the border fomatting.
5) Double Click F2 again - (F2, B4:H4, D8:H8, F11, H11 all get colored.)[/SIZE]
-- If already selected to display relation then No change, If F2 is double clicked while other cell is ex B13 cell relation is on, then show F2 relationship.

Will all the cells in 1 group always have the same border style? Or will the master cell have a different border than the rest of the group?[/SIZE]
-- Master cell, i assume is the cell double clicked, to let user know which cell relation they are seeing should be Thick Border, with bigger font.

Will you manually put the border on the cells in [How Cells Are Linked]!C5:C19? Or do you want to manually put the borders on [Cell Links]!cells and the program saves them (that way each cell can have a different border if you want)?[/SIZE]
-- Not sure if there can be easier way, i was manually putting the formatting in the cell saved coloumn along with color and i am manually adding the cell numbers. If there is easier way that would be great but may be step by step..

Once again Thanking you a ton
Nandu

[foxguy]

Hi Nandu;
Please do not answer with "if possible". Please tell me what you want. I have not seen any thing that you want that is hard to do. I just have to understand what you want.

These are the definitions of words I will use: If these are not what you want, please let me know.

"Group" = Cells that are related to each other. List of all cells in the group will be in [How cells are linked]!Column B

"Master Cell" = the only cell that will Turn On a group. The Master Cell will have a Thick Border and bigger font, and all other cells will have thin border. The Master Cell will be listed in [How cells are linked]!Column A

"Turned On" = all cells in the group have color that you have previously selected for that group. The Master Cell has thick border and bigger font, and all other cells in that group have thin border. You "Turn On" a group by double clicking the Master Cell.

"Turned Off" = all cells in the group have default color that you have previously selected (default color can be white or gray or whatever color you want). Master Cell has thick border, all other cells have thin border. You "Turn Off" a group by double clicking the Master Cell for a different group.

"Disabled" = same as "Turned Off"


When the file opens, do you want all groups Turned On or Turned Off or left the same as when the file was closed?

When you double click an empty cell, all groups will be Turned On. Is that right?
When you Turn On a group, all other groups are Turned Off. Is that right?

-- Not sure if there can be easier way, i was manually putting the formatting in the cell saved coloumn along with color and i am manually adding the cell numbers. If there is easier way that would be great but may be step by step..

Yes there is an easier way.
I can add a button to your menu, (or on the sheet, or right mouse button menu, or just about any place you want it) that creates a new group using the selected cell as Master Cell and using the color of the Master Cell as the color for that group.
Then when you double click any cell, it will be added to the group. If you double click a cell that is already in the group, it will be removed from the group.
When you double click the master cell again, it will stop adding cells to the group.

[learning]

Hi Guru Foxguy,
I realized after posting that i messed up on the fonts. I intend to make questions a bit smaller font.
Apologies.
-- Nandu

[learning]

Hi Guru Foxguy,
When the file opens, do you want all groups Turned On or Turned Off or left the same as when the file was closed?
-- Tuned on

When you double click an empty cell, all groups will be Turned On. Is that right?
-- Yes

When you Turn On a group, all other groups are Turned Off. Is that right?
-- Yes

Adding button:
Thank you very much, Please design it in such a way where when i am giving to team members, it is disabled so that no one can tamper with the integrity of data, which i fear would be circulated causing confusion.

As always Thank you a ton.
-- Nandu

[foxguy]

Nandu;

Adding button:
Thank you very much, Please design it in such a way where when i am giving to team members, it is disabled so that no one can tamper with the integrity of data, which i fear would be circulated causing confusion.

In order for the program to know that you are opening the file, it needs to know something unique about your computer, or I have to set up a password.

I can set up a button that will first ask for a password. You just have to tell me where in the menus to put it. It can be anywhere, I can even create your own toolbar to put it on, if you want.

Or the program can look for a file that is on your computer, but not on any other computer. Maybe create a text file named "Nandu.txt" somewhere on your computer.
You would just have to tell the program where the file is and every time it opens it would look for the file. If it finds it then [How Cells are Linked] is visible, no need to create a button.

[shg]

Or use the serial number of the C drive.

[foxguy]

Or use the serial number of the C drive.

Nice idea, never thought of that.

[shg]

Need code?

[learning]

Hi Guru Foxguy,
Thank you very much.
Maybe create a text file named "Nandu.txt" somewhere on your computer.
Let me know the location, i will create a file.

Thanks a ton
-Nandu

[foxguy]

Hi Nandu;

Maybe create a text file named "Nandu.txt" somewhere on your computer.
Let me know the location, i will create a file.

No, you create a file on your computer (not mine) and you tell the program where to look for it. I don't know any of your folder names.

But shg has a better idea. I'm going to set it up so you can type in your disk drives serial #, so when the file is opened on a different computer, it will hide everything.

[foxguy]

shg;

How can Nandu find the serial # for his C drive? Or do I have to write a macro that will locate it for him?

[shg]

MsgBox DriveSerialNumber("C")

Function DriveSerialNumber(sDriveSpec As String) As Long
    ' Returns the serial number of the specified drive
    
    ' sDriveSpec argument can be
    '   o   a drive letter ("C")
    '   o   a drive letter with a colon appended ("C:")
    '   o   a drive letter with a colon and path separator appended ("C:\")
    '   o   a network share specification ("\\computer2\share1")
    
    ' Returns 0 if not found
    
    On Error Resume Next
    DriveSerialNumber = CreateObject("Scripting.FileSystemObject").GetDrive(sDriveSpec).SerialNumber
End Function

[foxguy]

shg;

That's not what I meant.
What I wanted was a way for him to get the serial # outside of Excel, so he could type it into the worksheet for the Workbook_Open() to check.

But I figured out how to handle it.
I set up a macro he could run that asks for a password first, and then plugs it into a cell for him.

[foxguy]

Hi Nandu;

Here's the latest file see next message
I haven't finished with it yet, but I wanted to let you see how it is coming along.

When you open it, [How Cells are Linked] will be hidden (I Hope). To show it, run "GetSerialNumber" macro (Alt+F8). The password is "myPassword" (case sensitive). That will tell Excel that this is your computer.

To create a new Group, double click on a Non-Empty cell with no color, and hopefully the instructions are clear.

[foxguy]

Hi Nandu;

Here's an updated file that lets you set the color of Turned Off cells Relationships 7-2.xls
Set the color of Turned Off cells in [How cells are linked]!D2

[learning]

Hi Guru Foxguy,

I am truly grateful to you and your valuable time and so this forum. I have done a quick testing today and this is exactly what i was intending to present with data. Please accept my sincere thanks and wishes.

After i finish adding data, i will delete the getserialnumber macro before presenting to team. This way, i guess file is protected, I have removed the macro to do quick testing on the integrity of data. It works well as expected.
Thanks a ton.
-- Nandu

[foxguy]

Hi Nandu;

I think there was something about protecting the workbook, that I forgot to put in, but I can not remember what it was now.
So you might run into something with the file that does not work right. I will watch this thread for at least a month. If you discover something that is not right, just add a message to this thread, and I will see it.

[learning]

Hi Foxguy Guru,

I cannot thank you enough for the all the time and patience. I will definetly come back if i have any issue. Right now i am struck up with extracting data again from huge excel sheet sorting the coloumns. Once i did that, i will complete this sheet. Thank you a ton for all the efforts.

Cheers and Thanks
- Nandu