macro to pop up with drop down options

I have code written in vba that will prompt the user to enter additonal information if they type in a certain cell

Please Login or Register  to view this content.

and this in a cell that calls that function

Please Login or Register  to view this content.

so basically, if they type anything in C51 is will ask them for the dispostion.
the problem is that I need it to be a drop down menu, not an input box, so they can only select a set number of responses, not just whatever they would like to type.

I know how to make a drop down menu in a cell, but I don't know how to get it to pop up like the input box.

Hi davidmajorwhite,

I think a UserForm with either a ComboBox or a ListBox may be a better solution. The choices in either one can be populated by either a list from a Worksheet or by code. See the attached sample workbook which implements both, and the complete code that follows:

UserForm1 Code:

Please Login or Register  to view this content.

Ordinary Module Code (e.g. Module1):

Please Login or Register  to view this content.

I hope this helps, rather than confusing the issue.

Lewis

I'm sure what you wrote there could be adjusted to fit what I need but I'm not sure how to do it. I don't really want a button just the

Please Login or Register  to view this content.

to trigger the pop up. The list of possible entries I have in AT1:AT17.

Your formula didn't work for me in Excel 2003. I had to change iftext to isText.

Please Login or Register  to view this content.

I modified my code to generate a User Defined Function named 'askforresult()'. I needed two global variables, because UserForms and Spreadsheets have a hard time communicating with each other:
a. bUserForm1IsActive - boolean variable to indicate when the UserForm is open
b. sUserFormDisposition - string variable that saves the disposition returned from the UserForm.

Lewis

See the attached file and complete modified code below:
UserForm1 code:

Please Login or Register  to view this content.

Ordinary module code (e.g. Module1):

Please Login or Register  to view this content.

thanks, is there anyway for it to be more simple than that, though? I'd like it to pop up with just one drop down.

I'd like it to pop up with just one drop down.

If I understand the question correctly, you want the UserForm to only have one selection box. There are 3 in the UserForm for demo purposes only:
a. ComboBox that gets loaded using a range of Worksheet Cells
b. Combobox that gets loaded using code
c. ListBox that gets loaded using a range of Worksheet Cells (can also be loaded using code).

The idea was for you to see the choices available, and to decide which if any you wanted to use.

Lewis

I'd like to go with the first option, It wont work for me as is, so I assume I need to delete a lot of it to only utilize combo box 1?

To use ComboBox1 ONLY, delete the lines in red below from my previous post. I'm doing the 'red thing' instead of the corrected code, so you can learn to do it yourself next time.

UserForm1 Code:

Please Login or Register  to view this content.

Ordinary Module Code (e.g. Module1) - no changes required.

Lewis