none
Converting 2003 Custom Menu Bar to 2007 Ribbon

    Question

  • Hi all! :-)
     
    I will taking over a project soon to convert some Access apps created in
    2003 to 2007.  Several of the apps have custom toolbars. The client also
    wants to update the menu tabs and commands as well. After the 2003 menus are
    converted into 2007 Ribbons, they will need to be compatible with Access
    2010 as well.  There is an abundance of information on how to do this, and
    many vary in method and level of difficulty, and I have reviewed many of
    them. But, I was wondering if there might be a 2003 Menu bar to 2007 Ribbon
    utility that might help speed things up a bit. I thought perhaps some of the
    experts here might have some information on this, or some simple guides of
    how to transfer the commands from one to the other.
     
    Any suggestions or references would be very much appreciated.
     
    Jan :)
     
     
    Thursday, March 03, 2011 2:03 AM

Answers

  • Any suggestions or references would be very much appreciated.
     
     
     
    A few things:
     
    For your ribbon button code you want to use the OnAction setting and AVOID using a call back. In other words, you do not need to use callbacks but can have the OnAction DIRECTLY call a public function (this works by using the expression service in Access). This also means you do not need to use macros but can use VBA.
     
    Most Ribbon articles suggest to use this format for the xml on Action:
     
    onAction="MyButtonCallbackOnAction"/>
     
    The above is a ribbon call back. The above means you MUST place the VBA sub in a standard code module. You cannot place the code in the forms code module (where 9 out of 10 times that is where the code should be or will be).
     
    eg:
    Sub MyButtonCallbackOnAction(control As IRibbonControl)
    ' Callback Ribbon Button Klick

        Select Case control.id
            Case "MyButton1"

     
     
    The above is a real pain since code now cannot be placed in the form but some separate code module.
     
    You can avoid this issue and NOT USE an callback.
     
    Simply adopt and use the following format for on Action:
     
    =MyPublicFunctionName()
     
    So, the xml for the on action should be written as:
     
    <button id="MyHello" label="Show Hello"
     
        onAction="=MyPublicFunctionName()"
     
    />
     
     
    Note that you MUST place the = and the () under the string. And we now ARE NOT using sub, but above will direct call a function. So, no need for macro or call back code.
     
    By the way this is exactly the same format we can and would have been using since day one with menu bars in previous editions of access. If the developer had previously used this approach with custom menu bars that call VBA functions direct, then your conversion will be very easy from those menu bars to ribbon.
     
    I also have a ribbon class object that also allows you to write old style menu bar code to manipulate the ribbon to do things like enable or disable buttions. The syntax is very close to menu bar code and if you have places in your application that enable or disable menu bars, then this code will be of great help to convert that code to work on the ribbon.
     
     
    The above also lets you set lables and even change the picture with ease.
     
    Now that we know the Above concept for on Action, I will explain a bit further why you to use this approach:
     
    If the function name you specified in the on Action setting of the menu bar or now ribbon was named as public in the form's code module, then the CURRENT FORM with the CURRENT focus is where the function will be first looked for to execute. This is SIGNIFICANTLY important because it means you can use one custom menu bar for five different forms, but each of the five different forms will run a custom delete routine or whatever you want. And the code for each form remains in the form. (so, this tip is great for when over time you want to move buttons from a form up to the ribbon and keep the SAME code that the buttons on the form were calling before).
     
    So for example you could have a delete button on the ribbon. However you might need and want specific and specialized delete code for different forms. Well above means that for the given form that has the focus, then that's forms function code in the forms module will be run when it is called from the on action in the menu bar, or now ribbon.
     
    This means we could set the on action to =MyDelete()
     
    Then in each form you have, you simply declare a public functions such as
     
    Public Function MyDelete()
     
       code here to prompt and delete current record
     
    Note that you can also pass parameters right from the ribbon xml also.
     
    So for example, to have 3 buttons on the ribbon would launch 3 reports, we can do this with ONE function inside of the form.
     
    So, xml could be:
     
      <ribbon startFromScratch="true">
        <tabs>
          <tab id="MyReport" label="Report Print and View Options">
     
             <group id="Reports" label="Reports">
     
              <button id="rp1" label="Daily Sales"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySales')"
                     supertip= "Daily Sales Report"
                />
     
              <button id="rp2" label="Daily Sales by type"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySalestype')"
                     supertip= "Daily Sales by type"
                />
     
              <button id="rp3" label="Daily Sales by location"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySalesLocation')"
                     supertip= "Daily Sales by location"
     
              />
        </group>
       </tab>
    </tabs>
     
      </ribbon>
    </customUI>
     
     
    The above would look like:
     
     
    And we then can place the ONE single function in the forms code module:
     
     
    Public Function MyReport(strReportName As String)
     
       DoCmd.OpenReport strReportName, acViewPreview
     
    End Function
     
     
    So, the above shows how one bit of code can be passed values from the ribbon (in this case the name of the report to view). Note again how there is no iribbon defs and we not using a Sub. So, it is just plain Jane simple VBA function that is called directly from the ribbon. The above advice should simplify development with the ribbon by a quite a lot.
     
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    • Proposed as answer by Bruce Song Monday, March 07, 2011 6:58 AM
    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Saturday, March 05, 2011 7:55 PM
  • Hi Jan,

    Thank you for posting.

    As far as I know, there exists a very simple way by using USysRibbons table to add filed names to it, the table contains three fields: ID, RibbonName and RibbonXml. The RibbonXml field contains the detail information about your custom ribbon. Here is the MSDN article about how to custom Office2007 Ribbon: http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx#OfficeCustomizingRibbonUIforDevelopers_Runtime

    You can take a look at creating an Access Application-Level Custom Ribbon part.

    I hope it can help you and feel free to follow up after you have tried the sample according the steps.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Thursday, March 03, 2011 6:04 AM
  • Hi Jan,

    I would recommend you this tool http://www.ribboncreator.de/en/?CommandBars_Converter

    I didn't try it by myself but I absolutely rely on Gunter's experience since this website is very useful at all.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Thursday, March 03, 2011 6:45 AM

All replies

  • Hi Jan,

    Thank you for posting.

    As far as I know, there exists a very simple way by using USysRibbons table to add filed names to it, the table contains three fields: ID, RibbonName and RibbonXml. The RibbonXml field contains the detail information about your custom ribbon. Here is the MSDN article about how to custom Office2007 Ribbon: http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx#OfficeCustomizingRibbonUIforDevelopers_Runtime

    You can take a look at creating an Access Application-Level Custom Ribbon part.

    I hope it can help you and feel free to follow up after you have tried the sample according the steps.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Thursday, March 03, 2011 6:04 AM
  • Hi Jan,

    I would recommend you this tool http://www.ribboncreator.de/en/?CommandBars_Converter

    I didn't try it by myself but I absolutely rely on Gunter's experience since this website is very useful at all.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Thursday, March 03, 2011 6:45 AM
  • Hi Bruce :-)
     
    Thank you very much for the information you have provided. I will take a look at the link and try it. 
     
    I truly appreciate your time and help.
     
    Jan :) 

    Hi Jan,

    Thank you for posting.

    As far as I know, there exists a very simple way by using USysRibbons table to add filed names to it, the table contains three fields: ID, RibbonName and RibbonXml. The RibbonXml field contains the detail information about your custom ribbon. Here is the MSDN article about how to custom Office2007 Ribbon: http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx#OfficeCustomizingRibbonUIforDevelopers_Runtime

    You can take a look at creating an Access Application-Level Custom Ribbon part.

    I hope it can help you and feel free to follow up after you have tried the sample according the steps.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 03, 2011 9:32 AM
  • Hey Audrey :-)
     
    Thank you for sharing the information and I will certainly check it out.
     
    Your time and assistance is very much appreciated.
     
    Jan :)
     

    Hi Jan,

    I would recommend you this tool http://www.ribboncreator.de/en/?CommandBars_Converter

    I didn't try it by myself but I absolutely rely on Gunter's experience since this website is very useful at all.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, March 03, 2011 9:35 AM
  • Any suggestions or references would be very much appreciated.
     
     
     
    A few things:
     
    For your ribbon button code you want to use the OnAction setting and AVOID using a call back. In other words, you do not need to use callbacks but can have the OnAction DIRECTLY call a public function (this works by using the expression service in Access). This also means you do not need to use macros but can use VBA.
     
    Most Ribbon articles suggest to use this format for the xml on Action:
     
    onAction="MyButtonCallbackOnAction"/>
     
    The above is a ribbon call back. The above means you MUST place the VBA sub in a standard code module. You cannot place the code in the forms code module (where 9 out of 10 times that is where the code should be or will be).
     
    eg:
    Sub MyButtonCallbackOnAction(control As IRibbonControl)
    ' Callback Ribbon Button Klick

        Select Case control.id
            Case "MyButton1"

     
     
    The above is a real pain since code now cannot be placed in the form but some separate code module.
     
    You can avoid this issue and NOT USE an callback.
     
    Simply adopt and use the following format for on Action:
     
    =MyPublicFunctionName()
     
    So, the xml for the on action should be written as:
     
    <button id="MyHello" label="Show Hello"
     
        onAction="=MyPublicFunctionName()"
     
    />
     
     
    Note that you MUST place the = and the () under the string. And we now ARE NOT using sub, but above will direct call a function. So, no need for macro or call back code.
     
    By the way this is exactly the same format we can and would have been using since day one with menu bars in previous editions of access. If the developer had previously used this approach with custom menu bars that call VBA functions direct, then your conversion will be very easy from those menu bars to ribbon.
     
    I also have a ribbon class object that also allows you to write old style menu bar code to manipulate the ribbon to do things like enable or disable buttions. The syntax is very close to menu bar code and if you have places in your application that enable or disable menu bars, then this code will be of great help to convert that code to work on the ribbon.
     
     
    The above also lets you set lables and even change the picture with ease.
     
    Now that we know the Above concept for on Action, I will explain a bit further why you to use this approach:
     
    If the function name you specified in the on Action setting of the menu bar or now ribbon was named as public in the form's code module, then the CURRENT FORM with the CURRENT focus is where the function will be first looked for to execute. This is SIGNIFICANTLY important because it means you can use one custom menu bar for five different forms, but each of the five different forms will run a custom delete routine or whatever you want. And the code for each form remains in the form. (so, this tip is great for when over time you want to move buttons from a form up to the ribbon and keep the SAME code that the buttons on the form were calling before).
     
    So for example you could have a delete button on the ribbon. However you might need and want specific and specialized delete code for different forms. Well above means that for the given form that has the focus, then that's forms function code in the forms module will be run when it is called from the on action in the menu bar, or now ribbon.
     
    This means we could set the on action to =MyDelete()
     
    Then in each form you have, you simply declare a public functions such as
     
    Public Function MyDelete()
     
       code here to prompt and delete current record
     
    Note that you can also pass parameters right from the ribbon xml also.
     
    So for example, to have 3 buttons on the ribbon would launch 3 reports, we can do this with ONE function inside of the form.
     
    So, xml could be:
     
      <ribbon startFromScratch="true">
        <tabs>
          <tab id="MyReport" label="Report Print and View Options">
     
             <group id="Reports" label="Reports">
     
              <button id="rp1" label="Daily Sales"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySales')"
                     supertip= "Daily Sales Report"
                />
     
              <button id="rp2" label="Daily Sales by type"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySalestype')"
                     supertip= "Daily Sales by type"
                />
     
              <button id="rp3" label="Daily Sales by location"
                     imageMso="CreateReport" size="large"
                     onAction="=myReport('rptDailySalesLocation')"
                     supertip= "Daily Sales by location"
     
              />
        </group>
       </tab>
    </tabs>
     
      </ribbon>
    </customUI>
     
     
    The above would look like:
     
     
    And we then can place the ONE single function in the forms code module:
     
     
    Public Function MyReport(strReportName As String)
     
       DoCmd.OpenReport strReportName, acViewPreview
     
    End Function
     
     
    So, the above shows how one bit of code can be passed values from the ribbon (in this case the name of the report to view). Note again how there is no iribbon defs and we not using a Sub. So, it is just plain Jane simple VBA function that is called directly from the ribbon. The above advice should simplify development with the ribbon by a quite a lot.
     
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    • Proposed as answer by Bruce Song Monday, March 07, 2011 6:58 AM
    • Marked as answer by Bruce Song Tuesday, March 15, 2011 10:00 AM
    Saturday, March 05, 2011 7:55 PM
  • Hi Albert :-)

    Thank you so very much for all the very detailed information and codes suggestions. This is my first time creating a ribbon from scratch, and I have been trying to find some sort of utility or software that would simplify creating the ribbon and converting the necessary custom 2003 menu bar functions. I have been sampling some of the step-by-step articles and utilities to try and get a feel for how things should work. While it is coming along, it is going to depend on how much is involved in the 2003 menu bar.

    I will try your ribbon class object and see how that works as well. The one thing that I am not sure of, is what kind of coding is behind the 2003 current custom menu bar, and what it might take to get it into the ribbon. I am hoping to be able to get a sample of that soon and then I can better determine what method will work best for the conversion. I am hoping that I don't have to redo the entire 2003 menu information, but, if it does not seem that it will be compatible enough, then that would be the best course of action.

    Again, thank you for your time to reply, and the great detailed information, it is truly appreciated. :-)

    Jan :)

    "Albert D. Kallal [MVP]" wrote in message news:48aa8713-044d-412c-a99b-2d6d22e1e025@communitybridge.codeplex.com...







    Any suggestions or references would be very much appreciated.




    A few things:

    For your ribbon button code you want to use the OnAction setting and AVOID using a call back. In other words, you do not need to use callbacks but can have the OnAction DIRECTLY call a public function (this works by using the expression service in Access). This also means you do not need to use macros but can use VBA.

    Most Ribbon articles suggest to use this format for the xml on Action:

    onAction="MyButtonCallbackOnAction"/>

    The above is a ribbon call back. The above means you MUST place the VBA sub in a standard code module. You cannot place the code in the forms code module (where 9 out of 10 times that is where the code should be or will be).

    eg:Sub MyButtonCallbackOnAction(controlAs IRibbonControl)
    ' Callback Ribbon Button Klick

    Select Case control.id
    Case "MyButton1"



    The above is a real pain since code now cannot be placed in the form but some separate code module.

    You can avoid this issue and NOT USE an callback.

    Simply adopt and use the following format for on Action:

    =MyPublicFunctionName()

    So, the xml for the on action should be written as:

    <button id="MyHello" label="Show Hello"

    onAction="=MyPublicFunctionName()"

    />


    Note that you MUST place the = and the () under the string. And we now ARE NOT using sub, but above will direct call a function. So, no need for macro or call back code.

    By the way this is exactly the same format we can and would have been using since day one with menu bars in previous editions of access. If the developer had previously used this approach with custom menu bars that call VBA functions direct, then your conversion will be very easy from those menu bars to ribbon.

    I also have a ribbon class object that also allows you to write old style menu bar code to manipulate the ribbon to do things like enable or disable buttions. The syntax is very close to menu bar code and if you have places in your application that enable or disable menu bars, then this code will be of great help to convert that code to work on the ribbon.

    http://www.kallal.ca/Ribbon/ribbon.htm

    The above also lets you set lables and even change the picture with ease.

    Now that we know the Above concept for on Action, I will explain a bit further why you to use this approach:

    If the function name you specified in the on Action setting of the menu bar or now ribbon was named as public in the form's code module, then the CURRENT FORM with the CURRENT focus is where the function will be first looked for to execute. This is SIGNIFICANTLY important because it means you can use one custom menu bar for five different forms, but each of the five different forms will run a custom delete routine or whatever you want. And the code for each form remains in the form. (so, this tip is great for when over time you want to move buttons from a form up to the ribbon and keep the SAME code that the buttons on the form were calling before).

    So for example you could have a delete button on the ribbon. However you might need and want specific and specialized delete code for different forms. Well above means that for the given form that has the focus, then that's forms function code in the forms module will be run when it is called from the on action in the menu bar, or now ribbon.

    This means we could set the on action to =MyDelete()

    Then in each form you have, you simply declare a public functions such as

    Public Function MyDelete()

    code here to prompt and delete current record

    Note that you can also pass parameters right from the ribbon xml also.

    So for example, to have 3 buttons on the ribbon would launch 3 reports, we can do this with ONE function inside of the form.

    So, xml could be:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" <http://schemas.microsoft.com/office/2009/07/customui&quot;>>
    <ribbon startFromScratch="true">
    <tabs>
    <tab id="MyReport" label="Report Print and View Options">

    <group id="Reports" label="Reports">

    <button id="rp1" label="Daily Sales"
    imageMso="CreateReport" size="large"
    onAction="=myReport('rptDailySales')"
    supertip= "Daily Sales Report"
    />

    <button id="rp2" label="Daily Sales by type"
    imageMso="CreateReport" size="large"
    onAction="=myReport('rptDailySalestype')"
    supertip= "Daily Sales by type"
    />

    <button id="rp3" label="Daily Sales by location"
    imageMso="CreateReport" size="large"
    onAction="=myReport('rptDailySalesLocation')"
    supertip= "Daily Sales by location"

    />
    </group>
    </tab>
    </tabs>

    </ribbon>
    </customUI>


    The above would look like:

    <http://bxxgza.bay.livefilestore.com/y1pDI3gLDMIyaQo7yLEK_ZDjNwSgfGGligDy6mPq10SX_amrBHBwI_OtvpcEfk7qOs266DYBh9SxjP6D7vLEKehRE2g7CNQ02SO/reports.png?psid=1>

    And we then can place the ONE single function in the forms code module:


    Public Function MyReport(strReportName As String)

    DoCmd.OpenReport strReportName, acViewPreview

    End Function


    So, the above shows how one bit of code can be passed values from the ribbon (in this case the name of the report to view). Note again how there is no iribbon defs and we not using a Sub. So, it is just plain Jane simple VBA function that is called directly from the ribbon. The above advice should simplify development with the ribbon by a quite a lot.


    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com <mailto:Pleasenospam_kallal@msn.com>

    Monday, March 07, 2011 6:54 AM