Home / ribbon excel

Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet

Post a Comment
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
  5. Klik 2x ThisWorkbook dan masukan Kode Macro dibawah ini
    6. 
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
  6. Simpan dengan nama Menampilkan Menu Ribbon Excel Sesuai Pilihan Sheet
  7. Untuk type nya silahkan pilih Excel Binary Workbook (.xlsb)
  8. Kemudian Close Excel
  9. Buka Software Custom UI Editor
  10. Open folder dengan cara klik folder warna kuning kemudian pilih file excel tadi
  11. Klik Kanan pilih Office 2010 Custom UI Part dan Office 2007 Custom UI Part
  12. Copy Kode dibawah ini kedalam Office 2010 Custom UI Part
    13. 
<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>
  13. Copy Kode dibawah ini kedalam Office 2007 Custom UI Part
    14. 
<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>
  14. Untuk mengecek kevalidan Kode XML, silahkan klik tombol Validate
  15. Jika muncul vbInformation maka, kode XML sudah benar
  16. Simpan dan Close
  17. Buka file excel tadi dan lihat hasilnya akan seperti gambar paling atas
  18. 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"