Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
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 Auto_Open 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
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. NickH wrote: 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 Auto_Open 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't 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. ?;^) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
On 6 Mar, 19:10, Dave Peterson wrote:
Did you try the 1 second delay? Did it work? Hi Dave, Sorry no, it made no difference. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open - AutoCorrect Routine Doesn't Work
Dave Peterson wrote on 03/09/2009 08:04 ET :
I don't have another guess. wrote: On 6 Mar, 19:10, Dave Peterson wrote: Did you try the 1 second delay? Did it work? Hi Dave, Sorry no, it made no difference. Dave Peterson I found this thread looking for a solution for the same problem. I know 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 guess 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. If 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook_open() sub does not work | Excel Programming | |||
Update Links Stopping Workbook_Open Routine | Excel Programming | |||
Workbook_Open does not work | Excel Programming | |||
Workbook_Open() does not work | Excel Programming | |||
How do I transfer the autocorrect options from work to home? | Excel Discussion (Misc queries) |