Writing a Macro to check cell population & then run another macro.

Hello all, very new to VBA here, and I've been working on this problem for a few weeks now. Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.

How it would work: The person pulling out the inventory would fill out this form and click "Submit"

submitbutton.jpg

I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that. Thank you in advance for any and all help.

Maybe:

Please Login or Register  to view this content.

Assumes the re-order column is A.

Hi John, thanks for the response.

When I place that script into the correct sheet, and try to call on it using the "Call" command when the "Submit" button is pressed, ie.

"Private Sub cmdSubmit_Click()

Call Worksheet_Change"

It gives me an error "Function or Sub not defined". I am guessing Excel is not recognizing this as a macro, since it isn't on my pull down list of Macro's for this project.

No you can't call an event code. I meant for it too be the other way around. Can you post the code that you have?

What part of the code would you like?

The project is 4 sheets, with multiple user forms. Really the portion I'm concerned with though is just ONE column of data and generating a macro to check that when a "submit" button is clicked and e-mail if necessary.

Ok, this is my best shot.

This is the user form the user will fill out to pull product from the shelves.

\1

I then have a separate worksheet which the user never interacts with that lights up quantities drop below a given value.

\1

In that sheet, I placed the code you gave me, not knowing it wouldn't work of course.

\1

So if I had code that worked, I'd use this command to call on that code, which in turn calls the email macro.

\1

I'm sorry I completely misread your thread. The call command would go in your Submit code then.

Please Login or Register  to view this content.

I need to see how your Sub is written for a better interpretation. I'm not sure of how you want too activate the Click Event code. Do you want to push the button and it sends out emails for every "Yes" in Column E? Or are you after something else.

If I can just get it working for just one cell in the column, that's a good enough start to get on everything else.

I have uploaded a streamlined version of the project. There's a cell in sheet 1 with the word "YES" in it. I also created a button that simply runs the email script. Instead of that button running the email script though, I need a script to check that that cell does in fact have a value of "YES" and THEN runs the email script.

https://www.dropbox.com/s/gcs78a2rbv...MacroTest.xlsm

You should be able to "right click save as...." the above file.

I have to go to a meeting now and I'll be able to help you tomorrow, if no one else does:

Please Login or Register  to view this content.

This refers to A1 replace it with the range in your sample and test it. Let me know how it goes.

Awesome! This worked perfectly. Well, sort of.

What I did was create another button labeled "Check Stock", and used that code. This worked.

What I couldn't figure out was how to use the same code in conjunction with the "Submit" button on the userform. This is because I don't know the code to refer to that cell from a form.

You would have to add the sheet name with the Range.

example: Sheets("Sheet1").Range("A1").value

So, it would be

Private Sub cmdSubmit_Click()
If Sheets("Stock").Range("D2").Value = "YES" Then
Call CDO_Mail_Small_TextSK2
End If
End Sub


?

Do you want the macro to Run on Sheets("Stock") ?

The macro is just sending out an email, totally in the background, sort of unbeknownst to the user, it doesn't really run ON a sheet, per say.

Then yes post 12 should do it.