Creating Custom Menus in Power Point

A very good link to learn about Powerpoint.

http://www.bettersolutions.com/powerpoint/PBZ143/LE824411712.htm

Thanks,
Vikas

Adding an item in Right Click Menu.


Option Explicit

Private Sub Workbook_Open()
    Call addButton
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call addButton
End Sub

Sub addButton()
Dim cBut
On Error Resume Next
    Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
    Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
    With cBut
        .Caption = "RighClickMenu"
        .Style = msoButtonCaption
        .OnAction = "MacroName"
    End With
End Sub


Someone asked for help for creating a submenu in the right click menu. For example, An item like ColorRange in the right click pop up menu, and if you go over it, then it gives option to color the cells.

Following is the code for that:

Write the following code in the workbooks open method to add the item automatically once you open the workbook.



Sub addButton()
Dim cBut As CommandBarPopup
Dim pic As PictureFormat

On Error Resume Next
Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, temporary:=True)

With cBut
.Caption = "Color Menu"
End With

Dim red As CommandBarButton
Dim blue As CommandBarButton
Dim green As CommandBarButton
Dim black As CommandBarButton

Set red = cBut.Controls.Add(temporary:=True)
Set blue = cBut.Controls.Add(temporary:=True)
Set green = cBut.Controls.Add(temporary:=True)
Set black = cBut.Controls.Add(temporary:=True)

red.Caption = "Red"
blue.Caption = "Blue"
green.Caption = "Green"
black.Caption = "Black"
red.Picture = stdole.LoadPicture("c:\red.bmp")
green.Picture = stdole.LoadPicture("c:\green.bmp")
black.Picture = stdole.LoadPicture("c:\black.bmp")
blue.Picture = stdole.LoadPicture("c:\blue.bmp")
red.OnAction = "RedColor"
blue.OnAction = "blueColor"
green.OnAction = "greenColor"
black.OnAction = "blackColor"
End Sub



Private Sub Workbook_Open()
Call addButton
End Sub




Once you are done with the above code, then create a new module and paste the following code in the newly created module.




Enum Color
red
blue
green
black
End Enum


Sub RedColor()
ColorCell (Color.red)
End Sub
Sub BlueColor()
ColorCell (Color.blue)
End Sub
Sub GreenColor()
ColorCell (Color.green)
End Sub
Sub BlackColor()
ColorCell (Color.black)
End Sub

Sub ColorCell(clr As Color)

Dim rng As Range
Dim sht As Worksheet
Dim typNme As String

typNme = TypeName(Application.Selection)

Select Case (typNme)
Case "Range"
Set rng = Application.Selection

Select Case (clr)
Case Color.red
rng.Interior.Color = ColorConstants.vbRed
Case Color.black
rng.Interior.Color = ColorConstants.vbBlack
Case Color.blue
rng.Interior.Color = ColorConstants.vbBlue
Case Color.green
rng.Interior.Color = ColorConstants.vbGreen
End Select
End Select



End Sub

Creating Excel Menu from Dot net 2003

using System;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace ConsoleApplication3
{

class Class1
{
///

/// Summary description for Class1.
///

private Application excel= new ApplicationClass();
private Workbook book= null;
private Worksheet sheet = null;
private CommandBar MainMenuBar = null;
private CommandBarControl MenuBarItem = null;
//private CommandBarButton MenuItem = null;
public Class1()
{
book = excel.Workbooks.Open("C:\\log.xls",0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
InitMenuBarItems("My Main Menu");
excel.Visible=true;
sheet = (Worksheet)book.Sheets.get_Item(1);
Console.WriteLine("The file name is {0}",sheet.Name);
Console.ReadLine();
}

private void InitMenuBarItems(string Caption)
{
try
{
MainMenuBar =
excel.CommandBars["Worksheet Menu Bar"];
MenuBarItem = MainMenuBar.Controls.Add(
MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, Type.Missing, true);
MenuBarItem.Caption = Caption;

}
catch
{

}
}

[STAThread]
static void Main(string[] args)
{
Class1 cls = new Class1();

}
}
}

Create and register ADDin to Use in Excel

namespace NAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
}

public double Add2(double v1, double v2)
{
return v1 + v2;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}
}
}

Build the NAddIn project to create bin\debug\NAddIn.dll.
Test the Add-In in Excel:
Open a new workbook in Excel.
Select Tools, Add-Ins, Automation.
NAddIn.Functions should be listed - select it. OK.
In a cell, type =Add2(3,4)
The cell should display 7.
To register the .dll after moving it, run regasm with the /codebase flag (typically as c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase NAddIn.dll). You will get a warning about the assembly being unsigned - you can ignore this (or sign the assembly as documented).