Web Development

Ask something
Discussion Locked

Excel combo box

0 helpful
4 replies

by admin

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need urgent help in excel using vba. I was not satisfied with the data validation so i made my own. I made a combbox that is visible in a specific column only.Unfortunately, the user can double click the cell and type in values that are not in the combobox. How do i go around that ?How can i make that range/column cells uneditable ?

4 replies

by wayne m.

If you want to restrict the user to a selection from a list, use the data validation feature in Excel. Rather than describe it here, I'll point you to the appropriate Excel Help File.

From the Help Search box, enter "data validate list". From the results, select "Specify the valid entries for cells". From the next list, select "Restrict cell entries to the data from a list".

This should provide what you want without resorting to any direct coding.

by admin

Thanks for the quick reply. But unfortunately, Data Validation list doesnt not provide me with searching like the combobox which has MatchEntry. I have 200 items in the list and if i use datavalidation i have to scroll a long way down for some items.

by admin

There has been progress on the combobox issue. I managed to use the data validation implementing the Customer Validation criteria and then placing the combo box on top. But now it seems the vba code cannot allow me to paste anything on the worksheet. How do u set focus on a combobox or highlighting it ?Here is my code :

Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Text
End Sub

Private Sub ComboBox1_Click()
ActiveCell.Value = ComboBox1.Text
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ' detect the enter key and move down :: ie. y - co-ordinates
ActiveCell.Value = ComboBox1.Value
ActiveCell.Offset(1, 0).Activate
ElseIf KeyCode = 9 And Shift = 0 Then ' detect the tab key and move right :: x - co-ordinates
ActiveCell.Value = ComboBox1.Value
ActiveCell.Offset(0, 1).Activate
ElseIf KeyCode = 27 Then
ActiveCell.Value = ""
ComboBox1.Visible = False
End If

End Sub
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift = 1 Then ' detect if the shift key is clicked :: 1 = true
If KeyCode = 9 Then ' detect if the tab key is clicked
ActiveCell.Offset(0, -1).Activate ' moves the active cell to the left
ComboBox1.Visible = False ' hides the combo box
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("SIZE")) Is Nothing Then
ComboBox1.Visible = True
ComboBox1.Left = Target.Left
ComboBox1.Top = Target.Top
ComboBox1.Width = Target.Width
Target.Font.Name = "Arial"
Else
ComboBox1.Visible = False
End If
End Sub

This discussion is closed to new replies.