CODEWITHSUNDEEP
vbaexcel
rohrl77
rohrl77

Reputation: 3337

How to prevent dropdown from executing when source list is changed programmatically

I have an activeX dropdown form on my spreadsheet which executes code on _Change. My code modifies the dropdowns list source (adding or deleting items). Whenever this happens, the _Change is called again.

I have various workarounds, all of which were some version of changing the list source, but with no success. The reason none of this has worked is because clearing or altering the .ListFillRange actually triggers the _Change event again.

How do I prevent the _Changeevent from getting called if I want to add or delete items in the .ListFillRange

UPDATE w EnableEvents set to false:

Public Sub SetRangeForDropdown()
On Error Resume Next

    Application.EnableEvents = False

    'Get new List of employees from Employee sheet
    Dim rng1 As Range
    With wsDB_employee
        Set rng1 = .Range("A2:B" & .Range("A10000").End(xlUp).Row)
    End With
    With wsStage
        .Cells.Clear
        rng1.Copy .Range(.Cells(1, 1), .Cells(rng1.Rows.Count, 2))
    End With

    'Set range for dropdown on employee sheet
    Dim rng2 As Range
    Set rng2 = wsStage.Range("A1:B" & wsStage.Range("A10000").End(xlUp).Row)

    'Update employee list named formula
    ActiveWorkbook.Names.Add Name:="nfEmployeeList", RefersTo:=rng2
    Dim str As String
    str = rng2.Parent.Name & "!" & rng2.Address 'Source path for list fill range
    wsMA.cmbEmployeeSelection.ListFillRange = str

    Application.EnableEvents = True

End Sub

Apperantly EnableEvents does not work for ActiveX controls.

Thank you Microsoft for making life just a little bit more complicated!

Just found this: "Application.EnableEvents=False/True ONLY applies to Sheet and Workbook Events, not ActiveX Control Events" from here enter link description here

Upvotes: 2

Views: 101

Answers (3)

rohrl77
rohrl77

Reputation: 3337

I have solved the problem by adding a global variable that prevents the _Change event from firing. Here is that code:

Private Sub cmbEmployeeSelection_Change()

If bNOTRUN = False Then 'Check if ActiveX event should fire or not

    modEmployeeDB.SaveEmployeeData 'Save currently selected employee data
    modEmployeeDB.DBSoll_To_WorkerInfo 'Get called employee data

End If

End Sub

And this is the module as modified... note the simple Boolean variable that I added:

Public Sub SetRangeForDropdown()

On Error GoTo SetRangeForDropdown_Error

    bNOTRUN = True 'Global Variable that when True prevents Active X from firing

    'Get new List of employees from Employee sheet
    Dim rng1 As Range
    With wsDB_employee
        Set rng1 = .Range("A2:B" & .Range("A10000").End(xlUp).Row)
    End With
    With wsStage

        .Cells.Clear
        rng1.Copy .Range(.Cells(1, 1), .Cells(rng1.Rows.Count, 2))

    End With

    'Set range for dropdown on employee sheet
    Dim rng2 As Range
    Set rng2 = wsStage.Range("A1:B" & wsStage.Range("A10000").End(xlUp).Row)

    'Update employee list named formula
    ActiveWorkbook.Names.Add Name:="nfEmployeeList", RefersTo:=rng2
    Dim str As String
    str = rng2.Parent.Name & "!" & rng2.Address 'Source path for list fill range
    wsMA.cmbEmployeeSelection.ListFillRange = str

    bNOTRUN = False

    On Error GoTo 0
    Exit Sub

SetRangeForDropdown_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SetRangeForDropdown of Sub modEmployeeDB"
    bNOTRUN = False

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

it's always a good habit to make (nearly) sure that events handling is always brought back, like follows:

Public Sub SetRangeForDropdown()


'...your code

    On Error GoTo ExitSub
    Application.EnableEvents = False
    wsMA.cmbEmployeeSelection.ListFillRange = rng2

    'Update employee list named formula
    ActiveWorkbook.Names.Add name:="nfEmployeeList", RefersTo:=rng2

ExitSub:
    Application.EnableEvents = True

End Sub

Furthermore, avoid On Error Resume Next unless you really need it

Upvotes: 1

Vityata
Vityata

Reputation: 43575

You can disable the events in the SetRangeForDropdown and then enable them back.

So, write the following at the start:

Application.EnableEvents = False

And the following at the end:

Application.EnableEvents = true

Upvotes: 3

Related Questions