PDA

View Full Version : VBA events part 2



JKwan
01-13-2012, 10:33 PM
Wondering if the events on the form can be turned off, lime worksheet?

mikerickson
01-14-2012, 12:57 AM
Application.EnableEvents does not effect userform events.
I use a boolean variable ufEventsDisabled as below in the userform's code module.
Dim ufEventsDisabled As Boolean

Private Sub ScrollBar1_Change()
If ufEventsDisabled Then Exit Sub
ufEventsDisabled = True
TextBox1.Text = CStr(ScrollBar1.Value)
ufEventsDisabled = False
End Sub

Private Sub TextBox1_Change()
If ufEventsDisabled Then Exit Sub
ufEventsDisabled = True
With TextBox1
.Text = Format(Val(.Text), "0000")
ScrollBar1.Value = Val(.Text)
End With
ufEventsDisabled = False
End Sub

Bob Phillips
01-14-2012, 09:06 AM
This is very much a style thing, but I prefer to structure the code like so



Private Sub ScrollBar1_Change()
If Not ufEventsDisabled Then

ufEventsDisabled = True

TextBox1.Text = CStr(ScrollBar1.Value)

ufEventsDisabled = False
End If
End Sub

I just feel that this is better indicating the action.

Simon Lloyd
01-14-2012, 09:40 AM
The op doesn't state when to disable the events on the userform but i suggest the boolean is set at initialize and then manipulated afterwardsPrivate Sub UserForm_Initialize()
ufEventsDisabled = True
End Sub

JKwan
01-14-2012, 10:56 AM
Thank you everyone.
I will take a closer look when I am back at the office.
Basically, what I want to do is execute the initial event then don't execute subsequent events - until I am "done". What is happening is that I have a listbox, when I click an item within it, there are items that I don't want the user to select, I pop up a msgbox, then change my listindex to -1. However, once the listindex is -1, the click event fires up again (which I want to stop). I don't know if this is causing my "de-selection" not working, because when I change my listindex to -1, my listbox item is still selected (the one I want to de-select, is still selected).

Bob Phillips
01-14-2012, 12:36 PM
That is what Mike's code caters for.

mikerickson
01-14-2012, 07:54 PM
Simon, if one starts with the UF events disabled, how is the user to enable them?

Simon Lloyd
01-16-2012, 03:16 PM
Simon, if one starts with the UF events disabled, how is the user to enable them?Lol, good thinking batman!, that'll teach me to post off the cuff solutions! :banghead:

JKwan
01-16-2012, 03:48 PM
For the most part, I think that I got the events disabled where I like them to be disabled, thank you all. There is still one big thing that I like to do, but I am not able to get it to work. If you refer to post #5, I like to deselect the item from the listbox, but I am not able to. Hopefully, someone can help me out here. I attached a sanitized version of my program. if you goto cell B2, this will kick off the program. If you select LOPC, then the Product box is enabled. Now, select the first item RAW Hydrocarbon, this is a header, therefore, I don't want it to be selected. msgbox pops up and the program sets the LISTINDEX to -1, but it is not deselecting the item. I am doing this further down in my code in the lstPSClassification_Click, this deselects the item from the listbox.

Private Sub lstProduct_Click()
If bEventsDisabled Then Exit Sub
bEventsDisabled = True
If Not CheckSelection(Me.lstProduct) Then
MsgBox "You cannot make use of HEADER / Break Line", vbExclamation

' by putting the ListIndex to -1, there should be nothing selected
' I did this on the lstPSClassification_Click, it deselected the item,
' however, it is not working here, wondering why???
Me.lstProduct.ListIndex = -1
End If
bEventsDisabled = False
End Sub


Private Sub lstPSClassification_Click()
If Me.lstPSClassification.List(Me.lstPSClassification.ListIndex) = "LOPC" Then
Me.lstProduct.Enabled = True
Me.tbVolume.Enabled = True
Me.lstUnits.Enabled = True
Me.lstProduct.BackColor = &H80000005
Me.tbVolume.BackColor = &H80000005
Me.Repaint
Else
Me.lstProduct.ListIndex = -1 ' this works
Me.lstUnits.ListIndex = -1
Me.tbVolume = ""
Me.lstProduct.Enabled = False
Me.lstProduct.BackColor = &H8000000F
Me.tbVolume.Enabled = False
Me.lstUnits.Enabled = False
Me.tbVolume.BackColor = &H8000000F
End If
End Sub