Code Snippets


Public Sub ListToolbars() 
Dim icount As Integer
   For icount = 1 To CommandBars.Count
      Debug.Print CommandBars.Item(icount).Name
      
      If CommandBars.Item(icount).Name = " " Then
'CommandBars.Item(icount).Protection = msoBarNoProtection
         CommandBars.Item(icount).Enabled = False
      End If
   Next icount
End Sub

You can get a run-time error when hiding and showing a command bar using the visible property, because this property does not apply to all types of commandbars.


Allows you to select a particular tab

IRibbonUI.ActivateTab("mycustomtab") 
IRibbonUI.ActivateTabMso("builtintab")
IRibbonUI.ActivateTabQ("sharedtab")


Set cbobj = Application.CommandBars("Worksheet menu bar") _ 
   .Find Control(Type:=msoControlPopup, Tag:="yourtag")

If cbobj Is Nothing Then
   Call MsgBox("Not Found!")
End If

Creating a shortcut menu

Set mybar = CommandBar.Add("myshortcut", msoBarPopUp, True) 


Enable all toolbars

Public Sub EnableAllToolbars() 
Dim cb As CommandBar
   For Each cb In CommandBars
      If cb.Type = msoBarTypeNormal Then
         cb.Enabled = True
      End If
   Next cb
End Sub


CommandBars("MonthList").Delete 

Running a macro from a mouse over

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, _ 
                          ByVal Shift As Integer, _
                          ByVal x As Single, _
                          ByVal Y As Single)
   commandbutton1.backcolor = RGB(10,10,10)
End Sub

Creating a new toolbar

Set TBar = CommandBars.Add 
With TBar
    .Name = "MonthList"
     .Visible = True
End With

Adding a custom graphic to a button

Dim oShape As Shape 
Set oShape = Activeworkbook.Shapes.AddPicture(FileName:="C;\temp\image.gif")
oShape.Select
Selection.CopyPicture
Selection.Delete
CommandBars("Standard").Controls(3).PasteFace
set oShape = Nothing

Disabling a specific shortcut

CommandBars("Toolbar List").Enabled = False 

Disabling all the right click shortcut menus

Dim cb As CommandBar 
   For Each eb In CommandBars
      If cb.Type = msoBarTypePopUp Then cb.Enabled = False
   Next cb

Adding a button to the standard toolbar ?

Set xx = CommandBars("Standard").Controls.Add(msoControlButton, 3) 
xx.OnAction := "Macro_name"
xx.FaceId := 893

Adding a checkmark

CommandBars(1).Commands("MyMenu").Commands("Item").state = msoButtonDown 

Adding a dropdown control

Set NewDD = CommandBars("MonthList").Controls.Add(Type:=msoControlDropdown) 
.Style = msoButtonAutomatic ????
FILL IN SOME PROPERTIES
CommandBars("MonthList").Controls("DateDD").ListIndex = i

Restoring Defaults

For Each tb In CommandBars 
   If cb.Type = msoBarTypeNormal Then
      If cb.BuiltIn = True Then cb.Reset
   End If
Next cb

Public Sub ListControlsIds() 
   Dim cbar As Office.CommandBar
   Dim ctl As CommandBarControl
   Dim iid As Integer

   Application.CommandBars("temporary").Delete
      Set cbar = Application.CommandBars.Add("temporary", msoBarTop, False, True)
   
   For iid = 1 To 4000
      On Error Resume Next
      Set ctl = cbar.Controls.Add(ID:=iid)
   Next iid
   
   iid = 1
   For Each ctl In cbar.Controls
      Cells(2 + iid, 2).Value = ctl.Caption
      Cells(2 + iid, 3).Value = ctl.ID
      iid = iid + 1
   Next ctl
   
   cbar.Delete
End Sub



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext