How to make this worksheet action work with the CASE statement

[dmcgov]

i have the following routine that works, now i would like to expand it's capabilities to utilize the CASE statement

Please Login or Register  to view this content.

here is what i tried and is not working (but the "Approved" string works, just not the other cases)

Please Login or Register  to view this content.

thoughts?

[Arkadi]

Just use Case "string", not the mathematical operator = which will cause problems:

Please Login or Register  to view this content.

The Is = .... would result in a numerical comparison, which for a string is usually 0

[dmcgov]

thanks arkadi, i fixed the code for that but it is still only working for the string "Approved". Doesn't work for anything else. Should i upload the excel file?

[Arkadi]

The file would be the easiest way to see what is up, yes.

[dmcgov]

Here it is, thanks for helping out.

[Norie]

Why are you using Address here? Don't you want the value of Target?

Please Login or Register  to view this content.

[dmcgov]

i copied that code from an example. are you telling me that just "Target" by itself will give me the cell information?

fyi it returns a fixed address, like $P$15. which is the information that i need (so i can grab row 15 in this example)

[Norie]

In your Select Case are you trying to check the address of Target or the value of Target?

[Arkadi]

Doh big oversight on my part

[dmcgov]

i need the row number from the cell that has changed, in this case to "Approved". Approved is the contents of the cell, but i need the cell address so i can grab just the row, that way i know what to email to the user.

[Arkadi]

Try:

Please Login or Register  to view this content.

Target is the cell (or cells) that changed... Notice it COULD be more than one cell if the user pastes values in multiple rows which may cause you headaches.
Target.Address is the address of the range that changed, .Value will work for a single cell changing.
Target.Row will return the row number.

[protonLeah]

Please Login or Register  to view this content.

[dmcgov]

arkadi,

I figured it out. i had the code in "ThisWorkbook" and not on the Summary sheet. Now everything is working fine. Thanks for your help.

[dmcgov]

oops, didnt figure it out. still not working as expected. i will load up a workbook so that i can show you what is not working.

[dmcgov]

oops, didnt figure it out. still not working as expected. i will load up a workbook so that i can show you what is not working.

[dmcgov]

ok here is what i have (that works as a case statement)

Please Login or Register  to view this content.

note that if i make any changes to the code, then it breaks and will not work at all, even if i undo in the debugger.

here is the code that i want in each case statement:

Please Login or Register  to view this content.

how can i use the code above to fit in to my case statements?

[dmcgov]

so i have tried to not use the case statement and just do an if-then-ElseIf but that is not working for me at all. can anyone help out?

[Arkadi]

Well i must say... The file you said you were adding but ndver did, helped a lot

[dmcgov]

yup, my bad. sorry arkadi for that. here it is. note that i added sheet 1 and 2 to show the case statement in work. the rest is on the summary sheet (only approved works)

thanks again for helping out.

[Arkadi]

I don't understand... the code you provided says "approved" as part of the message, so how can it apply to other case statements? Are you trying to make the code send the same email for each "Case" option, but specifying whether the items is Approved, on Hold, No, or Written?

Your Case code is missing and "End With" for the "With Sendrng" block, but otherwise should work fine.

[dmcgov]

Sorry if i am confusing you. The code on the summary sheet only works with "Approved". I want it to work with "Hold", "No", "Written" as well. i would like to use the case statement as opposed to an if-then statement to do this. the sheet 1 and 2 show that i know how to write a case statement, i just cant get the code from summary to work this way. can this be done?

[Arkadi]

Well... If I understand correctly then this may do the trick.... I noticed Status can only be "Approved" in the validation at the moment, but I did test with "Hold" and the case statement works.

I left in your If statement but commented it out, changes or new lines in red below:

Please Login or Register  to view this content.

[dmcgov]

thanks arkadi, one quick question. should the "StopMacro" be placed just before the "End Select"? i know that i can't just have multiple copies of the same label.

thoughts?

[Arkadi]

Oops... I didnt think that part through... My apologies.
As the code is now, it wouldnt matter since the messagebox wont cause an error im sure. But yes you are 100% correct. Cut the label and the code that goes with it, and place it just before End Select

[dmcgov]

thanks arkadi, that did the trick. now everything is working as it should. the great news about doing it this way is that i can use this change macro on many different types of worksheets. I do appreciate all of your help.

[Arkadi]

Glad I could help Sorry it took me a bit to catch on to the requirements

[dmcgov]

you helped out for sure. no worries. thanks again