Re: Create a Ribbon with the Custom UI Editor

Hello,
I´ve got a problem...

I am absolutly a beginner in programing :(

I want to create a new Ribbon in an Excel 2007 file and link this with a Macro in VBA
I downloaded the Custon UI Editor on

http://openxmldeveloper.org/articles/customuieditor.aspx

The Makro - name is

Sub SKBudgetUebernahme() . This Macro is in a modul (standard) which means ProgPCenter

Now my Question:

What I have to to, when I want to change the buootons on the Ribbon and create a new Button, which is linkt with the Macro "Sub SKBudgetUebernahme()"

How must I rename it, so that the Button knows, where the Macro is ?

→ The whole Macro is that:

Sub SKBudgetUebernahme()

    ' Dim DateVon As Datestruct, DateBis As Datestruct
    Dim sql As String
    Dim db As Database, ws As Workspace
    Dim xl As Object
    Dim qt As QueryTable
    Dim val As Double
    Dim b_id As Integer
    Dim blatt As String
    Dim wks As Worksheet
    Dim iwks As Integer
  

    Application.Calculation = xlManual
    ' SetWaitCursor
   

   
    ' Wird dann vom User per Dialog abgefragt
    ' Dim odbc_uid As String
    ' Dim odbc_passwort As String
    ' Dim Odbc_dsn As String
   
    iwks = ActiveSheet.Index
    blatt = Worksheets(iwks).Name
    ' blatt = wks.Name

    Set ws = CreateWorkspace("ODBCWorkspace", "fusion", "", dbUseODBC)
    Workspaces.Append ws
    ' Der Name der ODBC-Verbindung ist in der Exceltabelle 'fest' hinterlegt (Bei Eintragen der Query Abfrage)
    ' Set conn = ws.OpenConnection("Conn_01", dbDriverPrompt)
    Set conn = ws.OpenConnection("av156_qs", dbDriverPrompt)
   
    Application.StatusBar = "Übertrage Planzahlen nach Avista für " ' & Sheets("Datenbank").Cells(Datum.monat, 2) & " " & Datum.jahr
    b_id = Sheets(blatt).Range(blatt + "!Budget_Nummer")            ' Budgetnummer
    If (b_id > 0) Then
        CopyPlanZahlen blatt, b_id
    End If
    Application.Calculation = xlAutomatic
    Application.StatusBar = False
    conn.Close
    ws.Close
End Sub


→ And the code in the Custom UI Editor looks like that:

<!-- This is example :  XL-Custom tab for your favorite macros Part 2   -->


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <!-- Add Custom tab to the ribbon with your favorite buttons-->
  <!-- The example add five groups to the new tab -->
  <!-- On the third group there is a menu with five options-->
  <!-- On the fourth group there is a splitbutton example-->
  <!-- On the fifth group there are button groups (the last one with built-in buttons)-->
  <!-- Click on the "generate Callbacks" button in the UI editor to create the macro(s)-->
  <!-- You can copy them in a module in your workbook then and add your code-->

  <ribbon>
    <tabs>
      <tab id="MyCustomTab" label="Übergabe" insertAfterMso="TabHome">
                                           
        <group id="customMakro">
          <button id="customButton1" label="Hier Klicken!" size="normal" onAction="SKBudgetUebernahme()" imageMso="DirectRepliesTo" />       
      </group>

      
       
      
   
      </tab>
    </tabs>
  </ribbon>

</customUI>

Ich thank you for your help !!!
Please help me !

Yours sincerely, Lambo

Hi Lambo

Have a look at http://msdn.microsoft.com/en-us/aa338202%28office.12%29.aspx - this provides a good explanation of how to implement these.

I haven't done this myself, but I can't help but notice that their button's onAction value is prefixed with ThisDocument for word or ThisWorkbook for spreadsheets.

Provided your macro is set up correctly I would think you can simply have:
<button id="customButton1" label="Hier Klicken!" size="normal" onAction="ThisDocument.SKBudgetUebernahme" imageMso="DirectRepliesTo" />