CODEWITHSUNDEEP
excelvbalistbox
bilakay
bilakay

Reputation: 67

Overwriting values in a range which is sourcerange of a listbox

I have a listbox on a userform which has a sourcerange which I am trying to overwrite by providing values from a userform but as soon as I overwrite a particular cell the event ListBox1_Click() fires up which is undesirable as it repopulates the data on the userform.

Private Sub ListBox1_Click()

Application.EnableEvents = False
Dim i As Long, fRow As Long

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        If i > 0 Then
            HSht.Range("cRow").Value = i + 1
            fRow = HSht.Range("cRow").Value
            Call getData(fRow)
            HSht.Range("LRow").Value = getlastRow()
            Me.ItemLbl.Caption = "Item number :" & HSht.Range("cRow").Value - 1 & " of " & HSht.Range("LRow").Value - 1
        End If
        Exit For
    End If
Next i

Application.EnableEvents = True
End Sub

Here is the update button code:

Private Sub cmdUpdate_Click()

Application.EnableEvents = False

'Update
Dim fRow As Long, i As Long
fRow = HSht.Range("cRow").Value
Call updateData(fRow)
HSht.Range("LRow").Value = getlastRow()
Me.ItemLbl.Caption = "Item number :" & HSht.Range("cRow").Value - 1 & " of " & HSht.Range("LRow").Value - 1

'MsgBox "Data updated successfully"
Application.EnableEvents = True
End Sub

E.g let's you have 10 fields and you have ten textbox on a userform to view/modify the data but you also have multicolumn listbox to view and scroll the data in a table format, when I scroll up or down I get the specific rows data in the textboxes on userform, I also have a button which says "overwrite" in case I want to modify the data on the worksheet through userform. But as soon it modifies one cell in the worksheet the event "Listbox1_click" triggers and it overwrites the data on the userform.

Upvotes: 2

Views: 169

Answers (1)

ComputerVersteher
ComputerVersteher

Reputation: 2696

Application.EnableEvents = false won't affect UserForms. You have to create a property and check it's value at event start and exit event sub if events disabled like:

' Top of UserForm-Class
Public EnableEvents As Boolean ' if Private code outside the userform can't change value.
'One should add a Letter/Getter to have more control over the property (exposing the variable that stores a property-value isn't recommended I think, with Get/Let we can perform checks or just make the Letter private, but the Getter public)
Private Sub UserForm_Initialize()
    Me.EnableEvents = True
End Sub

Private Sub ListBox1_Click()
If Me.EnableEvents = False Then 'the first three lines of code suppress the events-code execution if EnableEvents = False and must be on top of every event that you want to have disabled.
  Exit Sub
End If
 'Me.EnableEvents = False should be set on top of button code and Me.EnableEvents = True at buttom if other events of from should be suppressed.

Dim i As Long, fRow As Long

For i = 0 To ListBox1.ListCount - 1
  ...

End Sub

Private Sub cmdUpdate_Click()
If Me.EnableEvents = False Then 'the first three lines of code suppress the events-code execution and must be on top of every event that you want to have disabled.
  Exit Sub
End If
Me.EnableEvents = False 'disable Form-Events

... 'Button-Code

Me.EnableEvents = True 'reenable events
End Sub

Upvotes: 2

Related Questions