Excel Relationship linking Macro

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

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.

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.

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

How do you link cells?

Originally Posted by learning

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.

I still don't understand

Question 1.

Originally Posted by learning

Originally Posted by foxguy

How do you link cells? .

I would follow your suggestion on this, data is huge so easy like CNTL with Cells
may help me. that should be linked to the cell only.

Are you saying you want to use the macros that I've already written? Or are you asking for my suggestion (that I haven't made yet)?
I'm guessing that you haven't decided how to link cells.
How big is the data? Is it all on 1 worksheet? Is it organized like your Example with 1 semester per line?
Will my current macros work to link cells.
Example:
1. Double Click on BMX and program changes color of cell.
2. Double Click on name and program changes color of cell to match BMX color.
3. Repeat 2 as many times as you want.
4. Double Click on BMX and program stores the list of cells linked somewhere and changes ALL cells back to blank.


Question 2.

Originally Posted by learning

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.

Originally Posted by foxguy

In Example above, what happens if the user clicks D6? would it still show C4, D6, E10?

The values i am working are random and may or may not relate to each other.
which means the relation ship made with D6. (C4, D6, E10) may vary with when i click
C4 which could be same or different set of values ( C4, D10, E10) may be or some times it still shows existing relationship.

Are the random cells the same cells that were linked from the Question 1?

Originally Posted by learning

Originally Posted by foxguy

If C4 belongs to 2 groups, how will the program know which group to change color?

Should be dependent on which cell, user has clicked. Normally from the data, most of the time it would be major cell and minor cell (a complexity i removed because i know what cell to click to see the relationship).

I'm lost here. You may know what cell to click, but the program needs to know also, so when you click the cell, the program knows what to do.

I think I really need to know what you're trying to do. I think you want this:
1. User creates a group by linking cells together (each cell can be in more than 1 group)
2. User wants to see who belongs to the BMX (or Rugby, etc) group all at once.
3. User wants to see what groups a name belongs to and everyone else in the same groups.

Hi Guru Foxguy,

I am sure if you see the example attached, you will have little more clarity on what i am
trying to achieve in tems of linking cells.

Note 1: I used 4 test results for clarity only, maybe it makes sense to present one test at a time. Till i click or double click another cell which will display a new relation ship.
Note 2: Since it becomes messy, i have no shown any color.
Note 3: To make life easy, it is more than ok to fix one color (Yellow ?? will that be good, as long as i can see cell names). So when i double/single click another cell the results will change.

Things i will leave for you to make simplified or more friendly usage is
1. Color changes
2. Option to reset ( a separate option or when you click another cell)
3. When i close the excel, would you reset to blank color
Any thing else you might think off as long as i get those results

Thanks a ton for all this help.

Cheers
- Nandu

learning/Nandu,

Please don't quote whole posts. It's just clutter.

Thanks.

Nandu;

I'm sorry, I'm just not getting it. Your new workbook just confuses me more.

This is probably my weakest point. I don't know how to ask questions to get the answers I need.

Looking at the first file you uploaded:
I understand that you want to be able to double click a cell and have Excel highlight other cells. I just can't figure out how to tell Excel which cells you want highlighted, and when. You seem to want to click on Badminton and have one set of cells highlighted (that includes Stephannie) and then click on Stephannie and have a different set of cells highlighted. And I can't figure out how to tell Excel which set goes with which cell.

Why don't we start at the beginning, and do one thing at a time.
Step 1 is to create groups. If that is correct, then can the workbook I uploaded earlier be used to create your groups? I know it won't let you put a name into 2 groups, but if we fix that, will it let you create the groups?

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.

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.

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.

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?

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

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.

Originally Posted by 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

Maybe I didn't do it right. At the last minute, I protected the sheet, but didn't check to see if it still worked right with the sheet protected. I also have 3 files open with macros for my own use on my computer. One of them might have interfered with the file.

When you say there is no action, it makes me think that you have macros turned off. In the Excel menu Click Tools->Options->Security->Macro Security and make sure that you do not have "High" selected. If it's medium when you open the workbook, it will ask you if you want to allow macros to run.

I have put a message box into the file when it opens the first time, saying "This is just to make sure that you have macros enabled". If you don't get that message, you need to enable macros.

It's also possible that you are not Double Clicking fast enough. I put a message in when you do the first Double Click saying "This is to make sure that you're Double Clicking fast enough". If you do not get that message you need to Double Click faster

Here's the updated file Relationships 2.xls. I have tested it with no other files open, so hopefully I got it right this time.

Originally Posted by learning

Thanks a ton for this valuable effort. I have sent you two private messages let me know if you cannot access it.

There are not any private messages for me in my inbox.
I discovered another bug in the protection. So one more time here is an updated file Relationships 2.xls.
Let me know if you're playing around with it, and how it works.

1. Error:1004, Application defined or object defined error resume .. this error message turns up quite often whenever i double click on blank cell, 2. when ever i go back to list cell and double click it. some times randomly.

I can't get this error on my computer.
When you get the error, does it give you the option to "Debug"?. If so, click "Debug" and when the code appears click "Ctrl + L" to show the Call Stack. Copy the line of code and also the Call Stack, so I can see where you were at. After copying the Line and Call Stack, click "F5" and then "Cancel".

If there is no option to "Debug", then select "Cancel" and when the code appears, click "F8" twice and it should put the cursor on the line that caused the error. Copy it for me, so maybe I can figure out what is happening. Also click "Ctrl + L" to show the Call Stack and copy that for me. After copying the line, click "F5" and then "No".



I think most of your questions will be taken care of in the next version. (see below). This is typical of most complicated programs with clients that are new to custom made programs, they seldom know how complicated it is and what to ask for. Most people just accept what Excel can do and just live with it. Once I had 6 different versions, before the client was happy with the way things worked. 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.

5. Since macros and double clicks are confirmed, is it possible to delete those prompts so that it would be easy to use.

I forgot to tell you this. When you download the file from Excel Forum it is "Read Only", so changes are not saved. You need to "Save As" when you first open it, so changes will be saved when you close it.

6. Is it possible to show in relation of cell clicked in column format ( the values of the cell), cell selected on the viewing spread sheet. The relation ship display of your design is good to create a relationship in tems of cell position displayed in rows, i really like it as i have manually added cell relationship and it works.

I completely forgot about this. When you are on your data sheet, where do you want the contents of the List put? In the first workbook you uploaded you had all the lists to the right of the data. Do you want it like that? Do you want just the Active List's contents listed, with a drop down list to select which list to make the Active List, or all list's contents to the right of the data? I can insert a column "A" with the contents of all the cells in the Active List, and then remove it when the List is no longer active (that might get confusing when you try to maintain the List manually, but hopefully you won't ever have to do that). Or I can put the List or lists just about anywhere you want.

I think the concept is much more advanced then i wanted. I will wait for your instructions and copy my data to play around when you are ready.,

If this is more than you want, just tell me what you don't want. It did occur to me last night, that it might be less confusing if I hide the "HC" sheet, and just let you Double Click on cells and ask you what you want to do. I was starting with "how do you maintain the lists", but I should have started with "how do you want to activate and display a list".

This is what I'm going to set up today.
1) Hide sheet("HC") (I will rename it in the finished product, but it's easier right now).
2) When you Double Click on a cell in your data sheet and it is not a Major Cell in any list, a message box will ask you to name the List you want to create. This will make the cell you Double Clicked the Major Cell in that List. You can then change the colors of the Major Cell to whatever you want for that List. You then Double Click on all the cells you want in that List. The contents of all the cells in the List will be displayed in column format where you want them displayed as you add cells. When you Double Click on an empty cell it will ask you if you want to quit creating the List or add the cell to the List.
3) When you Double Click on a cell that is a Major Cell in a List already created, it will highlight all the cells in that List and display the contents of all the cells in column format where you want them displayed.
4) I'll also put a temporary menu button in the menus for List Maintenance (show sheet("HC")).

I think this will be less confusing.

To copy your own data into the workbook:
You can do this:
1) select my workbook
2) create blank sheet
3) select your worksheet
4) ctrl+a, ctrl+c
5) select my workbook
6) ctrl +v

But if you want to start getting better at using Excel's powers I suggest this:
1) Select your worksheet.
2) Right mouse click the Sheet Tab.
3) Select "Move or Copy..."
4) In "To Book:" select my workbook.
5) In "Before Sheet:" select where ever you want it to be.
6) IMPORTANT Click "Create a Copy". If you don't click this Excel will move your worksheet into my workbook and you will lose it.
7) Click "OK"
This will copy more than just the data in the sheet, it will also copy the entire sheet including things you don't see like range names.

Now you can play with it all you want, but your original file will still be there.
Until all the bugs are worked out, you don't want to move your data into my workbook. 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.

kia ora,

Originally Posted by foxguy

...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.

Originally Posted by foxguy

...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

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

Please Login or Register  to view this content.

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

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.

Originally Posted by foxguy

...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.

Originally Posted by foxguy

...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

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.

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.

Scratch this message. Hit Submit too soon.

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.

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.

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.

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.

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.

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.

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...

Nandu;

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

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"

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.

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

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.

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

Hi Nandu;

Originally Posted by learning

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.

Hi Nandu;

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

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.

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)?

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

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.

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.

Or use the serial number of the C drive.

Originally Posted by shg

Or use the serial number of the C drive.

Nice idea, never thought of that.

Need code?

Originally Posted by shg

Need code?

You could tell me is there is a different way than using the FileSystemObject. I had a client in the past that FileSystemObject didn't work on his computer, which is why I have avoided using it. Never did figure out why it didn't work on his computer, I just created a workaround.

Nope, same.

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.

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?

MsgBox DriveSerialNumber("C")

Please Login or Register  to view this content.

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.

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.

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

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.