Help with VBA Events - some work and some don't

[g8r777]

I have a workbook with the following code that runs when the workbook opens (code is in the ThisWorkbook module):

Private Sub Workbook_Open()

Sheet1.Activate


With Sheet3.ComboBox1
    .Clear
    .List = Application.Transpose(Range("AdminList"))
End With


With Sheet3.ComboBox2
    .Clear
    .List = Application.Transpose(Range("YesNo"))
End With


With Sheet3.ComboBox3
    .Clear
    .List = Application.Transpose(Range("YesNo"))
End With


With Sheet3.ComboBox4
    .Clear
    .List = Application.Transpose(Range("YesNo"))
End With


With Sheet3.ComboBox5
    .Clear
    .List = Application.Transpose(Range("Approval"))
End With


With Sheet3.ComboBox6
    .Clear
    .List = Application.Transpose(Range("YesNo"))
End With


With Sheet3.ComboBox7
    .Clear
    .List = Application.Transpose(Range("YesNo"))
    .Visible = False
End With


With Sheet3.Label5
    .Caption = ""
    .Visible = False
End With


With Sheet4.Cells(1, 7)
    .Value = 0
End With


Application.EnableEvents = True


End Sub

However, when I activate another worksheet, the code in the sub with the Worksheet_Activate event doesn't run:

Private Sub Worksheet_Activate()

TextBox1.Activate


End Sub

Even more confusing is that I have code that allows users to use the tab key to move through content controls. This is accomplished with KeyDown events. Here is the code for TextBox1:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)     
    If Shift = 1 And KeyCode = vbKeyTab Then
        TextBox15.Activate
        TextBox15.WordWrap = False
        TextBox15.WordWrap = True
    Else
    
        If KeyCode = vbKeyTab Then
        Application.EnableEvents = True
            TextBox2.Activate
            TextBox2.WordWrap = False
            TextBox2.WordWrap = True
        End If
    
    End If
     
End Sub

If I manually click in TextBox1 and then hit tab to move to TextBox2, the KeyDown event works. Because of the Application.EnableEvents = True in the second half of the sub, now all other events work fine include the Worksheet_Activate event (I can click to another sheet and then back to the sheet in question, TextBox1 is activated).

My question is why doesn't the Worksheet_Activate event work while the KeyDown event does? Incidentally, Textbox.LostFocus events don't work either until the Textbox1_KeyDown event is run. That is initially how I knew there was an event problem. I have another Textbox that has validation code that runs when the textbox loses focus. If I click in this box before tabbing out of Textbox1, the data validation code doesn't work (LostFocus event doesn't even fire).

What am I missing?

[SamT]

Private Sub Worksheet_Activate() 
     
   Sheets(OtherShtName_Here). TextBox1.Activate 
     
     
End Sub

[snb]

A lot of you code is redundant:

Private Sub Workbook_Open() 
    sn=Range("YesNo").value 

    for j =1 to 7
        Sheet3.oleobjects("ComboBox" & j).List = choose(j,Range("AdminList").Value,sn,sn,sn,range("approval").value,sn,sn)
    End With 
    sheet3.combobox7.visible=false

    With Sheet3.Label5 
        .Caption = "" 
        .Visible = False 
    End With 
    Sheet4.Cells(1, 7)= 0 
End sub

[g8r777]

The code you provided gives a Run-time error '438' - Object doesn't support this property or method at the line:

Sheet3.oleobjects("ComboBox" & j).List = choose(j,Range("AdminList").Value,sn,sn,sn,range("approval").value,sn,sn) 

[snb]

check what range(adminlist") is first.

Private Sub Workbook_Open() 
    sn=Range("YesNo").value 
     
    For j =1 To 7 
        Sheet3.oleobjects("ComboBox" & j).object.List = choose(j,Range("AdminList").Value,sn,sn,sn,range("approval").value,sn,sn) 
    End With 
    sheet3.combobox7.visible=False 
     
    With Sheet3.Label5 
        .Caption = "" 
        .Visible = False 
    End With 
    Sheet4.Cells(1, 7)= 0 
End Sub

[g8r777]

Not sure what you mean. This also doesn't answer the questions as to why the Worksheet_Activate event and Textbox.LostFocus event don't run but the Textbox.KeyDown event does (which then causes the other events to work because of the Application.EnableEvents = True line).

Not sure why the EnableEvents command works in the KeyDown event but the same command doesn't seem to do work in the Workbook_Open event.

Seems to me that either all event should work or all should not. I'm not sure why some do and some don't.

[snb]

the keydown event is an activex-control event, not a worksheet event, nor a workbook event.

[g8r777]

Yes. So is the LostFocus event (which doesn't work initially). So back to the question, why do some events work while others don't and how can this be solved?

[snb]

You'd maybe start with the fundamentals of VBA and of Excel's VBA first.

[SamT]

This also doesn't answer the questions as to why the Worksheet_Activate event and Textbox.LostFocus event don't run but the Textbox.KeyDown event does (which then causes the other events to work because of the Application.EnableEvents = True line).

It may be that Application.EnableEvents is not TRUE until you set it by testing for KeyCode = vbKeyTab the second time. Perhaps you have set it FALSE in some other procedure in one workbook or another and did not reset it TRUE.

  Else 
         
        If KeyCode = vbKeyTab Then '<------------------------------ the second time it is tested
            Application.EnableEvents = True 'Is this the only place in the entire Project you set a value for Application.EnableEvents?
            TextBox2.Activate 
            TextBox2.WordWrap = False 
            TextBox2.WordWrap = True 
        End If 
         
    End If

VBA BASICS:
Whenever working with Events, unless there is a specific reason to disable Events, it never hurts to set Application.EnableEvents to TRUE at the start of all procedures. As you gain experience, you will know when you don't have to.

If you disable Events in any procedure, be sure to renable them before you leave that procedure.

[Aflatoon]

Somewhere in your code you are disabling events and not re-enabling them. That is the only possible explanation for those symptoms.

As has been mentioned, Enableevents does not affect events for ActiveX controls - the GotFocus and LostFocus events actually belong to the container OLEObject, not the Textbox control itself, and those are affected by EnableEvents.

[g8r777]

The Workbook_Open sub runs fine. The last line of code in that sub sets EnableEvents to true. No other code is executed before I click on the sheet in question. If I then click on the sheet, the Worksheet_Activate sub does not run and none of the LostFocus event subs run either until the KeyDown event sub with the EnableEvents = True line is run.

So the EnableEvents = True in the Workbook_Open doesn't seem to be working.

The only lines of code with EnableEvents are the two we've been talking about (both of which set to true - one works, one doesn't).

There has to be some other explanation.

[Aflatoon]

I think you'll have to post the workbook. The only thing that is common to the events you say don't work and irrelevant to the ones that do is the Application.EnableEvents setting. How have you tested that the Workbook_Open code is actually running?

[g8r777]

Added the line Sheet5.Cells(1, 1) = "test" to the Workbook_Open sub. Saved the file, exited the file, reopened the file. Sheet5 cell A1 now says "test".

[g8r777]

Here is the workbook in question.

Distribution.xlsm

[Aflatoon]

Works fine for me. Activate event is fired as soon as I select Sheet3. Perhaps you have another workbook/add-in interfering. Try starting Excel in Safe Mode and then open this workbook.

[SamT]

There are only two instances of "EnableEvents" in the workbook and both are set to TRUE.

@ g8r777,
Try commenting out the one in the Workbook_Open sub and see what happens.

Also, try scattering this line of code throughout your project

MsgBox "Enable Events is " & Application.EnableEvents & "in Sub (Insert name of sub here)" 'Debugging

It will tell you the status of EnableEvents at that point.

Finally, teach me something. You have pairs of

Control.WordWrap = False
Control.WordWrap = True

in many places in your code. What is their purpose?

[g8r777]

Thank you Aflatoon. The functionality was correct in safe mode. We have a document management system that has an Excel plugin. This appears to be what is causing the issue. I have disabled the plugin for now and the workbook is functioning properly. I will go back to the vendor and let them know as I don't have any control over the code they are using.

SamT,

See this post to answer your question.

http://www.vbaexpress.com/forum/show...xit&highlight=

When we upgraded to Office 2013, textbox controls got all messed up with font size issues. The only way I could find to correct is by cycling WordWrap off and on. Not very pretty but effective.

[g8r777]

Vendor is working on the problem. In the meantime, since we need both the plugin and the workbook, is there a way (registry entry maybe) to set enableevents = true globally (every time Excel opens this is the case)?

[Aflatoon]

It is set to true whenever you restart Excel, so something is turning it off each time.