combo box value is not setting

Question

I have a combobox cboDept that when clicked sets its value to 'dept' (which is global). It then makes another combobox cboBldAddress visible and set it to have focus. When executed the cboBldAddress combo never loads. Where's there pilot's error? For brevity i have cut the other Case statements.

Private Sub cboDept_Click()  
    dept = cboDept.Value
    Me.cboBldAddress.Visible = True
    Me.cboBldAddress.SetFocus
End Sub


Private Sub cboBldAddress_GotFocus()  
    Dim sql As String
    Select Case dept
      Case "Administrative"
           ssql = "select address from building where department = 'Administration'"
           cboBldAddress.RowSourceType = "Table/Query"
           cboBldAddress.RowSource = ssql
           Me.cboBldAddress.Requery       
    End Select
End Sub

If you are using `ComboBox` in Exccl `Userform` then it may be `MSforms2` type controls, and does not have `GotFocus` event, If that is the case, may use the enter event or move the code stuff to `cboDept_Click()` May refer [SO post](https://stackoverflow.com/questions/30593980/dynamic-handling-of-gotfocus-event-in-vba) — Ahmed AU, Mar 06 '19 at 00:36

Sergey S. · Accepted Answer · 2019-03-06T05:56:45.300

0

Declaration is incorrect. Declare

Dim ssql As String

And it should work. Enable

Option Explicit

in each module and you will avoid such kind hard-to-find problems

edited Mar 06 '19 at 05:56

answered Mar 06 '19 at 05:37

Sergey S.

6,296
1
14
29

Just checked, your code works fine for me even with the wrong declaration. I believe you need to check the code after adding `Option Explicit`, probably you have the problem in another place with variable names. – Sergey S. Mar 06 '19 at 06:03

combo box value is not setting

1 Answers1