PDA

View Full Version : Ribbon Object



AntonioZZZ
06-10-2010, 03:24 PM
Hi everybody,

i'm makeing a simple add in in excel 2007 with a customized tab.

actually the ribbon is very user friendly once you get customed with it...still it is a bit longer to program than the usual menu we had before...by the way...

i'm having this problem: i use an editbox to get some parameter and then perform an action with that... after the routin the editbox is invalidate...

but if something goes wrong the public object myribbon (initialized in onload routine of the customUI), goes away (I can see that is value is nothing)...

how can i prevent it to vanish? shall i take more care of the error handling? in wich way? is there a way reinitialize the public object myribbon?

I don't know if i've been good enough in explaining my problem...ask me if not.

many thanks

Antonio

Paul_Hossler
06-10-2010, 04:27 PM
Good question, often asked, never solved, but I hear Office 2010 is better

1. There's a Office Ribbon forum here also

2. http://www.vbaexpress.com/forum/showthread.php?t=22053

Paul

xld
06-10-2010, 04:32 PM
No, it is just as bad in Office 2010. You have more control over the ribbon, but once the ribbon object goes, you have to reload that tab (which means the workbook).

AntonioZZZ
06-11-2010, 01:24 PM
this is a very bad news..by the way i'll check the right forum...sorry i didn't notice the ribbon section...

if there is a moderator could you please move the topic there?

thanks

Antonio

AntonioZZZ
06-11-2010, 01:33 PM
2. http://www.vbaexpress.com/forum/showthread.php?t=22053

Paul

the backup object is a very good idea! i'll try it hoping that excel cancel out just the current ribbon object and not every public object.

thanks

if anyone else has suggestion i'll wait few days than i'll mark as solved

xld
06-11-2010, 03:09 PM
this is a very bad news..by the way i'll check the right forum...sorry i didn't notice the ribbon section...

if there is a moderator could you please move the topic there?

thanks

Antonio

Moved as requested.

sarali
09-18-2010, 10:46 PM
never used office 2007

Frosty
02-17-2011, 09:45 AM
This is the way I've started referencing it, based on internet research. Obviously where you actually put your memory reference is flexible. I happened to choose the primary header of my vba addin, but it could go anywhere. If in Excel, you could put it on a hidden sheet. In Powerpoint you could put it in some hidden object, I suppose. The primary help with this was WernerGg at the Code Cage Forums, although their application was in Excel.

Whoops, one clarification. I utilize custom conversion functions (so I have "fCStr" instead of "CStr") which basically just error trap any unexpected errors to return the default value.

So my fCStr(null) will return "", whereas CStr(null) would return an error. Remove the "f" from those two lines of code and the below will work, at least most of the time. Or write your own functions to duplicate the above.

Hope this helps anyone who was looking for it. This functionality was invaluable during my development in 2010, so I thought I would share.



'public variable for our ribbon (for the callbacks)
Public pub_myRibbon As IRibbonUI
'required to store pointers to the ribbon
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(destination As Any, source As Any, ByVal length As Long)

'----------------------------------------------------------------------------------------------
'The onload event-- MS says not to put any sort of dialog pop ups during this
'----------------------------------------------------------------------------------------------
Public Sub onLoad(Ribbon As IRibbonUI)
Dim lRibbonPointer As Long

'load our public variable
Set pub_myRibbon = Ribbon

'*** Store a memory reference to our ribbon object, in case the VDE loses scope
'get our long pointer reference, using the objptr function
lRibbonPointer = ObjPtr(Ribbon)
'put in the header, so we can store notes in the main body of the project
ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text = fCStr(lRibbonPointer)
'mark this document as saved, so this never triggers a "do you want to save this project?"
ThisDocument.Saved = True
End Sub
'----------------------------------------------------------------------------------------------
'allow us to reference the ribbon, regardless if the public variable has been lost
'utilizes the CopyMemory library
'----------------------------------------------------------------------------------------------
Public Function myRibbon() As IRibbonUI
Dim oRibbon As Object
Dim lRibbonPointer As Long

'if we've lost our scope, recreate it from our stored pointer
If pub_myRibbon Is Nothing Then
'get our pointer
lRibbonPointer = fCLng(ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text)
'this reloads it from memory
CopyMemory oRibbon, lRibbonPointer, 4
'and restores our public object
Set pub_myRibbon = oRibbon
End If
'return our value
Set myRibbon = pub_myRibbon
End Function

dexterial
03-05-2014, 03:49 AM
#If VBA7 Then
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
#Else
Public Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
#End If



This is the way I've started referencing it, based on internet research. Obviously where you actually put your memory reference is flexible. I happened to choose the primary header of my vba addin, but it could go anywhere. If in Excel, you could put it on a hidden sheet. In Powerpoint you could put it in some hidden object, I suppose. The primary help with this was WernerGg at the Code Cage Forums, although their application was in Excel.

Whoops, one clarification. I utilize custom conversion functions (so I have "fCStr" instead of "CStr") which basically just error trap any unexpected errors to return the default value.

So my fCStr(null) will return "", whereas CStr(null) would return an error. Remove the "f" from those two lines of code and the below will work, at least most of the time. Or write your own functions to duplicate the above.

Hope this helps anyone who was looking for it. This functionality was invaluable during my development in 2010, so I thought I would share.



'public variable for our ribbon (for the callbacks)
Public pub_myRibbon As IRibbonUI
'required to store pointers to the ribbon
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(destination As Any, source As Any, ByVal length As Long)

'----------------------------------------------------------------------------------------------
'The onload event-- MS says not to put any sort of dialog pop ups during this
'----------------------------------------------------------------------------------------------
Public Sub onLoad(Ribbon As IRibbonUI)
Dim lRibbonPointer As Long

'load our public variable
Set pub_myRibbon = Ribbon

'*** Store a memory reference to our ribbon object, in case the VDE loses scope
'get our long pointer reference, using the objptr function
lRibbonPointer = ObjPtr(Ribbon)
'put in the header, so we can store notes in the main body of the project
ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text = fCStr(lRibbonPointer)
'mark this document as saved, so this never triggers a "do you want to save this project?"
ThisDocument.Saved = True
End Sub
'----------------------------------------------------------------------------------------------
'allow us to reference the ribbon, regardless if the public variable has been lost
'utilizes the CopyMemory library
'----------------------------------------------------------------------------------------------
Public Function myRibbon() As IRibbonUI
Dim oRibbon As Object
Dim lRibbonPointer As Long

'if we've lost our scope, recreate it from our stored pointer
If pub_myRibbon Is Nothing Then
'get our pointer
lRibbonPointer = fCLng(ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text)
'this reloads it from memory
CopyMemory oRibbon, lRibbonPointer, 4
'and restores our public object
Set pub_myRibbon = oRibbon
End If
'return our value
Set myRibbon = pub_myRibbon
End Function