SammyB wrote: ↑31 Dec 2020, 03:43
I have solved Item 5, Button push OnAction. It doesn't seem to check if the macro is in PERSONAL.XLSB. This code works:
......
.OnAction = "PERSONAL.XLSB!" & sAction
.......
Hello SammyB
So are we saying that this problem item 5 was not directly to do with the 32 64 issue?
My guess would be that you were just lucky previously that Excel took the right guess at where to find the macro that you were referring to. ( Possibly Excel will often guess correctly where your macro is if it is one in your
PERSONAL.XLSB ***)
It’s probably best to always give the full macro reference.....
I have never ever used the
.OnAction, and also, although I had heard of the
PERSONAL.XLSB thing , I had not had much to do with that thing either…
But I took a guess that the correct referencing syntax might be something similar to things like the syntax in
Application.OnTime or Application.Run
My initial investigation suggests that is the case. That being the case, it might help avoid other unexpected problems to try to fully reference the macro.
It took me a couple of years to finally figure out the tricky syntax in the full reference for a macro. So I thought it would be useful for me here to post the example for this Thread for future reference.
The following macros below is all in the uploaded workbook,
API3264Button.xls. It is doing something similar to your macro. It makes a couple of buttons. I am just trying to demo for you with the macros the full syntax for a macro got at by the
.On Action
http://i.imgur.com/onDA5lH.jpg
Code: Select all
Sub SamTest() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=35920
Rem First Call of PlaceButton to standard module here
' Call PlaceButton(ActiveSheet.Range("A7:E9"), "SayHello", "'F:\Excel0202015Jan2016\OffenFragensForums\eileenslounge\API3264Button.xls'!'Modul1.Samction ""Hello""'")
1 Call PlaceButton(ActiveSheet.Range("A7:E9"), "SayHello", "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!'Modul1.Samction ""Hello Sammy"" & vBCr & VblF , 1 , ""from "" & tHisWOrkbOOk.nAmE '")
Rem Second Call of PlaceButton to Personal thing
' where is my Personal thing..... FoundPersonal XLSB.JPG : http://i.imgur.com/1b6bPNN.jpg C:\Users\Elston\AppData\Roaming\Microsoft\Excel\XLSTART
' you you will need to modify this following bit accordingly to get to your PERSONAL.XLSB and the macro in it. ( so you need to change the path at the start and also change the module name )
2 Call PlaceButton(ActiveSheet.Range("A11:E13"), "SayHello2", "'C:\Users\ElstOn\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!'Modul1.Samction2 ""Hello Sammy"" & VBcr & vbLF , 2 , ""from PERSONAL.XLSB"" '")
End Sub
'
Private Sub PlaceButton(r As Range, sPrompt As String, sAction As String)
Dim b As Button
Set b = r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
With b
.Caption = sPrompt
.OnAction = sAction
End With
End Sub
' This macro can be referenced by simply Samction But for demo purposes I use the full reference in Call 1 'F:\Excel0202015Jan2016\OffenFragensForums\eileenslounge\API3264Button.xls'!'Modul1.Samction ""Hello""'
Sub Samction(ByVal Word1 As String, ByVal Nmber As Long, ByVal Word2 As String)
MsgBox Prompt:="Call " & Nmber & ". " & Word1 & " " & Word2
End Sub
You will need to do a few things before you run
Sub SamTest()
_ First in your
PERSONAL.XLSB you will need to put a macro like this in a module
http://i.imgur.com/MTtWoMU.jpg
Code: Select all
Sub Samction2(ByVal Word1 As String, ByVal Nmber As Long, ByVal Word2 As String)
MsgBox Prompt:="Call " & Nmber & ". " & Word1 & " " & Word2
End Sub
_ You will then need to make a note of the code module name, and also try to find and make a note of the full path to your
PERSONAL.XLSB. It took me a while to find my
PERSONAL.XLSB . In fact I couldn’t find it anywhere until after I had saved the above macro (
Sub Samction2 ) and restarted my Excel. I then finally found it here:
C:\Users\Elston\AppData\Roaming\Microsoft\Excel\XLSTART
(
***Incidentally, that path looks similar to a default one that I have often seen Excel guess for me. So that might explain why you have a good chance sometimes of Excel guessing correctly where your
PERSONAL.XLSB is: That sort of path may be a typical default path that Excel uses. ).
Once you have done all that, then, you will need to modify this following bit accordingly to get to your
PERSONAL.XLSB and the macro in it. ( So you need to change the path at the start and also change the module name )
C:\Users\ElstOn\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!'Modul1.Samction2
( make sure you check in the complete final code line that you have two pairs ( so 4 in total ) of the single quotes
'
It must have a form like pseudo
'yyyyyyyy
'!'xxxxxxxx
' )
It took me a couple of years to finally figure out the tricky syntax in the full reference for a macro. That’s why I thought it would be helpful to post the example for this Thread for future reference.
Alan
_.___________________________________-
Share ‘PERSONAL.XLSB’ :
https://app.box.com/s/hzbsw05wq5mw4mefmphg1ju83i3gizgd
Share ‘API3264Button.xls’ :
https://app.box.com/s/z6otirzalf2ktj3kqy97sdy11md3wiqb
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also