Create Excel addin

[Erays]

How would I create the addin and have the option added to the tools menu?

Using the vba below


Sub ChangeCellRefs()
Dim strw As String
Dim myRng As Range
Dim c As Range
strw = " You may want to save your work to a new file."
strw = strw & " Press OK to Add the $ to the Formulas, Cancel to quit."
If vbCancel = MsgBox(strw, vbOKCancel + vbCritical, "Task Cannot Be Undone") Then Exit Sub
Set myRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
For Each c In myRng
'Application.ConvertFormula Formula:=c.Formula, fromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute
c.Formula = Application.ConvertFormula(Formula:=c.Formula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next
End Sub

[Killian]

Hi there :hi:

You need to add two more routines to your module. One to add the menu item and one to delete it:


Sub BuildMyMenuItem()
Dim newCtrl As CommandBarControl
Set newCtrl = Application.CommandBars("Worksheet Menu Bar") _
.Controls("Tools").Controls.Add
newCtrl.Caption = "Change Cell Refs"
newCtrl.OnAction = "ChangeCellRefs"
End Sub

Sub DeleteMyMenuItem()
Dim ctrl As CommandBarControl
For Each ctrl In Application.CommandBars("Worksheet Menu Bar") _
.Controls("Tools").Controls
If ctrl.Caption = "Change Cell Refs" Then ctrl.Delete
Next
End Sub


Then go to the code window for the WorkBook_Open event and call DeleteMyMenuItem (to get rid of any existing instances) and then call BuildMyMenuItem to add it. You'll see that the action it calls is your original routine. Then go to the WorkBook_Close event and call DeleteMyMenuItem.

That should keep the menu side of things organized. Now, go into Excel and save the workbook as an addin (.xla extension). Then, back in the VBEditor, go to the project explorer, select the "ThisWorkbook" item and change the "IsAddIn" property to true then click the save icon and close out of Excel.
Put the .xla file into your XLSTART folder (something like "C:\Program Files\Microsoft Office\Office10\XLStart", depending on how you installed Office and what version you have.

And that should do it. Next time you start Excel, your menu item should be available!

[Zack Barresse]

And you could even shorten the Deletion procedure to 2 lines ...


Sub DeleteMyMenuItem()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Change Cell Refs").Delete
End Sub

[Killian]

It's true Zack, but I like to encourage the use of COLLECTIONS where I can
:yes :rotlaugh: :yes

[Zack Barresse]

It's true Zack, but I like to encourage the use of COLLECTIONS where I can
:yes :rotlaugh: :yes

ROFL!! :D

EXCELLENT POINT!!! A great way to learn!! :yes