Macro Drop Down Box

[chalky]

I currenly have a macro that runs and ends with an input box popping up asking the user to enter yes or no. It goes as follows:

Range("N53").Value = InputBox("Yes or No")

However, as this is an input box the text is a free field and the yes or no is just a title. To avoid errors on input (ie people pressing Y for yes) i want this input box to be a drop down box, or i only want the user to be able to select the options i offer.

Any thoughs

[Bob Phillips]

Why not use a MsgBox

If MsgBox("Select Yes or No", vbYesNo) = vbYes Then
Range("N53").Value = "Yes"
Else
Range("N53").Value = "No"
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" <[email protected]> wrote in
message news:[email protected]...
>
> I currenly have a macro that runs and ends with an input box popping up
> asking the user to enter yes or no. It goes as follows:
>
> Range("N53").Value = InputBox("Yes or No")
>
> However, as this is an input box the text is a free field and the yes
> or no is just a title. To avoid errors on input (ie people pressing Y
> for yes) i want this input box to be a drop down box, or i only want
> the user to be able to select the options i offer.
>
> Any thoughs
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=501107
>

[Kurt Barr]

One way to do it would be to use a ComboBox instead of a TextBox.

"chalky" wrote:

>
> I currenly have a macro that runs and ends with an input box popping up
> asking the user to enter yes or no. It goes as follows:
>
> Range("N53").Value = InputBox("Yes or No")
>
> However, as this is an input box the text is a free field and the yes
> or no is just a title. To avoid errors on input (ie people pressing Y
> for yes) i want this input box to be a drop down box, or i only want
> the user to be able to select the options i offer.
>
> Any thoughs
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=501107
>
>

[chalky]

The msgbox idea worked perfectly thanks! One think i wanted to add though was further options, ie yes, no, maybe (amongst others).

Any ideas on how this would work? I can't say i am familiar with the combobox.

Thanks for your help so far

chris

[Bob Phillips]

I am afraid that you cannot have Maybe as this technique uses built-in
buttons, but you could use Cancel

Dim ans As Long
ans = MsgBox("Select Yes or No", vbYesNoCancel)
If ans = vbYes Then
Range("N53").Value = "Yes"
ElseIf ans = vbNo Then
Range("N53").Value = "No"
Else
Range("N53").Value = "Maybe"
End If



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" <[email protected]> wrote in
message news:[email protected]...
>
> The msgbox idea worked perfectly thanks! One think i wanted to add
> though was further options, ie yes, no, maybe (amongst others).
>
> Any ideas on how this would work? I can't say i am familiar with the
> combobox.
>
> Thanks for your help so far
>
> chris
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=501107
>

[chalky]

Thanks for your reply, i will look into how this might be incorporated. I am pretty sure it will keep things ticking over for the time being.

Thanks
chris