Excel Cell run code based on value

**gmc2k2** · 02-03-2011, 08:32 AM

Hi, i need a bit of help, im looking to run a bit of code based on if a cell has a value of yes or no, If yes i want and inputbox to popup and then whatever value i type into that input box to show in the next columns cell, if no then to move to the column afters cell, for the yes/no cell im using data validation and this will be a yes/no cell all the way to the bottom of that columns end, so as you can see itll only popup with another input box if the next row down cell yes/no is selected and then an input box will popup asking for the value for the next columns cell if that makes sense?
cheers

**TMS** · 02-03-2011, 09:08 AM

You could do this with a Worksheet Change event monitoring the column with the data vailidation.

However, you need to think through the process. What happens if the Input Box pops up and the user presses cancel/escape? What happens if they just press enter without inputting a value. Would you want to reset the original cell?

You'd be best uploading a sample workbook with some typical data, identifying what you expect and what do when things go wrong ... as they inevitably will ;-)

Regards

**Domski** · 02-03-2011, 09:13 AM

A fairly basic example based on the yes/no's being entered in Column A. There may be other things you need to take into account.

Please Login or Register  to view this content.

Code need to go on the worksheet's code page. Right click on the sheet tab and select View Code.

Dom

**TMS** · 02-03-2011, 09:20 AM

@Domski: do you need an "Exit Sub" after the "Application.Undo".

I generally assume that, if multiple cells are being changed, the user might have selected a range and deleted the entries. It's difficult to cater for all the possibilities ...

Regards

**Domski** · 02-03-2011, 09:49 AM

Originally Posted by TMShucks

@Domski: do you need an "Exit Sub" after the "Application.Undo".

I generally assume that, if multiple cells are being changed, the user might have selected a range and deleted the entries. It's difficult to cater for all the possibilities ...

Regards

Ooops, yes you're correct I missed that.

I agree it can just be multiple deletions but I tend to put the limit to changing one cell in initially for ease and then if it doesn't suit the OP's requirement build further adaptations in later. Just my approach.

Dom

**gmc2k2** · 02-03-2011, 10:29 AM

Thank you very much thats helped greatly and sorted my problem, just one more question, in relation to the above i need the same type of function but instead of a Inputbox i would like a Countif type function, if once cell has a value and another cell in the same row has a value inside it then it would count it, so im guessing it would involve and AND and Countif?
e.g COUTIF B2="Value" and C2=Value.....but only count if they both have yes as the answer otherwise it wouldnt count it if it was just the one?
thank again

**gmc2k2** · 02-03-2011, 10:31 AM

if its possible as ive incorporated some code where i have a button that copies a a sheet and renames it to a month i would like it to only count the value from the month sheet the user selects?

**Domski** · 02-03-2011, 10:49 AM

I have to admit I don't really follow what you are asking in either of your subsequent questions.

Take a breath and try and explain the situation a bit more clearly (maybe with a bit more capitalisation and punctuation

).

Dom

**gmc2k2** · 02-03-2011, 11:18 AM

sorry domski got a little excited trying to explain myself haha, what i mean is i need a function or coding that would count if different cells meet a certain criteria but they have to meet it in the same row...
e.g. i have a column where it could contain lets say apples, oranges and pears
and another column where it would contain a date for when one of the above was bought
what i would like it to do is to count all the apples and as long as the date cell is not empty
and the same again for oranges and pears.
what ill do then is to total each count and multiply it by cost for each product (apples, oranges and pears have seperate prices) and then total all those costs up if that makes sense?

**TMS** · 02-03-2011, 11:28 AM

I think you've got to the point where you *really* need to upload a sample workbook.

Regards

**Domski** · 02-03-2011, 11:47 AM

I agree, it's making my head hurt just trying to read your questions/explanations.

Dom

**gmc2k2** · 02-03-2011, 11:47 AM

there you go

**TMS** · 02-03-2011, 12:44 PM

OK, something like:

=SUMPRODUCT(--(D8:D17="Apples"),--(F8:F17<>"")) & " Apples " & SUMPRODUCT(--(D8:D17="Oranges"),--(F8:F17<>"")) & " Oranges " & SUMPRODUCT(--(D8:D17="Pears"),--(F8:F17<>"")) & " Pears "

Regards

**gmc2k2** · 02-03-2011, 03:54 PM

Epic thank you very much all, its sorted all my problems out

Excel Cell run code based on value

LinkBack

Thread Tools

Rate This Thread

Display

Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Re: Excel Cell run code based on value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions