Using validation on an ActiveX Control

[duugg]

Hello All,

I have this code here given to me by fumei (thanks again fumei!)




Option Explicit

Private Sub Document_Open()
Dim yadda() As String
Dim j As Long
yadda = Split("California,Colorado,Connecticut", ",")
For j = 0 To UBound(yadda())
ComboBox1.AddItem yadda(j)
Next
End Sub



As this ActiveX Control box currently stands, once I tab to this field, I am able to type any text I desire and then tab to the next field, an ability which I do NOT want.

I would like to know if there is additional code anyone can provide within the existing code above that restricts the data selection to ONLY "California", "Colorado" or "Connecticut".

Can this be done?

Thanks much :yes

[fumei]

One way is like this:
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If ComboBox1.Text <> ComboBox1.List(0) Or _
ComboBox1.Text <> ComboBox1.List(1) Or _
ComboBox1.Text <> ComboBox1.List(2) Then
MsgBox "you typed something."
End If
ComboBox1.ListIndex = 0
End Sub
This essentially means if you type anything, once you release the key - the KeyUp event - the code checks to see what the text value is. If it is NOT one of the listed item, it displays a message, and then makes it the first item. You of course do not have to have the message.

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If ComboBox1.Text <> ComboBox1.List(0) Or _
ComboBox1.Text <> ComboBox1.List(1) Or _
ComboBox1.Text <> ComboBox1.List(2) Then
ComboBox1.ListIndex = 0
End If
End Sub
just makes it the first item (ListIndex = 0).

[fumei]

Strange. I checked the attached file, and it does not work...

But MY file works. Hold on.

[fumei]

Very strange. It works for me here, but the attached thread file does not.

[fumei]

Try changing the procedure that populates to Document_open.

[duugg]

Thanks very much for the response fumei,

I tried copying your new code and pasting it into the code you gave me from the other post and I received this message here...




"Compile Error" "Procedure declaration does not match description or event procedure having the same name".



Would you be able to combine both of your codes together (this new post here and the post from the other thread) so that I don't get this error message?

Also, would you be able to answer my questions from the other post as well? : pray2:

Thanks much :)