excel sheet with control combobox

[Jorvis]

Hi, I have some control combobox code that I am experimenting with in excel:

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Dogs"
MsgBox "Dogs"
Case "Cats"
MsgBox "Cat"
Case "Mice"
MsgBox "Mice"
Case Else
End Select
End Sub

Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Dogs"
ComboBox1.AddItem "Cats"
ComboBox1.AddItem "Mice"
ComboBox1.Text = ComboBox1.List(0)
End Sub

When I link the output to a cell, it returns the word value, how can I get it to return a number from 1-3, instead of the word values? Thanks!

 

[Jorvis]

If I have asked a stupid or innapropriate question, I wish someone would just tell me, instead of ignoring me. Believe me, I would not ask if I could find it myself.

I know I can get a numerical return from the form controls, but I like the activex versatility better. I have seen this question answered before, but after days/weeks of searching, I just can't find it.

I want to get the listbox to return a number to the linked cell, instead of text, so I can do simple math with the result. Thanks.

 

[Jorvis]

I now have a working code for returning a numerical value.

My new problem occurs when I try to add a combobox2. I get the error "Private Sub Worksheet_Activate() name is ambiguous", when I try and reuse the code after changing all the references to reflect combobox2.


Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Dogs"
Worksheets(1).Range("A1").Value = 1
Case "Cats"
Worksheets(1).Range("A2").Value = 1
Case "Mice"
Worksheets(1).Range("A3").Value = 1
Case Else
End Select
End Sub

Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Select Pet"
ComboBox1.AddItem "Dogs"
ComboBox1.AddItem "Cats"
ComboBox1.AddItem "Mice"
ComboBox1.Text = ComboBox1.List(0)
End Sub

 

[Jorvis]

I can tell by the sheer numbers of unanswered forum questions, that the resource pool is quite low here. Thanks anyway.