Workbook_Open - AutoCorrect Routine Doesn't Work

[NickH]

I'm using Excel 2003 - SP3

I've written a couple of routines to Add and subsequently Delete a
couple of AutoCorrect entries. The intention was to call the Add
routine from Workbook_Open and the Del routine from
Workbook_BeforeClose.

Both routines work fine when fired from the VBE using F5. The Del
routine also works fine when called from the Workbook_BeforeClose
routine. However, the Add routine is impotent when called from either
the Workbook_Open or Autpen routines.

It runs without error, as evidenced by msg boxes but it has no effect
on the ACL. Here's the code for the Add routine - apologies for any
wrap-around...

Public Sub AutoCorrectAdd()
Dim repl() As Variant
Dim x As Long
Dim TLA_used As Boolean
Dim TLAO_used As Boolean

MsgBox "Start of AutoCorrectAdd. Error = " & Err.Number

TLA_used = False
TLAO_used = False

repl = Application.AutoCorrect.ReplacementList

For x = 1 To UBound(repl)
If repl(x, 1) = "TLA" Then TLA_used = True
If repl(x, 1) = "TLAO" Then TLAO_used = True
Next

If Not TLA_used Then _
Application.AutoCorrect.AddReplacement _
What:="TLA", _
Replacement:="Top Level Action Owner"
If Not TLAO_used Then _
Application.AutoCorrect.AddReplacement _
What:="TLAO", _
Replacement:="Top Level Action Owner"
' Yes I do want both TLA and TLAO to mean the same

MsgBox "End of AutoCorrectAdd. Error = " & Err.Number

End Sub

There are a small number of earlier posts regarding this issue and
responses all seem to imply the developer is doing something wrong but
don't manage to pinpoint what. I'm wondering if this could be a
version/update specific problem. Is it possible that Microsoft have
blocked ACL changes by opening routines, to prevent a specific hack?

I guess what I'm asking is - Am I fighting a lost battle or is there a
way round this?

 

[Dave Peterson]

Your code worked fine for me (also xl2003).

And since you saw the msgbox output, the code must be running. And since you
can run the procedure successfully by using F5, then you have permission to
update the ACL file.

I tried opening the file with the macro when there were no other visible
workbooks open, by double clicking on the filename in windows explorer, by
saving it as an addin and doing both those worked fine. I couldn't break it.

So my guess (and it's just a guess!) is that excel needs some time to play
catchup when it's opening your file.

I added a short delay and it still worked ok--but that's not much of a test for
me, since it worked ok before.

This was my code:

Option Explicit
Sub Auto_open()
Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & ThisWorkbook.Name & "'!dothework"
End Sub
Sub auto_close()
On Error Resume Next 'in case either isn't there.
Application.AutoCorrect.DeleteReplacement what:="TLA"
Application.AutoCorrect.DeleteReplacement what:="TLAO"
On Error GoTo 0
End Sub
Sub DoTheWork()
Application.AutoCorrect.AddReplacement _
what:="TLA", _
Replacement:="Top Level Action Owner"
Application.AutoCorrect.AddReplacement _
what:="TLAO", _
Replacement:="Top Level Action Owner"
End Sub

I've seen other posts similar to yours where a slight delay allows excel to
catch up and do things right. (I've never had to use this in real life,
though.)

And if you really want those entries in your ACL, then I wouldn't even check.
Just overwrite them <bg>.

 

[hewett_nick]

Oh heck! Now I'm stumped. I was kind of hoping someone would come back
with a "No you can't do that - you'll have to find another way." Then
I'd go ahead and use the Worksheet_Change event on each sheet. Now
I've got to find out why this works for you and not for me or I won't
sleep all weekend.

Security settings?
My security level = Medium
Trusted Publishers...
Trust all installed Add-Ins and templates = ticked
Trust access to Visual Basic Project = ticked

And hey I'm trying to make life easier for my users not stomp all over
their beloved TLA's (hence the alternative). You're a hard man Dave
Peterson. ?;^)

 

[Dave Peterson]

Did you try the 1 second delay?

Did it work?

Oh heck! Now I'm stumped. I was kind of hoping someone would come back
with a "No you can't do that - you'll have to find another way." Then
I'd go ahead and use the Worksheet_Change event on each sheet. Now
I've got to find out why this works for you and not for me or I won't
sleep all weekend.

Security settings?
My security level = Medium
Trusted Publishers...
Trust all installed Add-Ins and templates = ticked
Trust access to Visual Basic Project = ticked

And hey I'm trying to make life easier for my users not stomp all over
their beloved TLA's (hence the alternative). You're a hard man Dave
Peterson. ?;^)

 

[hewett_nick]

Did you try the 1 second delay?

Did it work?

Hi Dave,

Sorry no, it made no difference.

 

[Dave Peterson]

I don't have another guess.

Hi Dave,

Sorry no, it made no difference.

 

[yeshoney]

Dave Peterson wrote on 03/09/2009 08:04 ET :

I don't have another guess.

wrote:


Dave Peterson

I found this thread looking for a solution for the same problem. I know what
going on but cant solve the issue. Adding to ACL when opening the workboo
works, even if you dont see it later on. The problem is it is adding th
entries to the list in a language that is not the language I use for editing.
My Office-2007 is set to display UI in english but is set to use spanish fo
editing. When Excel starts, the editing language is still english (my guess i
it is set to the language of the UI). The routine to add to ACL adds to m
english list, not to my spanish list. Later on, as soon as data is entered int
a cell for example, excel internally makes the switch to spanish. If then I us
VBA to add to ACL, it adds to the spanish list.

Adding a Wait doesnt help.. it is not a matter of time. It is a matter o
steps: when does the switch from one language to the other occur.

So far I have not been able to use VBA to tell excel to make to switch NOW.

Not only that: What if you really want to add to the list in a coupl
different languages?

 

[yeshoney]

yeshoney wrote on 05/11/2010 13:41 ET :

Dave Peterson wrote on 03/09/2009 08:04 ET :
I found this thread looking for a solution for the same problem. I kno whats
going on but cant solve the issue. Adding to ACL when opening the workbook
works, even if you dont see it later on. The problem is it is adding the
entries to the list in a language that is not the language I use for editing

My Office-2007 is set to display UI in english but is set to use spanish for
editing. When Excel starts, the editing language is still english (my gues is
it is set to the language of the UI). The routine to add to ACL adds to my
english list, not to my spanish list. Later on, as soon as data is entered
into a cell for example, excel internally makes the switch to spanish. I then
I use VBA to add to ACL, it adds to the spanish list.

Adding a Wait doesnt help.. it is not a matter of time. It is a matter of
steps: when does the switch from one language to the other occur.

So far I have not been able to use VBA to tell excel to make to switch NOW.

Not only that: What if you really want to add to the list in a couple
different languages?

I solved my problem in a far from elegant way (SENDKEYS way...), but at least
could move on.
Added a couple lines to Workbook_Open to force a language change:

Dim Celda As Range
Dim ValorCelda
Set Celda = ActiveCell
ValorCelda = Celda.Value
SendKeys "a", True ' Any value
SendKeys "~", True ' Enter. AT THIS POINT my editing languag
is rescued.
Celda.Activate ' Put things back to what they were:
Celda.Value = ValorCelda ' ActiveCell and it's value