InputBox using "Data Validation"
 

Is there any way an InputBox can provide a drop-down list [ie Data
Validation] ?

A range named NumberID [up to six, or possibly a few more rows] should
ideally "drop down" when the InputBox is called.

Thanx,
- Mike

InputBox using "Data Validation"
 

Nope.

But you could create your own userform that does exactly what you want.

If you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html


MikeF wrote:

Is there any way an InputBox can provide a drop-down list [ie Data
Validation] ?

A range named NumberID [up to six, or possibly a few more rows] should
ideally "drop down" when the InputBox is called.

Thanx,
- Mike

--

Dave Peterson

InputBox using "Data Validation"
 

Ok, I created a user form [frmNumberID] with one ComboBox [cboNumberID].
That's it.

Here is the relevant form code:

Private Sub UserForm_Click()
cboNumberID.Enabled = True
End Sub

Private Sub cboShowID_DropButtonClick()
ActiveCell.Value = Me.cboNumberID.Value
End Sub

Here is the code in the procedure that calls it:
*** navigation to a particular cell, then....
frmNumberID.Show
ActiveCell.Value = cboNumberID

....But although it drops the correct value into the cell, when I click the
form's close box it goes to an error.
Debug says it's this line: ActiveCell.Value = Me.cboNumberID.Value

What am I missing??

Thanx,
- Mike





"Dave Peterson" wrote:

Nope.

But you could create your own userform that does exactly what you want.

If you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html


MikeF wrote:

Is there any way an InputBox can provide a drop-down list [ie Data
Validation] ?

A range named NumberID [up to six, or possibly a few more rows] should
ideally "drop down" when the InputBox is called.

Thanx,
- Mike

--

Dave Peterson

InputBox using "Data Validation"
 

I bet you're unloading the userform when you click the close button (do you have
a dedicated button or are you using the X button in the userform's title bar)?

That means that since the userform is unloaded from memory, there isn't any
userform for VBA to find--and no userform means no combobox named cboNumberID
that exists anymore.

Since you're putting the value in the activecell in teh _dropbuttonclick event,
you could just remove that line of code that's causing the trouble.

=====

As a user, I think I would rather see a dedicated "ok" button that does the real
work. Then I would choose the value from the combobox(es) and then hit the ok
button. If I made a mistake, I wouldn't have to worry about the worksheet
getting bad data.

Option Explicit
Private Sub CommandButton1_Click()
If Me.cboNumberID.ListIndex = -1 Then
'nothing in the combobox
'so don't do anything
Else
ActiveCell.Value = Me.cboNumberID.Value
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.cboNumberID
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With

With Me.cboShowID
.AddItem "1"
.AddItem "2"
.AddItem "3"
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
End With
End Sub


MikeF wrote:

Ok, I created a user form [frmNumberID] with one ComboBox [cboNumberID].
That's it.

Here is the relevant form code:

Private Sub UserForm_Click()
cboNumberID.Enabled = True
End Sub

Private Sub cboShowID_DropButtonClick()
ActiveCell.Value = Me.cboNumberID.Value
End Sub

Here is the code in the procedure that calls it:
*** navigation to a particular cell, then....
frmNumberID.Show
ActiveCell.Value = cboNumberID

...But although it drops the correct value into the cell, when I click the
form's close box it goes to an error.
Debug says it's this line: ActiveCell.Value = Me.cboNumberID.Value

What am I missing??

Thanx,
- Mike

"Dave Peterson" wrote:

Nope.

But you could create your own userform that does exactly what you want.

If you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html


MikeF wrote:

Is there any way an InputBox can provide a drop-down list [ie Data
Validation] ?

A range named NumberID [up to six, or possibly a few more rows] should
ideally "drop down" when the InputBox is called.

Thanx,
- Mike

--

Dave Peterson


--

Dave Peterson