Need help producing a inputbox on cell change within a range

**DavidBarker** · 10-24-2014, 11:12 PM

Hi all - apologies if this is anything similar that has come up before but ive been trolling these forums looking for an example to change that may work with what i want to no avail.

Hence ive conceded that my VBA just isnt at the level my mind wants it to be - hence ive come to the experts.

what i would like to achieve is this

i have a range of data A12:N1000

Data in each cell from column I is selected from a drop down list

when a cell in column I is set to "Won", i would like the have an Inputbox pop up to enter a date, and that date is then placed in same row, column N as the cell selected as "won".

any help is much appreciated.

**alansidman** · 10-24-2014, 11:24 PM

Try this:

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

**MarvinP** · 10-24-2014, 11:27 PM

Hi David and welcome to the forum.

Put this code behind the sheet (not in a module) that you want it to work on..

Please Login or Register  to view this content.

**DavidBarker** · 10-25-2014, 06:36 PM

thanks Alan,

the script kind of worked, but it just ran and kept popping up on random spots on the spreadsheet.

plus i don't want to have to 'run' it as such.

id like it to be activated by change cell to "won" and only that same row has the date entered.

**DavidBarker** · 10-25-2014, 06:46 PM

Marvin,

yes this seems very close but didnt work. changing cell in col I didnt have any effect.

Please see below file. this may be easier.

basically its an estimation register, when we win a project, i want a prompt to enter the win date (otherwise it never gets filled out).

Estimating Register 2014.xlsm

thanks in advance

**alansidman** · 10-25-2014, 06:46 PM

If you are looking for it to happen when you have a change event, then you should look at Marvin's solution. I interpreted your needs differently from your original description.

**MarvinP** · 10-25-2014, 09:10 PM

Hi David,

I tested on "Won" and you are looking for "WON". Try this new code.

**alansidman** · 10-25-2014, 09:17 PM

if you add the term Option Compare Text before you write any code, then it will not be an issue as to the case of the criteria.

http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx