+ Reply to Thread
Results 1 to 89 of 89

Excel Relationship linking Macro

  1. #1
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    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
    Attached Files Attached Files
    Last edited by learning; 06-03-2010 at 04:16 PM. Reason: Lots of thanks to Foxguy.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Excel Relation Ship linking Macro

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

  3. #3
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relation Ship linking Macro

    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

  4. #4
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relation Ship linking Macro

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

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  5. #5
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  7. #7
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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

  8. #8
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    I link each cell manually and the when clicked on cell it should be colored.
    How do you link cells?

    Quote Originally Posted by learning View Post
    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.

  9. #9
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    Hi Guru Foxguy,

    Thank you very much for all this, sorry couldn't check yesterday.
    Since i still have some idea and evolving as i would work in it. Here are my answers

    Quote Originally Posted by foxguy View Post
    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.


    Quote Originally Posted by foxguy View Post
    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.

    Quote Originally Posted by foxguy View Post
    In Example below, if user clicks D6 it would show D6, D8, C8, F8.
    Does that mean that D6 can belong to 2 groups?
    Yes some times D6 may belong to two groups as they may be dependent that way.

    Quote Originally Posted by foxguy View Post
    How can the program know which group to show?
    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).

    Quote Originally Posted by foxguy View Post
    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 would take this as sound advice and of course accept it

    Probably a reset option to setback spread sheet to original blank color, will that add to the complexity, if yes ignore it please.

    Thank you very much, really great.
    Hope i have a online tutor here, would you be one.

    cheers
    -- Nandu

  10. #10
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    I still don't understand

    Question 1.
    Quote Originally Posted by learning View Post
    Quote Originally Posted by foxguy View Post
    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.
    Quote Originally Posted by learning View Post
    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.
    Quote Originally Posted by foxguy View Post
    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?
    Quote Originally Posted by learning View Post
    Quote Originally Posted by foxguy View Post
    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.
    Last edited by foxguy; 05-25-2010 at 01:14 PM.

  11. #11
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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
    Attached Files Attached Files
    Last edited by shg; 05-25-2010 at 08:40 PM. Reason: deleted spurious quote

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Relationship linking Macro

    learning/Nandu,

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  13. #13
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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?

  14. #14
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    Hi shg,

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

  15. #15
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 05-26-2010 at 10:17 PM.

  17. #17
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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

  18. #18
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  19. #19
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  20. #20
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 05-27-2010 at 06:34 PM.

  21. #21
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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.

  22. #22
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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?

  23. #23
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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
    Last edited by learning; 05-28-2010 at 08:46 PM. Reason: Customary thanks missed out.

  24. #24
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 05-29-2010 at 01:49 AM.

  25. #25
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  26. #26
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Quote Originally Posted by learning View Post
    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.

  27. #27
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    Hello Guru Foxguy,

    Thanks a ton for this valuable effort. I have sent you two private messages let me know if you cannot access it. I think it looks very promising and friendly. I would wait for your instructions to proceed to use copy my data to see it working.

    Thanks a Ton
    Cheers
    -- Nandu

  28. #28
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Quote Originally Posted by learning View Post
    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.

  29. #29
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    Hi Guru Foxguru,

    Thank you for all efforts and help. I have played with for some time.
    I have done some testing on this and have these small nagging issues i guess would be easy for you to sort out.
    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.
    2. The color and font selection is missing in "HC" sheet but i could still see them working in "sheet 2".
    3. Double click on "G3" does not always links to "Test 5" cell in second sheet.
    4. Created my own list "Nandu" for test and want to create double click on cells to create a relationship, the application some times opts to deselects the list as double click on the blank cells is also reset.
    5. Since macros and double clicks are confirmed, is it possible to delete those prompts so that it would be easy to use.
    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 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.,
    Thanks a lot.

    Cheers
    -- Nandu

  30. #30
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  31. #31
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Excel Relationship linking Macro

    kia ora,

    Quote Originally Posted by foxguy View Post
    ...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.

    Quote Originally Posted by foxguy View Post
    ...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 Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  32. #32
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    Hi Foxguy Guru,

    Thank you very much for the message and all your valuable efforts.
    1. I have enclosed the error message in the attachment. It appears in the line Function Highlight_Cells, line 305, col 15 and i could not give you call stack as my system does not have debug enabled, i will work on that option to give more details. The way to duplicate is easiest way is to keep clicking on the cells.

    2. Regarding cells related information in top down list, some where in "P30" cell in the form of Results one column with "Related Items" if i can drag ( if not complex great other it is more than ok to fix a location so that i can create cells accordingly). The down list is all cells names being printed downwards in the coloumn.

    3. My data is currently in Visio, i will start to create those interim data from visio to excel to use your tool tonight. I can easily copy them from this sheet to new sheet if you choose to give me in the easy way you have advised me.

    4. Please advise me the way to disable or if you in your next copy please can you disable the prompts like " double click and macros enable" checks. I have a doubt here, once i create the relationship for viewing, should i have to ensure that the person who is just viewing also has macros turned on.

    Thanks a ton.

    Cheers
    -- Nandu
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  34. #34
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Thumbs up Re: Excel Relationship linking Macro

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

  35. #35
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Excel Relationship linking Macro

    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.

    Quote Originally Posted by foxguy View Post
    ...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.


    Quote Originally Posted by foxguy View Post
    ...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

  36. #36
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  37. #37
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  38. #38
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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

  39. #39
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Scratch this message. Hit Submit too soon.
    Last edited by foxguy; 05-31-2010 at 03:37 PM.

  40. #40
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  41. #41
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    Thank you Foxguy Guru,

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

    Cheers
    -- Nandu

  42. #42
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  43. #43
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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.

  44. #44
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    Correction:
    Quote Originally Posted by learning View Post
    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

  45. #45
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-01-2010 at 01:10 PM.

  46. #46
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  47. #47
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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.

  48. #48
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    Hi Guru Foxguy,

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

    Cheers
    -- Nandu
    Attached Files Attached Files

  49. #49
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  50. #50
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  51. #51
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  52. #52
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-02-2010 at 12:32 PM.

  53. #53
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

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

  54. #54
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Nandu;

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

  55. #55
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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
    Attached Files Attached Files

  56. #56
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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"

  57. #57
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  58. #58
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-03-2010 at 06:19 PM.

  59. #59
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Thumbs up Re: Excel Relationship linking Macro

    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
    Attached Files Attached Files
    Last edited by learning; 06-08-2010 at 07:14 AM. Reason: missing attachment

  60. #60
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-08-2010 at 12:54 PM.

  61. #61
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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
    Attached Files Attached Files

  62. #62
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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
    Last edited by foxguy; 06-18-2010 at 02:05 PM.

  63. #63
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  64. #64
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Hi Nandu;

    Quote Originally Posted by learning View Post
    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.

  65. #65
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Hi Nandu;

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

  66. #66
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  67. #67
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  68. #68
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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
    Attached Images Attached Images

  69. #69
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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)?
    Last edited by foxguy; 06-22-2010 at 09:58 PM.

  70. #70
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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
    Last edited by shg; 06-23-2010 at 10:14 AM.

  71. #71
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  72. #72
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-23-2010 at 01:51 PM.

  73. #73
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  74. #74
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  75. #75
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Relationship linking Macro

    Or use the serial number of the C drive.

  76. #76
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Quote Originally Posted by shg View Post
    Or use the serial number of the C drive.
    Nice idea, never thought of that.

  77. #77
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Relationship linking Macro

    Need code?

  78. #78
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    Quote Originally Posted by shg View Post
    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.

  79. #79
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Relationship linking Macro

    Nope, same.

  80. #80
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  81. #81
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  82. #82
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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?

  83. #83
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Relationship linking Macro

    MsgBox DriveSerialNumber("C")
    Please Login or Register  to view this content.

  84. #84
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  85. #85
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.
    Last edited by foxguy; 06-24-2010 at 12:24 PM.

  86. #86
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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

  87. #87
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

  88. #88
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Excel Relationship linking Macro

    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.

  89. #89
    Registered User
    Join Date
    09-17-2009
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Excel Relationship linking Macro

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1