NEW REQUEST - Run Last Button Clicked

[swordswinger710]

PLEASE SEE POST #18 FOR UPDATED REQUEST SUMMARY!

I have a situation where clicking buttons determines what data gets entered into Cell B7. The data is collected from one to three other cells on the page, depending on which button was used, and this all works great. An issue arises, however, when there is already data in the B7 cell and someone manually changes the data in one of the three data cells. If they forget to click the appropriate button after that, then the B7 cell displays incorrect data, which is still based on the previous data cell entries.

My question is this – how would I set this up so that when data in one or more of the three data cells is changed and the B7 cell already contains data (from someone clicking the appropriate button at an earlier time) the B7 cell would automatically know which cells it previously received data from and adjust its data accordingly?

I’ve attached an example workbook so you can see how this should work, any help is greatly appreciated!

[Winon]

Hello swordswinger710,

Sorry, I have not looked at your sample Workbook yet, but could you not apply some sort of DataValidation to restrict Cell values?

Please let me know if that does not work, and I shall attempt to assist further.

Regards.

[swordswinger710]

Hey Winon, it's been a while, how've you been? Thanks for your reply.

I thought about Data Validation but I don't really need there to be restrictions on the cells as they are meant to be changed to whatever the user desires. I just need the check to see if there is already data on the B7 cell, which, if there is, would then update to whatever was changed.

Maybe Data Validation can do that, but I haven't been able to figure that out.

[Winon]

Hi swordswinger710,

Hey Winon, it's been a while, how've you been?

Quite well tank you, until I had a look at your Workbook.LOL

Sorry my friend, but there are no Buttons or any Code to show what you want to accomplish with same.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Kind regards.

[swordswinger710]

Haha, I'm sorry Winon, I thought I did just that!

I didn't include any buttons as I thought this needed to work without buttons, I only mentioned them to clarify how the data is being transferred from the three data cells to the B7 cell in the first place. There should be three examples on the sheet to show what I'm aiming for. I apologize if my original post was not clear enough, but perhaps I'm misunderstanding you?

I'm working on including the buttons for you to hopefully help the situation.

[Winon]

Sorry swordswinger710,

It still remains as clear as mud.

_ You have explained well what you want. We get the general jist
_ On the face of it, it looks fairly easy to give you what you want.
_ But you will appreciate you have your files and stuff in front of you and have a very clear picture of everything.
_ But for someone seeing the project for the first time ( especially if they not too bright like some of us ! ) it is very difficult to get a clear “Picture” of what you have and what you want.

_ It would be very helpful if you can:

_ Give some test data to demo what you typically have. Keep the data to the minimum required to demonstrate all typical scenarios. ( Usually with VBA any code done for you which works on a small amount of data will work on a much larger amount of data with little or no modification. But it makes it a lot easier to work on, debug, and to show clearly in the Thread for others of this Forums' benefit if the data is kept to the minimum. Desensitize the data if necessary, ( Make it up if you like, just make sure you choose the data carefully so that it has typical format and in all other ways representative of real data types . )
_ Hand fill in the results that you want the code to give you based on that particular given test data. (Sometimes it is helpful or easiest to have a “Before” Worksheet and an “After” Worksheet.
The “Before” should show what you have, and the “After” should show what you want the output to look like after running any code we do for you, based on the “Before” )

Kind Regards.

[swordswinger710]

Thanks Winon, I understand now and you were absolutely right. It turns out those buttons add quite a bit of meaning to this case and I've revised the sample workbook and reattached it to the original post. I explained everything on the sheet, but if I missed some important info or anything isn't clearer than mud, please let me know. Thank you again and I apologize for the trouble!

[Winon]

Hello swordswinger710,

Sorry for getting back to you so late.

There are so many variables, in your requirement, that could scare off quite a few guys, I think. I gave it some good thinking and I would like to believe, that I came up with the simplest and quick way to deal with it. I have build in Data Validation with a prohibition of pasting over any of those Data Validation Cells.

Hopefully the attached sample Workbook will satisfy your requirements.

Kind Regards.

[swordswinger710]

Hey Winon, no worries, and thank you, your solution might even work for me but I just checked my master worksheet and it seems those data cells already contain validation for other requirements, so it seems I'm back at square one.

Would there be a VBA equivalent that would function in a similar manner?

[Winon]

I think you will owe me a coke and a Bun, swordswinger710.

The permutations of the task at hand remains HUGE! Why I say that is because of the three option buttons. If a user makes any changes in any of the Cells in the Range B3:B6, how would one guess which one of the three option buttons should take preference?

With that said, I know you will understand the dilemma. I have reworked the Workbook, to the best of my abilities, and hope that you will find the attached sample of same satisfactory.

Regards.

[swordswinger710]

I would be happy to provide you that should we ever be in the same place at the same time!

I did not realize that this would be so daunting a task, I thought perhaps there might be a way to record which button was clicked and then somehow work from that? But I suppose you would know more about this sort of thing than me.

Thank you for that code Winon, I am going to see what I can do with it. If this is our only option, then it might be better to just have the B7 cell clear its data while displaying a message box whenever one of the data cells are changed.

[Winon]

Thank you for the feedback, swordswinger710.

I had another good look at the last Workbook I sent you, and I found it a bit irritating myself. I have made some adjustments, and if you don't mind regarding Cell B7 as a "Message Box", I believe that you will be more acceptable to the revised attached Workbook.

Regards.

[Winon]

O.K. swordswinger710,

Here we go! Have you tried my latest sample Workbook?

In the meantime, I have prepared another one for you with a Message Box, with controls, that if the user does not update the Data as required, after filling out B3:B6, he/she would not be able to continue with any other work on the sheet, until at least one of the Three Buttons have been Clicked. Also test the attached sample Workbook by deleting any or all Data in the range B3:B6, and see what happens.

I would appreciate your feedback on any of my attempts.

Regards.

[swordswinger710]

Hey Winon, thank you, yes I have but I've been busy with some other issues as well which is why I haven't got back to you until now.

The second example seems the most desirable so far, as the third one assumes that a button always needs to be clicked, which isn't the case if the ID isn't right.

Even with the second option though, there remains the problem of the user knowing which button to click after they change a data cell. Don't get me wrong, this is definitely better than it was before, I just wish there was some way that the button which placed the data in B7 could be 'remembered' by Excel and adjust any changes in the data cells accordingly.

[Winon]

Thank you for getting back to me, swordswinger710.

As I have mentioned in Post# 10

The permutations of the task at hand remains HUGE! Why I say that is because of the three option buttons.

The ID plays a prominent Role in the whole issue:

some way that the button which placed the data in B7 could be 'remembered' by Excel and adjust any changes in the data cells accordingly.

If any changes in Range B3:B6 should occur, including the ID, how would the system remember which Format to apply in B7? That is if either B3 = "I" or "IA"

Best regards.

[swordswinger710]

If any changes in Range B3:B6 should occur, including the ID, how would the system remember which Format to apply in B7?

I'm racking my brain here and I'm wondering, what if there was a way, perhaps by use of a hidden cell somewhere, to record which button was clicked last? The button macros work great, right? So what if, when we click the PO button for example, it would enter 'PO' in Cell A1 on Sheet 2? Then if someone came along and changed one of the data cells, our code would check which button was clicked last and just automatically click it for us again? If the ID is no longer acceptable, then B7 could empty without any error message.

Is that crazy or do you think I might be on to something?

[Winon]

It could take some time and workarounds, swordswinger710.

Your suggestion carries some merit, which I have also been considering, but was not sure if you were willing to accept same.

Please leave it with me, and I shall get back to you in a couple of days or so.

In the meantime enjoy a Merry Christmas! LOL

Regards.

[swordswinger710]

Would someone else be willing to help me with this? Winon wanted me to ask.

To summarize this whole thread so far:

I have two sheets in my workbook. The Button Sheet contains three wonderfully functioning buttons which, when clicked, take data from specified cells and display their appropriate information in the B7 cell. The Record Sheet exists primarily to record which button was clicked by displaying its name in cell B3.

What I need help with is adding code which would actually perform the recording of the name of a button that was clicked, and then check this name whenever data is entered into any of the data cells on the Button Sheet. One of two things would then need to happen:

1. If there is no name in cell B3 on the Record Sheet, that would mean that none of the buttons have yet been clicked, and nothing else would occur.
2. If there is a name in cell B3 on the Record Sheet, then the matching button's code would automatically run to update the B7 cell on the Button Sheet.

I've added an updated example workbook to aid with this. The button functions are outlined on the Button Sheet, but to the best of my knowledge they shouldn't matter at all so long as those two steps can be managed.

Thanks in advance for any help!

[Leith Ross]

Hello Swordswinger710 & Winon,

I have added the macro code below to the attached workbook and attached the buttons to it. Let me know if there are any issues.

Please Login or Register  to view this content.

[swordswinger710]

Hey Leith, thanks so much for your post!

Clicking any of the three buttons now records its name to cell B3 on the Record Sheet splendidly! Unfortunately, the button functions are no longer working - clicking them clears the ID cell and nothing is entered into cell B7. Changing any of the data cells on the Button Sheet also has no effect on the B7 cell just yet.

Would you have any clue as to why? And thanks again for pitching in here!

[Leith Ross]

Hello Sowrdswinger710,

Too many distractions today. Here is the revised and test macro and workbook.

Please Login or Register  to view this content.

[swordswinger710]

No worries Leith, as my dear old grandpa used to say, 'The hurrieder I go, the behinder I get.'

So with your latest code, the first two buttons, PO and PN are working again, but the BOTH button now functions like the PO button. Also, changing a value in B3:B6 still doesn't trigger the macro for the last button clicked.

I'm wondering if perhaps my summarized post #18 for this thread is clear enough, as your coding seems to be repeating what the buttons already do very well. Just so we're on the same page and I know whether I've been clear enough - you do know that all I need is coding for the following two functions, correct?

Function 1: Record each button click on the Record Sheet - which you've already figured out, thank you!

Function 2: When a B3:B6 cell on the Button Sheet receives data, check which button was clicked last (by viewing the B3 cell on the Record Sheet) and run the macro for that button automatically, which would technically update cell B7 on the Button Sheet all on its own.

As I mentioned in Post #18, I don't think the button functions are important for this solution, not as long as we can get the right button macro to run automatically when the data cells change, if that is doable? Do let me know if something still isn't clear.

[Leith Ross]

Hello Swordswinger710,

I like your Grandpa's wisdom.

Okay, third time is the charm (I hope). I added a Change event macro to the "Button Sheet" to execute the last button selected whenever cells B3:B6 are changed. The buttons function as they did before.

Module2 Macro Code

Please Login or Register  to view this content.

Button Sheet Change Event Macro Code

Please Login or Register  to view this content.

[Winon]

@ Leith Ross,

I bow to the genius in you. What a fantastic solution!!!

Hello Swordswinger710,

Leith Ross provided you with an outstandingly good solution, however, I believe your Notes on the Sheet might have been misread by Leith, hence you may not be totally chuffed with the results in B7.

I have taken the liberty of "Clarifying" your Notes, and to Leith's chagrin, tweaked the code a little to meet with your exact requirements.(Hopefully)

Please ask Leith not to be too angry with me, and check out the attached revised sample Workbook, originally provided by Leith.

Kind Regards.

[swordswinger710]

Wow guys, that is really getting there! I've done extensive testing and here are my findings:

1. I'm still not sure why Macro2 is doing what PO_PN is supposed to be doing. In fact, it seems to be overwriting it completely, as I'm not getting any of the custom error messages that I had set up in PO_PN. Don't get me wrong, Macro2 can replace my existing PO_PN code if that's the better way to go, I just thought that would be extra work and would cause extra problems since all my buttons were already functioning perfectly.

2. I'm trying to merge this coding to my actual workbook and am running into a few issues - the first being the point I just made with the custom error messages not appearing (PO_PN seems to not be running at all) and the second issue being that I'm not sure how to merge the Worksheet_Change code with my existing Worksheet_Change code.

I am still led to believe that everything would work great if only we could just run the PO_PN code whenever data in one of the data cells is changed using the recorded button name on the second sheet. I could be completely wrong though, as I am definitely not the pro here. I've attached a new sample workbook with the actual page names and cells positioned where they are located in my workbook. I added my current Worksheet_Change code as Worksheet_Change1 and the new one as Worksheet_Change2.

How would I do this merging and have PO_PN run again?

[Winon]

Hi Swordswinger710,

Thank you for the feedback!

The Forum or something else which I cannot identify right now, won't allow me to view you latest Workbook.

So I have decided to delete your Macro PO_NO, and adjusted Leith's version to cater for your needs. Please see the attached revised sample Workbook.

Regards.

[mikerickson]

Rather than putting the value from the source cell in B7, why not have each button put a different formula, like "=G8" in B7. (I haven't looked at your file so G8 is only an example)
That way any user changes to G8 will be immediately reflected in B7.

[swordswinger710]

Thank you so much again! I'm extremely close now! mikerickson, thank you too, that seems like a brilliant way to go about it, I'm just not sure how it would work for all the conditions, but you might be on to something there!

The final bits of code from PO_PN which I can't seem to figure out how to put into your last Macro2 are these:

Please Login or Register  to view this content.

...which adds a ' & -XXX' to the end of the PO value and a '-XXX' to the end of the PN value - I think the small difference between these two may have been overlooked.

And then there's something in this bit:

Please Login or Register  to view this content.

...which displays an error message if the ID type doesn't specify engraving and clears the result cell.

I've added the workbook again in it's nearly complete state, and I hope you can access it. Do you think those two functions could still be added? Then all that's left is for us to merge the two Worksheet_Change events and I'm done! I'm not sure why this site isn't letting you see that though.

[swordswinger710]

See if you can read the Worksheet_Change code in the attached Word document. Seems I can't post it directly on here either.

[mikerickson]

Define a name
Name: KeyCell RefersTo: =Sheet1$D$8

Name: PNValue RefersTo: KeyCell & "_xxx"


Put =PNValue in B7

Pressing one of the command buttons would leave the cell's and the formula alone, but would change the definition of KeyCell.

When I get off work, I'll open your file and make a more directed, less generic, suggestion.

[mikerickson]

Regarding the BOTH button:

If the user presses the PN button and then the PO button, its not clear if you want the PO to be shown or you want Both to be shown.
The attachment will show the PO button in the case above, the Both button must be pressed if you want both to be shown.
If you want the buttons linked (i.e. pressing PO then PN is the same as pressing BOTH) then use the sections of code after the Exit Sub lines in the button routines, marked "code for buttons linked"
I also added a Clear button.

[swordswinger710]

WOW. That seems to be the way to go! I love the fact that the buttons are highlighted after they've been clicked - that is an extremely useful function in this case which I had not previously even thought of.

Regarding your question - you had it right. Each button enters precisely what its name implies, with only the BOTH button entering both results. The code for the other case you thought of won't be required then, and my apologies where that was not clear enough.

The CLEAR button is a nice feature, but I don't think it would be used (and I also don't have room for it on my actual worksheet).

This should work for me though, as long as I can still have my error messages and other functionality that my original button code contained. What are your thoughts about integrating these last points with your method?

1. Are G2 and K3 on the Entry Page required? If so, would they be able to go on the Value Sheet instead, beneath the G3 cell possibly? I need those two cells on the Entry Page for other purposes. Or are none of these three cells required with this method?

2. I like the way the buttons disappear, but C15 should also be cleared if C14 ever receives data that does not require a button click. Values like 'IA & III' or 'I & IV' should still be allowed though. I guess I should really have a confirmation message popping up if a user tries to manually enter data into C15 in those cases, asking them if they are sure they want to add data to C15.

3. If a user tries to click a button when there is an empty cell C3, C4, C8 or C14, an error message should appear and no data should be added to cell C15. If one or more of the four data cells are cleared for whatever reason when C15 already contains data, C15 needs to clear and a button click should be required again.

4. Using the BOTH button should combine the two values with a ' & ' instead of a '-'. Also, having 'IA' in the C14 cell needs to add precisely this: ' & -XXX' to the end of just the PO value, '-XXX' to the end of just the PN value, and if the BOTH button is used, then only one '-XXX' following the combined value should be displayed. For example: with 'IA' in C14 and the other values as in our last example, PN should display '123456-XXX' (this is currently correct), PO should display 'PO629999-18 & -XXX', and BOTH should display 'PO629999-18 & 123456-XXX'.

That's all that is missing, as far as I can tell. Do you think those things could be integrated with your method still intact? I'm looking forward to your wisdom and thanks so much again!

[mikerickson]

I've cleaned things up a bit.
If you look at the name manager, you'll see five names, rawFormula, rawPN, rawPO, rawSupliment, rawToggle, that refer to individual cells.
If you ever want to move C15, C8, C3, C8, C14 or C4, you can change these definitions and the routine will follow.

More to the point, the names PN_display, PO_display and Both_display are named formulas that refer to those.

Currently
Name Both_display refers to ="PO"&RIGHT(rawPO,6)&"-"&rawSupliment&" & "&rawPN&Suffix

If, at some future time, you want to change the in between & to "and", you could change the definition to ="PO"&RIGHT(rawPO,6)&"-"&rawSupliment&" and "&rawPN&Suffix and the no change to the VBA is needed.

ALSO, there is the name Buttons_Clicked which will return the button that is clicked, i.e. PN, PO, Both or none.
Values!G3 contains the formula =Buttons_Clicked. You can do what you want with this and you can use that named value as you want.

Finally, Most of the coding that the previous workbook had was to get Buttons to behave like CheckBoxes. Since you've clarified the relationship between PN, PO and Both, I replaced the Buttons with option buttons, since that's exactly what they act like. And its made the workbook a ton more stable.

[swordswinger710]

I did not know you could do that with the Name Manager! That is cool! I was able to tweak that according to my needs really well. The last few things I haven't been able to figure out yet on my own consisted mostly of the message boxes:

1. Since we're using options instead of buttons now, I think it would be best if they didn't disappear at all. A custom confirmation message in case someone clicks a button when 'I' or 'IA' aren't present should appear instead.

2. When I click an option button and all the data is present and C14 contains an 'I' or 'IA', there shouldn't be a message box since there is nothing wrong.

3. When C15 already contains data and I change C14 to no longer contain an 'I' or 'IA', there shouldn't be a message box since C15 must be cleared anyways.

4. When something other than 'I' or 'IA' are in C14 and the three other data cells contain data, and I manually try to enter data in C15, I get a confirmation message, which is great, but if I click 'No' then another message pops up and I can keep clicking 'No' until the cows come home. 'No' should preferably just cancel the last entry.

5. C15 is currently being cleared when C14 ever receives data that does not require a button click, which is as it should be (the confirmation message should still be avoided as mentioned above in request 3). Values like 'IA & III' or 'I & IV' should still be allowed though. I'm don't know the best way to do this; I tried it myself but screwed everything up. Previously I had all the likely combinations set up like so:

Please Login or Register  to view this content.

6. Lastly, If a user tries to click a button when there is an empty cell C3, C4, C8 or C14, an error message should appear for each case. The way I had it previously was like this:

Please Login or Register  to view this content.

Thanks yet again for this incredibly intelligent manner of coding! I added the workbook as I tweaked it so you can see if I messed anything up.

[mikerickson]

Are C3, C4, and C8 restricted to numerical values?

[mikerickson]

I think this might do what you want.

[swordswinger710]

Thanks mikerickson, I do appreciate that but unfortunately your last example leaves me with more issues than fixes.

Perhaps we should tackle one final area at a time? I took another look at what I really need and I changed/simplified the things that I could. Using my latest attached sample workbook, would you mind taking a look at it and adding only this functionality to it for now:

1. Could we just leave the option buttons alone no matter what happens, so no disappearing and no colour changing?

2. C15 is currently being cleared when C14 ever receives data that does not require a button click, which is as it should be. I still need to allow values like 'IA & III' or 'I & IV' though. These are all the possible combinations:

Please Login or Register  to view this content.

Once we've got that working, then I believe all that will be remaining is some error message functionality. Thank you for your continued help with this, and I hope we can fix these last few things as we are so close to a solution!

[mikerickson]

I don't understand what you mean by "allow values like "IA & III" or "I & IV".

Currently it does allow those values. It also disables the option buttons per "C15 is currently being cleared when C14 ever receives data that does not require a button click

[swordswinger710]

Ah, I wondered if we were not understanding each other with that one.

Values like "IA & III" or "I & IV" in C14 still need to allow C15 to contain the appropriate data as usual, since the first entry contains 'IA' and the second contains 'I'. At the moment I believe this is the bit of code concerned: Case "I", "IA".

Basically, the way 'I' is being treated in C14 should be the way all these cases are treated:
"I", "I & II", "II & I", "I & III", "III & I", "I & IV", "IV & I"

And the way 'IA' is being treated in C14 should be the way all these cases are treated:
"IA", "IA & II", "II & IA", "IA & III", "III & IA", "IA & IV", "IV & IA"

Do let me know if I'm still not making sense and my apologies for any confusion.

[mikerickson]

Perhaps this.

[swordswinger710]

Okay! I believe I've got everything working the way I'd like it to now, with one last thing remaining:

When the C14 cell contains a value other than I or IA, I can still click the buttons and add data to C15 manually without any issues. What should be happening instead is I should be getting a confirmation message; if I clicked No, then nothing would be entered into C15, and if I clicked Yes, then the value should be entered.

Would you be able to take a look at my attached workbook and let me know what I'm missing? Thank you!

[mikerickson]

"When the C14 cell contains a value other than I or IA, I can still click the buttons"

\
You should be able to click the buttons, they should be disabled and nothing happens, not even the selection mark.
Hmm...

When the C14 cell contains a value other than I or IA, ...add data to C15 manually ... getting a confirmation message; if I clicked No, then nothing would be entered into C15

I'll look into it. For this purpose does "I & II" etc count as "I"

[swordswinger710]

You should be able to click the buttons, they should be disabled and nothing happens, not even the selection mark.

Do check the attachment, I have been tweaking things as I've been learning how they work, so I probably messed something up. Keep in mind though that everything else is now satisfactory for me.

For this purpose does "I & II" etc count as "I"

Yes it does, basically any value in C14 which does not contain a separate "I" or "IA" must bring up the confirmation message.

[mikerickson]

Your code in the sheet1 module was different than what I posted in #40.
I replaced it and this is the result.

[swordswinger710]

Thanks mikerickson, the reason it was different was because what was posted in Post #36 and #40 broke a lot of things that were working in Post #33.

I'm trying to add just the bit that does what I most recently requested, and it works when the data in C14 doesn't contain an 'I' or an 'IA' as recently discussed, but it also displays the confirmation message every time C14 does contain an 'I' or an 'IA'. How do I have this appear only when C14 does not contain the correct value?

Please Login or Register  to view this content.

[mikerickson]

In the workbook from post #44, the confirmation message does not show if I or IA is entered into C14.
The confirmation message only shows if I or IA (etc) is in C14 and the user enterers something other than I/IA.

[swordswinger710]

Oh I see what going on, there's a bit of a misunderstanding here. I don't need a confirmation message when something other than 'I' or 'IA' is entered into C14 - I only need one when C14 doesn't contain an 'I' or 'IA' and data is entered into C15 either by a button click or manually.

I'll give you some examples:

1. C14 receives 'I' and no confirmation message appears. The PO button is clicked, C15 receives the data and no confirmation message appears.

2. C14 receives 'IA & III' and no confirmation message appears. The PN button is clicked, C15 receives the data and no confirmation message appears.

3. C14 receives 'III' and no confirmation message appears. The BOTH button is clicked, and a confirmation message appears asking the user to confirm entry of C15 data since there is no 'I' or 'IA' in C14. Clicking 'YES' enters the data, clicking 'NO' doesn't.

4. C14 receives 'NA' and no confirmation message appears. The user enters '12345' into C15, and a confirmation message appears asking the user to confirm entry of C15 data since there is no 'I' or 'IA' in C14. Clicking 'YES' enters the data, clicking 'NO' doesn't.

Apologies where I was previously unclear, and I hope this helps clarifies things, do let me know. Thanks again!

[swordswinger710]

Does anybody else have any suggestions for me? I am stuck until I can solve this last issue. Thank you!

[swordswinger710]

So it turns out I need the buttons instead of the options we changed to, due to the layout of my actual worksheet. I did some more experimenting and testing on my own and have attached one last version to this post.

This is what works:

1. All the buttons enter data correctly, including the IA extensions as needed.
2. C15 clears when one of the data cells has its data removed.
3. The C15 value updates accordingly when the data in C3, C4, or C8 is changed.

This is what doesn't quite work as it should:

1. The buttons enter data into C15 even when one of the data cells are empty or when C14 doesn't contain an I or IA; and no error messages appear.

I'm sure there's redundant code in there as well, but this is as far as I could get. Would anyone be able to take one last look at the attached workbook for me?

[Winon]

Hello swordswinger710,

mikerickson really created awesome! Coding, and I don't want to mess it up.

Please try the attached Workbook now.

Regards.

[swordswinger710]

Thank you Winon! I really appreciate you helping out!

I ran through that one, and after adjusting a few things to meet my needs I am still missing the one thing that is holding me back from completion:

1. I can still enter data in C15 regardless of the C14 value, without any warning. What should happen is when the C14 value does not contain an 'I' or 'IA', data entry in C15 needs to be confirmed via a popup message.

You can check out my adjusted version attached. Would a simple data validation on C15 do the trick?

[Winon]

Hi swordswinger710,

Thank you for the feedback.

In the WorkSheet_Selection_Change Event, unquote the last piece of Code and see if you can make do with that change!

Regards.

[Winon]

O.K. swordswinger710,

How about these changes to your last Workbook?

Regards.

[swordswinger710]

The thing is, users need to be able to select and manually enter data into C15 if they choose to do so, and this should be allowed unless there's no 'I' or 'IA' (for example 'III') in which case a confirmation message should appear.

Is C15 data validation not a good option for us?

[Winon]

Post deleted Messages crossed.

[swordswinger710]

Well, we don't need to use Data Validation, but I thought we could hopefully use some kind of formula which would search the C14 cell for either an 'I' or an 'IA', and if there was a match then button clicks and manual entries would be allowed; whereas if there was no match, a confirmation message would pop up.

Does that sound plausible?

[Winon]

You really know what you want swordswinger710,


Does the attached Workbook work for you?

[swordswinger710]

I'll admit I am trying really hard to communicate what I need, but it looks like I'm still having trouble being clear enough, as that still doesn't work for me.

I'm think it would be best if I marked this thread as solved and start a new one with just my final request. You have been very helpful and I am really grateful to you and the others who popped in and helped me out with this. Thanks to you all I am now much closer to my final goal than I was when I began. I'll add a link to this post once my new thread is up for those interested in following the progress.

Thanks again!

[Winon]

Hello swordswinger710,

I find your statements in Posts #54 and #56 somewhat contradicting'

[swordswinger710]

No worries Winon! I'll be happy to try and explain it one more time. I attached the sample that is the closest I've got to a working solution. Everything in there is perfect - except for this:

Users need to be able to click the buttons, or select and manually enter data into C15, and both these things should always be allowed - unless C14 DOES NOT contain either an 'I' or an 'IA'. ('I' and 'IA' are both indications that C15 requires data, whereas if those two cases are NOT present, C15 data is NOT USUALLY required.)

In the case where C14 contains a 'III' for example, then clicking one of the buttons or manually entering data into C15 should result in a message popping up which would say 'It looks like you want to add data in C15 when C14 doesn't require it, are you sure you want to do this? Yes or No?'

I thought we could hopefully use some kind of formula, validation or otherwise, which would search the C14 cell for either an 'I' or an 'IA' anytime C15 received data through a button click or manual entry, and then either allow data to be entered into C15 or pop the confirmation message, depending on the search results.

Does that explain it a bit better?

PS I've started new thread regarding this last issue here.

[Winon]

Hi Sword,

Please use the Attached sample Workbook.

[swordswinger710]

Thanks again Winon, I'm trying, but I get asked the same question regardless of the entry in C14. When I put 'I' or 'IA' in C14, every button I click asks me if I'm sure. This needs to happen only when those values AREN'T present in C14.

Also, is that the workbook I've most recently uploaded? I thought the rest of the coding seemed to be simplified quite a bit more in mine.