Macro that effectively runs all the time?

[jamer02]

Hey all,

Is it possible to have a macro which effectively runs all the time.

For example, if the user imputs something which is wrong I want the macro to pick it up straight away with a message box.

Is this possible?

Thanks for your help.

[Phil_V]

You can't run a macro "all the time", but depending on what you want to do you can have a macro triggered, when a user changes a value on the worksheet, or selects a different cell, or changes sheets, or, or, or.

There are many things that can be used to trigger the macro.
From the sounds of what you have written you would be looking for the OnChange event, which is triggered when a user changes a value on the worksheet.
However, although this is certainly one solution to the problem, it is worth bearing in mind that there are also various methods you can use to make sure the user enters correct data in the first place, (such as drop down boxes, and data validation).

[jamer02]

Ok well heres a more detailed explanation of what I want:

I need a macro that when a value is entered into the certain cell I want it to check we have this item in stock, so:

Say "230" is enetered into "A1" then I want it to check that the same value is in the "230" sheet.

So yes the macro will be triggered on a value being entered into the cell.

Does this help you help me?

Thanks.

[Phil_V]

Yes, you are wanting the following Event then, right click on the worksheet tab, and select view code, (this will open the VBA Editor in the correct section for that worksheet).
The template is as follows:

Please Login or Register  to view this content.

Target is the range that has just been changed.

[jamer02]

And if this is done in multiple cells then it will work ok?

[Phil_V]

As long as the macro written within the function deals with the ranges correctly then yes

[jamer02]

Cool, thanks a lot, I will get to work on that and post back if I need any further help.

[Phil_V]

Good luck, and be sure to post back if you have any queries once you have a complete idea of what you want to achieve

[jamer02]

I have a quick go at this but think I am completely stuck, come up with this random code:

Please Login or Register  to view this content.

Could you help me please, I can give you more info if needed.

Thanks a lot.

[Phil_V]

More info is definitly needed I am afraid

Ok, so on Sheet1 someone can enter a value.
Will they enter that value anywhere on sheet1 or only in certain locations?

Once they have entered that value, (for example: 230) what exactly do you want to do?
Your macro above seems to be trying to then see if the value of "H13" exists on Sheet2, or is it looking for "230" on sheet2, or is it looking for one of these values on a sheet called "230" ?

I am unclear on exactly what is being checked, and what it is being checked against

[jamer02]

Ok well would you like me to uplod the file on rapidshare later when I get home when I have more time and I will put a detailed description of what I want and what it does for you.

Thanks for your willingness to help so far and in the future.

So if you look at this post this evening about 6pm BST and I should have posted for you.

Thanks

[Phil_V]

Ok, you can upload the file here too. If it is large you might need to zip it first.
When you reply to the post, just click [Go Advanced] and there is an attachment option near the bottom.

[jamer02]

Ok well heres the link for my file:

http://rapidshare.com/files/25807344...K_DEL.xls.html

And heres a more detailed explanation:

As you can see, information is entered into cells A19:G42 but I only really want this macro to focus on columns C, E and G. So lets suggest the following is entered into these rows:

C: 230
E: H13
G: 1

If you look in the other sheets you will understand the input.

If the user inputs 230 diameter in grade H13 and its bar number one but on the "230" sheet there it doesn't exist because the item isn't there then I want a message box to appear to say this.

You see now why I would like it in real time?

Does this help or do you need more explanation.

Thanks a lot

[Phil_V]

NOTE Requested via PM that the OP upload the file as an attachment as I am unable to access the link they posted, but no reply. Treating this as 'Case Closed'.