PDA

View Full Version : Help with VBA Events - some work and some don't



g8r777
03-31-2014, 10:38 AM
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
03-31-2014, 11:08 AM
Private Sub Worksheet_Activate()

Sheets(OtherShtName_Here). TextBox1.Activate


End Sub

snb
04-01-2014, 01:33 AM
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
04-01-2014, 08:20 AM
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
04-01-2014, 09:25 AM
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
04-01-2014, 11:25 AM
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
04-01-2014, 11:38 AM
the keydown event is an activex-control event, not a worksheet event, nor a workbook event.

g8r777
04-01-2014, 11:42 AM
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
04-01-2014, 01:07 PM
You'd maybe start with the fundamentals of VBA and of Excel's VBA first.

SamT
04-01-2014, 02:11 PM
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
04-02-2014, 02:01 AM
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
04-02-2014, 06:34 AM
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
04-02-2014, 06:51 AM
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
04-02-2014, 07:04 AM
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
04-02-2014, 07:13 AM
Here is the workbook in question.

11499

Aflatoon
04-02-2014, 07:24 AM
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
04-02-2014, 08:11 AM
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)" 'DebuggingIt 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
04-02-2014, 08:24 AM
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/showthread.php?47720-Excel-2013-text-box-glitch-tiny-cursor-on-entry-font-size-fine-on-exit&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
04-02-2014, 02:48 PM
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
04-02-2014, 03:20 PM
It is set to true whenever you restart Excel, so something is turning it off each time.

SamT
04-02-2014, 06:43 PM
You might try a calling long loop function in the Workbook_Open sub as a delay timer before setting EnableEvents.


Private Function Delay()
'This Tells when Enable Events is set FALSE
Dim i, j, k, X, Y
X = True
For i = 1 to 1000
for j = 1 to 1000
for k = 1 to 1000
X = Application.EnableEvents
If Not X Then Y= msgBox "i =" & i & "; j =" & j & "; k =" & k, vbOKCancel
If Y = vbCancel Then Exit Function
Next k
Next j
Next i
End Function

In Workbook_Open

'
'
'
Delay
'
'
''

Adjust the sizes of i, j, and k until you get the problem back then increase one of one of them by a factor of ten. When the Vendor fixes his problem, remove the call to Delay

snb
04-03-2014, 04:09 AM
I'd prefer one of these workbook events (or both)



Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = True
End Sub

Aflatoon
04-03-2014, 04:14 AM
What would be the benefit? If events are disabled neither will run, and if events are enabled, what will they achieve?

GTO
04-03-2014, 04:41 AM
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)?


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

I do not know what a plug-in is. That said, is it something we could disable thru VBA?

g8r777
04-03-2014, 06:34 AM
Meant to say add-in. It's a program that adds additional functionality to Excel either to enhance what Excel can do or, in this case, to provide functionality for Excel to communicate with other programs.

I assume you can disable through VBA however how would the code get run? We've already established that events aren't working properly so the user would have to proactively run the code.

My workaround for now is that I have placed a command button on the worksheet that says "Click Here First". This runs the same code that was in the Workbook_Open sub and also enables events. Everything else works fine from that point on.

A minor inconvenience for our users but not the end of the world.

Aflatoon
04-03-2014, 07:09 AM
Since we know workbook_open works, you could use OnTime:
At the end of Workbook_Open add:

Application.Ontime Now() + Timeserial(0, 0, 2), "ResetEvents"
then in a normal module:

Sub ResetEvents()
Application.EnableEvents = True
End Sub

g8r777
04-03-2014, 07:23 AM
Workbook_Open does not work. That is the event that was not working properly to begin with.

Aflatoon
04-03-2014, 07:34 AM
You said (repeatedly) that Workbook_Open did work, but subsequent Worksheet_Activate didn't.

GTO
04-03-2014, 07:37 AM
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

'etc...
End Sub


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

etc...


The Workbook_Open sub runs fine. ...


Workbook_Open does not work. That is the event that was not working properly to begin with.

My brain hurts. I'm admittedly sleep-deprived this week, and if I have missed something, will apologize, but - this last comment seems quite opposite all prior statements.

g8r777
04-03-2014, 08:12 AM
I thought the Workbook_Open event was working correctly. Apparently it did temporarily but the add-in disabled all events. My brain hurts too. I was disabling and enabling the add-in a lot yesterday and it may have been an instance where it was disabled that the Workbook_Open was functioning.

I think until the vendor can fix the issue the "Click Here First" button is the route to go.

SamT
04-03-2014, 10:37 AM
Put the code in post # 21 in the Personal.xlsm workbook, or whatever it's called in your Excel version.

GTO
04-03-2014, 12:48 PM
Just a thought and only lightly tested, but at least in Excel2010 for WIN, Auto_Open() still works.

In a Standard Module:


Option Explicit

Public bOpenEventRan As Boolean

Public Sub Auto_Open()
Dim sArg As String

If Not bOpenEventRan Then

Application.EnableEvents = True

MsgBox "Hello World!", 0&, vbNullString 'testing

sArg = "'" & ThisWorkbook.Name & "'!ThisWorkbook.Workbook_Open"

Application.Run sArg

End If

End Sub

'testing
Public Sub ToggleEvents()
Application.EnableEvents = Not Application.EnableEvents
MsgBox "EnableEvents = " & Application.EnableEvents
End Sub

In ThisWorkbook Module:


Option Explicit

Private Sub Workbook_Open()

bOpenEventRan = True

MsgBox "Your code here:" & vbCrLf & vbCrLf & _
"If Workbook_Open() ran first (and no ""Hello World!"")," & vbCrLf & _
"EnableEvents equaled True. If ""Hello World!"" showed first," & vbCrLf & _
"'Auto_Open' ran and reset EnableEvents.", 0&, vbNullString

End Sub

Hope that helps,

Mark

snb
04-03-2014, 02:19 PM
You can open the file from Word.
Before loading you can adapt the application.enableevents property.

In the Document_open event of the word document:


Private Sub Document_Open()
With GetObject(, "Excel.Application")
.enableevents = True
.Visible = True
.Workbooks.Open "G:\OF\example.xlsb"
End With
ThisDocument.Close 0
End Sub

The only thing users have to do is open the Worddocument. No extra button required.

Aflatoon
04-04-2014, 02:27 AM
@Mark,

Excellent idea! :)

GTO
04-04-2014, 02:55 AM
@Aflatoon:

:115: A rare moment of lucidity... :cloud9:

(Thank you so much)

Mark