Pop up Box

[saeed9313]

Here is the task I want to do.

In cell a3 user selects yes or no. Then in cell b3, if they selected yes for a3, a pop up box should appear which asks for a value (1-100), and if they selected no for a3, then b3 automatically becomes zero. How can I do this.

Thanks

[mrice]

Try this macro pasted into the appropriate sheet tab in the VBA editor (Alt F11)

Please Login or Register  to view this content.

[VBA Noob]

Hi mrice,

Any reason why didn't you use Ucase?

VBA Noob

[royUK]

Or Option Compare Text

Please Login or Register  to view this content.

[mrice]

No reason not to use UCASE other than attempting to provide an example of the syntax should any further options need to be added at a later stage.

[saeed9313]

Thanx, I will try both.

[saeed9313]

Martin,

How you actually run macro. I am trying to play it but it is asking me for macro name. I copied it VB module and saved it.

Saeed

[VBA Noob]

It's an event macro and the code goes in the sheet module.
Right click the sheet tab > select view code > paste in the code

Runs when cell A3 equals Yes

VBA Noob

[saeed9313]

Thanks. I was able to run the macro. Now here is the complicated situation. I need to do same operation in various rows in different sheets of same workbook. I tried to duplicate this code but I am getting a message "Duplicate Decleration in Current Scope". Here is my example (R1... is row number and ( ) is the column). So cell A1 wold be "Quest1".

R1 Quest1 (A) Yes/No (B) Yes (C) If yes, % of employees covered (D) 100 (E)
R2 Quest2 (A) Yes/No (B) Yes (C) If yes, % of alignment (D) 85 (E)
R3 Quest3 (A) Yes/No (B) Yes (C) If yes, % of employees covered (D) 10 (E)
R4 Quest4 (A) Yes/No (B) No (C) If yes, % of alignment (D) 0 (E)


In this case I want a number (1-100) under E if C is "Yes" and for "No" it would be zero. Above macro works fine for one row after I change cell addresses but how I can do same thing for multiple rows and in different sheets.

Thanks.

[saeed9313]

Thanks, I used Roys' procedures