Solved Create Function for all comboboxes to lock them for typing new values (1 Viewer)

[tihmir]

hi all, I need to create function to lock them for typing new values.
I have that code On Key Down event, but how to build that as function

Private Sub ComboBoxName_KeyDown(KeyCode As Integer, Shift As Integer)
 Select Case KeyCode
 Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
  'Do nothing
 Case Else
  KeyCode = 0
  MsgBox "You must select a value from the dropdown!" & vbNewLine & vbNewLine & "       You cannot enter new data here!"
 End Select
End Sub

And then on load form to call that function, which should work for all comboboxes on that form.
Is that possible?

 

[jdraw]

Review Combobox.AllowValueListEdits

Would seem (untested) you could set it to False for your combos.

 

[arnelgp]

you can set the Form's AllowEdits to No on existing records and Yes on New Record
or explicitly Lock the control on the same conditions as the above.

 

[MajP]

You can set the "Limit to List" property to yes forcing you to type or enter one of the values. However this does not do it the way you want because it notifies you after the fact. If you wanted to do it your way with a single event handling function it cannot be done because you need to pass the arguments for key code and shift.

You need to make a class
Class RestrictedCombo

Private WithEvents m_RestrictedCombo As ComboBox
Public Sub Initialize(TheCombo As ComboBox)
  TheCombo.OnKeyDown = "[Event Procedure]"
  Set Me.RestrictedCombo = TheCombo
  'MsgBox Me.RestrictedCombo.Name
End Sub
Public Property Get RestrictedCombo() As ComboBox
    Set RestrictedCombo = m_RestrictedCombo
End Property
Public Property Set RestrictedCombo(ByVal objNewValue As ComboBox)
    Set m_RestrictedCombo = objNewValue
End Property
Private Sub m_RestrictedCombo_KeyDown(KeyCode As Integer, Shift As Integer)
 Select Case KeyCode
 Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
  'Do nothing
 Case Else
  KeyCode = 0
  MsgBox "You must select a value from the dropdown!" & vbNewLine & vbNewLine & "       You cannot enter new data here!"
 End Select
End Sub

In the form you need

Public RC As RestrictedCombo
Public Function RestrictCombo()
  Set RC = New RestrictedCombo
  RC.Initialize Me.ActiveControl
End Function

Then in the each combos onEnter property you need
=RestrictCombo()

However, limit to list is probably all you really need.

 

[tihmir]

Thanks everyone for your help!

 

[MajP]

Here is an easier way to do it.

'Set the forms KeyPreview property to Yes and tag all controls to restrict to "Restrict"

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  If Me.ActiveControl.Tag = "restrict" Then
   Select Case KeyCode
   Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
    'Do nothing
   Case Else
    KeyCode = 0
    MsgBox "You must select a value from the dropdown!" & vbNewLine & vbNewLine & "       You cannot enter new data here!"
   End Select
  End If
End Sub