Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet

Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet - Menggunakan Software Custom UI Editor for Office memang mudah-mudah sulit, karena terkadang kita sudah membuat kodenya tiba2 hilang. Oleh sebab itu, ada yang menganjurkan untuk memakai AddIns RibbonX.

Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet


Ikuti dan terapkan langkah-langkah dibawah ini dengan tepat :

  1. Buka Excel
  2. Buat 5 buah sheet seperti berikut :
    • Sheet 1 Rename menjadi HideAll
    • Sheet 2 Rename menjadi ShowAll
    • Sheet 3 Rename menjadi Menu1
    • Sheet 4 Rename menjadi Menu2
    • Sheet 5 Rename menjadi Menu3
  3. Tekan Alt+F11 untuk masuk ke jendela VBA Editor
  4. Buatlah 1 buah module dan masukan Kode Macro dibawah ini kedalam module tersebut
  5. 
    Option Explicit
    
    Dim Ribbon As IRibbonUI
    Dim MyTag As String
    
    'Callback CustomUI
    Sub OnRibbonLoad(dolanan As IRibbonUI)
        Set Ribbon = dolanan
    End Sub
    
    Sub GetVisible(control As IRibbonControl, ByRef returnedVal)
        If control.Tag Like MyTag Then
            returnedVal = True
        Else
            returnedVal = False
        End If
    End Sub
    
    Sub RefreshRibbon(Tag1 As String)
        MyTag = Tag1
        If Ribbon Is Nothing Then
            MsgBox "sistem mengalami error, restart your workbook", vbCritical
        Else
            Ribbon.Invalidate
        End If
    End Sub
    
    'Callback onAction
    Sub Macro1(control As IRibbonControl)
        MsgBox "Tombol 1" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro2(control As IRibbonControl)
        MsgBox "Tombol 2" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro3(control As IRibbonControl)
        MsgBox "Tombol 3" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro4(control As IRibbonControl)
        MsgBox "Tombol 4" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro5(control As IRibbonControl)
        MsgBox "Tombol 5" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro6(control As IRibbonControl)
        MsgBox "Tombol 6" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro7(control As IRibbonControl)
        MsgBox "Tombol 7" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro8(control As IRibbonControl)
        MsgBox "Tombol 8" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro9(control As IRibbonControl)
        MsgBox "Tombol 9" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro10(control As IRibbonControl)
        MsgBox "Tombol 10" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro11(control As IRibbonControl)
        MsgBox "Tombol 11" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro12(control As IRibbonControl)
        MsgBox "Tombol 12" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro13(control As IRibbonControl)
        MsgBox "Tombol 13" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro14(control As IRibbonControl)
        MsgBox "Tombol 14" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro15(control As IRibbonControl)
        MsgBox "Tombol 15" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro16(control As IRibbonControl)
        MsgBox "Tombol 16" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro17(control As IRibbonControl)
        MsgBox "Tombol 17" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro18(control As IRibbonControl)
        MsgBox "Tombol 18" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro19(control As IRibbonControl)
        MsgBox "Tombol 19" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro20(control As IRibbonControl)
        MsgBox "Tombol 20" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro21(control As IRibbonControl)
        MsgBox "Tombol 21" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro22(control As IRibbonControl)
        MsgBox "Tombol 22" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
    Sub Macro23(control As IRibbonControl)
        MsgBox "Tombol 23" & vbCrLf & "www.dolananexcel.id", vbInformation
    End Sub
    
  6. Klik 2x ThisWorkbook dan masukan Kode Macro dibawah ini
  7. 
    Option Explicit
    
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        Select Case Sh.CodeName
        Case "Sheet1": Call RefreshRibbon(Tag1:="*")
        Case "Sheet2": Call RefreshRibbon(Tag1:="Menu1")
        Case "Sheet3": Call RefreshRibbon(Tag1:="Menu2")
        Case "Sheet4": Call RefreshRibbon(Tag1:="Menu3")
        Case Else: Call RefreshRibbon(Tag1:="")
        End Select
    End Sub
    
  8. Simpan dengan nama Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet
  9. Untuk type nya silahkan pilih Excel Binary Workbook (.xlsb)
  10. Kemudian Close Excel
  11. Buka Software Custom UI Editor
  12. Open folder dengan cara klik folder warna kuning kemudian pilih file excel tadi
  13. Klik Kanan pilih Office 2010 Custom UI Part dan Office 2007 Custom UI Part
  14. Copy Kode dibawah ini kedalam Office 2010 Custom UI Part
  15. 
    <customUI onLoad="OnRibbonLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="true">
    <tabs>
    <tab id="tab1" label="Menu1" getVisible="GetVisible" tag="Menu1" >
     <group id="Group1" label="Menu1 Group">
      <button id="Button1" label="Caption 1" size="large" onAction="Macro1" image="menu1" />  
      <button id="Button2" label="Caption 2" size="large" onAction="Macro2" image="menu1" />
     </group>
     <group id="Group2" label="Menu1 Group">
      <button id="Button3" label="Caption 3" size="large" onAction="Macro3" image="menu1" />
      <separator id="Separator1" />
      <button id="Button4" label="Caption 4" size="normal" onAction="Macro4" image="menu1" />  
      <button id="Button5" label="Caption 5" size="normal" onAction="Macro5" image="menu1" />
      <button id="Button6" label="Caption 6" size="normal" onAction="Macro6" image="menu1" />
     </group>
    </tab>
    
    <tab id="Tab2" label="Menu2" getVisible="GetVisible" tag="Menu2" >
     <group id="Group3" label="Menu1 Group">
      <button id="Button7" label="Caption 7" size="large" onAction="Macro7" image="menu2" />
      <separator id="Separator2" />
      <button id="Button8" label="Caption 8" size="normal" onAction="Macro8" image="menu2" />  
      <button id="Button9" label="Caption 9" size="normal" onAction="Macro9" image="menu2" />
      <button id="Button10" label="Caption 10" size="normal" onAction="Macro10" image="menu2" />
     </group>
     <group id="Group4" label="Menu1 Group">
      <button id="Button11" label="Caption 11" size="large" onAction="Macro11" image="menu2" />  
      <button id="Button12" label="Caption 12" size="large" onAction="Macro12" image="menu2" />
     </group>
    </tab>
    
    <tab id="Tab3" label="Menu3" getVisible="GetVisible" tag="Menu3" >
     <group id="customGroup5" label="Menu1 Group">
      <button id="Button13" label="Caption 13" size="large" onAction="Macro13" image="menu3" />  
      <button id="Button14" label="Caption 14" size="large" onAction="Macro14" image="menu3" />
     </group>
     <group id="Group6" label="Menu1 Group">
      <button id="Button15" label="Caption 15" size="large" onAction="Macro15" image="menu3" />
      <separator id="Separator3" />
      <button id="Button16" label="Caption 16" size="normal" onAction="Macro16" image="menu3" />  
      <button id="Button17" label="Caption 17" size="normal" onAction="Macro17" image="menu3" />
      <button id="Button18" label="Caption 18" size="normal" onAction="Macro18" image="menu3" />
     </group>
     <group id="Group7" label="Menu3 Group">
     <menu id="DropdownMenu" label="My Menu" size="large" image="menu3"  >
      <button id="Button19" label="Caption 19"  onAction="Macro19" image="menu3" />
      <button id="Button20" label="Caption 20"  onAction="Macro20" image="menu3" />
      <button id="Button21" label="Caption 21"  onAction="Macro21" image="menu3" />
      <button id="Button22" label="Caption 22"  onAction="Macro22" image="menu3" />
      <button id="Button23" label="Caption 23"  onAction="Macro23" image="menu3" />
     </menu>
     </group>
    </tab>
    </tabs>
    </ribbon>
    </customUI>
  16. Copy Kode dibawah ini kedalam Office 2007 Custom UI Part
  17. 
    <customUI onLoad="OnRibbonLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="true">
    <tabs>
    <tab id="tab1" label="Menu1" getVisible="GetVisible" tag="Menu1" >
     <group id="Group1" label="Menu1 Group">
      <button id="Button1" label="Caption 1" size="large" onAction="Macro1" image="menu1" />  
      <button id="Button2" label="Caption 2" size="large" onAction="Macro2" image="menu1" />
     </group>
     <group id="Group2" label="Menu1 Group">
      <button id="Button3" label="Caption 3" size="large" onAction="Macro3" image="menu1" />
      <separator id="Separator1" />
      <button id="Button4" label="Caption 4" size="normal" onAction="Macro4" image="menu1" />  
      <button id="Button5" label="Caption 5" size="normal" onAction="Macro5" image="menu1" />
      <button id="Button6" label="Caption 6" size="normal" onAction="Macro6" image="menu1" />
     </group>
    </tab>
    
    <tab id="Tab2" label="Menu2" getVisible="GetVisible" tag="Menu2" >
     <group id="Group3" label="Menu1 Group">
      <button id="Button7" label="Caption 7" size="large" onAction="Macro7" image="menu2" />
      <separator id="Separator2" />
      <button id="Button8" label="Caption 8" size="normal" onAction="Macro8" image="menu2" />  
      <button id="Button9" label="Caption 9" size="normal" onAction="Macro9" image="menu2" />
      <button id="Button10" label="Caption 10" size="normal" onAction="Macro10" image="menu2" />
     </group>
     <group id="Group4" label="Menu1 Group">
      <button id="Button11" label="Caption 11" size="large" onAction="Macro11" image="menu2" />  
      <button id="Button12" label="Caption 12" size="large" onAction="Macro12" image="menu2" />
     </group>
    </tab>
    
    <tab id="Tab3" label="Menu3" getVisible="GetVisible" tag="Menu3" >
     <group id="customGroup5" label="Menu1 Group">
      <button id="Button13" label="Caption 13" size="large" onAction="Macro13" image="menu3" />  
      <button id="Button14" label="Caption 14" size="large" onAction="Macro14" image="menu3" />
     </group>
     <group id="Group6" label="Menu1 Group">
      <button id="Button15" label="Caption 15" size="large" onAction="Macro15" image="menu3" />
      <separator id="Separator3" />
      <button id="Button16" label="Caption 16" size="normal" onAction="Macro16" image="menu3" />  
      <button id="Button17" label="Caption 17" size="normal" onAction="Macro17" image="menu3" />
      <button id="Button18" label="Caption 18" size="normal" onAction="Macro18" image="menu3" />
     </group>
     <group id="Group7" label="Menu3 Group">
     <menu id="DropdownMenu" label="My Menu" size="large" image="menu3"  >
      <button id="Button19" label="Caption 19"  onAction="Macro19" image="menu3" />
      <button id="Button20" label="Caption 20"  onAction="Macro20" image="menu3" />
      <button id="Button21" label="Caption 21"  onAction="Macro21" image="menu3" />
      <button id="Button22" label="Caption 22"  onAction="Macro22" image="menu3" />
      <button id="Button23" label="Caption 23"  onAction="Macro23" image="menu3" />
     </menu>
     </group>
    </tab>
    </tabs>
    </ribbon>
    </customUI>
    
  18. Untuk mengecek kevalidan Kode XML, silahkan klik tombol Validate
  19. Jika muncul vbInformation maka, kode XML sudah benar
  20. Simpan dan Close
  21. Buka file excel tadi dan lihat hasilnya akan seperti gambar paling atas
  22. Simpan dan Selesai

Jika Anda melakukan langkah-langkah diatas dengan benar dan penempatan kode juga benar maka, sistem akan berjalan sesuai intruksi yang Anda berikan melalui kode XML dan kode Macro diatas.

Catatan : Icon Menu Ribbon Silahkan di Ganti Sesuai Selera Anda.
Artikel Menarik : Download Icon Ribbon Excel Full Version (png, bmp, ico, gif)

File Penerapan


Kami juga memikirkan pemahaman dari semua pengunjung, sehingga selain artikel, kami juga memberikan file penerapan untuk pemahaman mendalam.


Nama File Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet
Extensi .xlsm
Size 42 kb
Berkas

Terimakasih sudah berkunjung :)

Bantu kami meningkatkan kualitas blog ini dengan mengisi survei 1 menit.

©2017| dolananexcel

Post a Comment for "Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet"