Code Needed To Link Values Among Cells

[swordswinger710]

I have four cells on a worksheet which are related to each other, and I'm attempting to add some rules to keep them properly connected depending on what users may enter into one of the cells. I should mention that I have the complete rules posted here, but the fellow who was helping me out there has not been able to continue doing so due to his busy life, so I am trying to get this figured out one cell at a time.

Cell D1 is the main cell which controls all the others. This is how I need to get it set up:

‘D1’ CELL RULES:

1. The D1 cell may never contain a dash. An error message must appear to prevent this from being entered if anyone ever tries to do so. Anything else may be entered.

2. When the D1 cell receives data, this is what must happen with the A1 cell:

The A1 cell must receive the value from the D1 cell followed by a ‘-1’ - UNLESS the A1 cell ALREADY CONTAINS a ‘-X’ value where ‘X’ is a number LARGER THAN 1. If the A1 cell does contain a ‘-’ followed by a number greater than 1, an error message must appear and the D1 cell may not receive the data.

If the B1 and C1 cells ALREADY contain data, the values preceding their ‘-X’s must be changed to match the data just entered in the D1 cell as well.

EXAMPLE: D1 cell receives 12345. If the A1 cell contains 54321, then the A1 cell changes to 12345-1. If the A1 cell contains 54321-2, an error message appears to prevent 12345 from being entered into the D1 cell. If the B1 cell contains 54321-2 and the D1 cell receives 12345, the B1 cell updates to 12345-2.

3. When the D1 cell is cleared, then the '-1' must be removed from the value in the A1 cell, and any data in the B1 and C1 cells must be completely cleared.

EXAMPLE: D1 cell contained 12345 and was cleared. If the A1 cell contains 12345-1, then the A1 cell changes to 12345. If the B1 cell contains 12345-2 and the C1 cell contains 12345-3, they both clear.

Please see the attached workbook for more examples and clarity. Thanks so much for any help!

[Leith Ross]

Hello swordswinger710,

Isn't this basically the same post as the previous one in the Excel Forum? I did answer your question there after Winon tried helping you. Have you reviewed that post yet?

Previous post NEW REQUEST run last button clicked

[swordswinger710]

Hey Leith Ross! Thank you for that, but no, this is something different entirely which has nothing to do with button clicks and that sort of thing. This and the other post you referenced are the last two issues I'm struggling with in my workbook. I replied to your post there, thank you for your help!

[jaslake]

Hi swordswinger

What does your actual Data File look like, including headers? Please give a half dozen examples...Sheet 1 before...Sheet2 after...perhaps then I can follow.

[swordswinger710]

Hey jaslake, thanks so much for looking into this!

My actual data file is not as tidy as my sample file and the four cells involved are all over the place, which is why I thought I was doing so well at explaining myself with that example workbook. :P I've tried to do better this time, with 5 examples of before and after and using bold text to highlight the important parts of each example. I also included the rules for the other cells so you can possibly get the whole picture better, but as I mentioned, cell D1 is the main one and I'd like to tackle that one first.

I hope the attached file is much clearer now, but please let me know if something still doesn't make sense; and thanks again for your assistance!

[jaslake]

Hi swordswinger

You have a LOT of mental gymnastics going on here...it'll take a bit of time to sort it out. Get back to you.

[jaslake]

Hi swordswinger

Chances of this being what you require are slim...try it...let me know.

This Code is in the Worksheet Module

Please Login or Register  to view this content.

[swordswinger710]

Nailed it! That is seriously awesome work dude, thank you so much! Cell D1 is done.

Do you have suggestions on how I could add the rules for the other three cells? I was hoping I would be able to use the D1 code to figure out the rest of them but I'm having some trouble with knowing where to add it.

And out of curiosity, what does the Enable_Right_Click code do exactly? Would it be required once I put this into my actual workbook?

[jaslake]

Hi swordswinger

Will the user be permitted to make manual entries in A1, B1 and C1?

Regarding this, Module2 should be removed from the Workbook...it's detritus from a previous project.

And out of curiosity, what does the Enable_Right_Click code do exactly? Would it be required once I put this into my actual workbook?

[swordswinger710]

Yes they will, which is why they all will require their own rules as summarized on the example worksheet. Cell D1 is a sort of 'master' cell and the others are not as intense rule-wise.

Thanks for letting me know about that Right_Click section, it had me scratching my head a bit. :P

[jaslake]

Hi swordswinger

Regarding Cell A1...Does "not present" mean "empty"???

*> '-1' value is only permitted when D1 data is not present

[swordswinger710]

Hey again, yes that's exactly what it means. For example, if A1 receives 12345-2 and D1 contains 12345, then an error message should pop up and the data not entered. If D1 is empty though, then it's fine.

In case you missed the link in the first post, there is a more detailed description of the complete rule set here.

Thanks a million again jaslake, you are a lifesaver.

[jaslake]

Hi swordswinger

Included in the Code in the attached is Code for Cell D1 and A1 (not B1 or C1)...please test it.

Please Login or Register  to view this content.

[swordswinger710]

Hey and thanks again for your continued help with this! Here's what I'm finding:

The first rule isn't working just yet - when a value with a '-1' is entered, the value preceding the '-1' needs to be put into D1.

The second and third rules are working but need an error message before reverting back to their original value.

[jaslake]

Hi swordswinger

You've lost me...I've written Code for only Cell D1 and A1. I'm attaching two Files...

1. 1 Rule D Cell Example v1.xlsm...This File only applies to Cell D1
2. 2 Rules A and D Cell Example v1.2.xlsm...This File will apply only to Cells D1 and A1

Test the first File (for Cell D1 ONLY)...does it work...you previously indicated it does...

Nailed it! That is seriously awesome work dude, thank you so much! Cell D1 is done.

Test the second File for Cells D1 and A1...do both Cells work?

Code for B1 and C1 is NOT YET WRITTEN.

[swordswinger710]

Oh my goodness, I was completely unclear there, my sincere apologies. The three rules I referred to in my last post were the three rules for the A1 cell, nothing more. The D1 cell rules are still as good as ever. Let me try wording that properly:

1. '-1' value gives D1 preceding data' This isn't working just yet - when a value with a '-1' is entered, the value preceding the '-1' needs to be put into D1.

2. '> '-1' value is only permitted when D1 data is not present' and 'value without '-X' only permitted when D1 is empty' are working but I would need an error message before they revert back to their original value.

[snb]

For cell D1 I'd suggest:

Please Login or Register  to view this content.

[jaslake]

Hi swordswinger

Try the Code in the attached. It has Rules for Columns A thru D.

[swordswinger710]

Thanks snb, that seems like a nice clean code, but I'm having trouble getting all the cell rules to work properly.

Thank you jaslake, everything is tested and tried and this is all that's left!

1. I need to be able to delete the value in C1 without any messages.

2. I need to be able to delete the value in B1, and when I do so, any value in C1 should also clear, again without any error messages.

3. When data is entered into one of the four cells and I either click or use a keystroke to enter the value, the cell that just received the data is selected again, which could get bothersome on a page where these are a few of many cells that require data entry. Would that be changeable?

I've attached the workbook again since I've updated all the error messages with custom text. Thank you again, we're nearly done!

[jaslake]

Hi swordswinger

Where would you like the Cursor to go?

3. When data is entered into one of the four cells and I either click or use a keystroke to enter the value, the cell that just received the data is selected again, which could get bothersome on a page where these are a few of many cells that require data entry

[swordswinger710]

Hi jaslake,

Instead of having the same cell selected after the data entry, the next clicked-on or key-stroked cell should be selected instead, like cells normally work. For example, if I enter data into Cell A1 and hit Enter, A2 is now selected and A1 has the new data. Or if I enter data into Cell A1 and then click on Cell A2, A2 is now selected and A1 has the new data.

[jaslake]

Hi swordswinger

Won't guarantee I got them all.

This appears to be "fixed"...

I need to be able to delete the value in C1 without any messages.

and this...

I need to be able to delete the value in B1, and when I do so, any value in C1 should also clear, again without any error messages.

I may have missed some of these...you do the testing and let me know...you're going to need to maintain this Code...here's the line of Code you require to fix this...

Please Login or Register  to view this content.

Instead of having the same cell selected after the data entry, the next clicked-on or key-stroked cell should be selected instead

[swordswinger710]

Awesome, everything seems good now except for the cell selection issue.

That line of code you gave me works well for when the Tab key is used, but using the Enter key doesn't work since the cell to the right is now selected, and Enter would normally select the one below. Clicking a cell further away after data entry doesn't keep that clicked cell selected either. Is there no way to keep the selecting of cells functioning normally?

[jaslake]

Hi swordswinger

Changing the Code from this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

will cause the Cursor to move down.

[swordswinger710]

Yes, I was able to figure that out also, but then if Tab or a click elsewhere is used, that doesn't work again.

There's got to be something that can be done with selecting the next cell as usual, isn't there? After each of the four cells receive data, there must currently be something that is telling them to select themselves again, I'm just having trouble finding what exactly.

[jaslake]

Hi swordswinger

This behavior is being caused by the indicated line of Code...

Please Login or Register  to view this content.

There is a work around but I'm not certain you'll be happy with it...it'll take a bit...get back to you.

[jaslake]

Hi swordswinger

I've added Selection Change Code to eliminate the need to Undo...it works...you must select Cells A1 or B1 or C1 or D1 in order for the Code to store OldValue.

Please Login or Register  to view this content.

[swordswinger710]

Hey jaslake, that is working much better, thank you! One question regarding the new coding method - I noticed that when the first rule for the D1 cell kicks in, the D1 cell is selected again. This is actually okay though, I think that it makes more sense to select the D1 cell again in that case so that the user can try another entry.

Is there a fairly painless way to have the cell in question be selected again after an error message appears for it? I tried to edit the code but I'm not quite sure what I'm doing. Basically, after each error message, the cell should be selected again, but if there's no error then it should just work as it does now.

What do you think?

[jaslake]

Hi swordswinger

After each Error Message, if it does not already exist, add the indicated Line of Code...

Please Login or Register  to view this content.

[swordswinger710]

Hey jaslaske, thanks, that seemed to do the trick for the messages.

I'm now trying to get everything transferred over to my actual workbook. The four cell locations all have to change, so I went through the code and updated all the numbers as best I could, but I'm still getting some run-time errors. Could you take a look at the attached workbook to see what I did wrong? I'd really appreciate it!

[jaslake]

Hi swordswinger

Is your most recent attachment of the SAME STRUCTURE as your Actual File?

[swordswinger710]

Yes it is, don't ask my why I didn't arrange it that way to begin with. I guess I thought it would be easier for you to understand, my apologies.

[jaslake]

Hi swordswinger

I won't guarantee all the kinks are worked out...you do the testing.

[swordswinger710]

All the kinks are gone! Dude, that is amazing, thank you!

One last question for you - is there one quick and easy way to merge two Worksheet_Change codes into one, or is each case different? My actual file already contains a Worksheet_Change code on that sheet and I have no clue as how to add this new one to it.

My existing code can be found in the attachment, for some reason I can't post it here as I get some html error or something.

[jaslake]

Hi swordswinger

I'm unable to test the combined Code without your File.

The combined Code does not Compile as there is a Function/Procedure missing. I assume it exists in your Actual File.

Please Login or Register  to view this content.

Here's the entire Combined Code...

Please Login or Register  to view this content.

[swordswinger710]

Got it!

As far as I can tell, everything is working now! Thank you so very much for your help, and even more for your patience!

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.