Add in

  • Thread starter Thread starter PCOR
  • Start date Start date
P

PCOR

Will some please tell me in detail how to make/construct a simple add in
for excel.
or tell me where I can get some info on thi subject. I tried Microsoft but
what I found was not useful
Thanks


Norton Professional 2004 says this email is clean...believe it
 
This is an instruction set on creating an addin that I got off the net,
which I haven't test-run, but I reprint in lieu of your firewall

Create an addin in Excel 97, 2000, 2002 using VBA in Microsoft Excel

1.. Start the Visual Basic Editor by pressing Alt+F11.
2.. Select Debug, Compile VBA project name. In previous versions of Excel
the VBA-code was automatically compiled when you saved the addin. This is no
longer true, you have to do it yourself before you save the addin.
3.. If you want to lock the project from viewing you can select Tools,
Properties for VBA project name. In this dialog you activate Protection and
check the option Lock project for viewing. Fill in a password and click the
OK-button.
4.. Activate Excel by pressing Alt+F11.
5.. Select File, Properties..., Summary and fill inn information for the
fields Title and Comments. The title will be the name that appears in the
Add-Ins dialog (the dialog displaying available add-ins), the comment will
be the description that appears when you select the addin in the Add-Ins
dialog. Click the OK-button to close the Properties dialog.
6.. Select File, Save as..
7.. Change the option Filetype: to Microsoft Excel addin (*.xla) (the last
choice in the dropdown). The add-in must contain at least one worksheet if
this option is to be displayed.
8.. Click the Save-button to save the workbook as an addin. The locking of
the project will not take effect until you close and re-open the workbook.

You can convert a workbook to an addin by changing the property IsAddin to
True for the ThisWorkbook-object. This must be done from the Visual Basic
Editor. When the property is changed you can save the workbook by clicking
on the Save-toolbarbutton.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the VERY quick response.
I am new at this and thanks for the detail listed below...BUT how would I
make an addin that would print "BOB PHILLIPS is very helpful"(without the
quotes.
Thanks
 
This is a very open question, as it would be dependent upon when you want
the macro to run. For instance, you may want a toolbar button that runs the
macro, or you may want the macro to be triggered when a certain event
occurs.

The code in itself is very simple

Sub myMacro()

MsgBox "This is my macro"

End Sub

Event code, or toolbars are a bit trickier, but not frighteningly so.

Give us more details of what you want to do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Again many thanks for the speed.
My aim is to have 4 or 5 macro run from the tool bar
Let me try this:
I would write the 4 or 5 macros such as
sub mymacro1()
Print "This is macro one"
end sub
I then do the same for macro 2...and so on.
I would then go thru the process you described earlier to make the addin
I would then attahced the Add in
How would I then invoke the various macros.
If I am all wet with the above just say so
Thanks again
 
Okay,

Here is some code that will create a 5 button toolbar. You need to do the
following

- change the sToolBar value to something you want
- change the button captions, from Button 1, 2 etc to your button ids
- change the OnAction property values from macro1, etc. to your macros
- if you want button icons change the faceid property (John Walkenbach has a
utility to help you at http://j-walk.com/ss/excel/tips/tip67.htm to help
find the values of
the FaceIds), and remove the .Style property, or add .style property to al
buttons and the caption is shown on the button
- add this code toe the ThisWorkbook code module of the spreadsheet before
you save it as an addin


Option Explicit

Const sToolbar As String = "PCOR's Toolbar"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars(sToolbar).Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim cMenu1 As CommandBarControl
Dim cbToolbar As CommandBar

On Error Resume Next
Application.CommandBars(sToolbar).Delete
On Error GoTo 0

Set cbToolbar = Application.CommandBars.Add(sToolbar, , False, True)

With cbToolbar
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Button 1"
.FaceId = 1
.OnAction = "macro1"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Button 2"
.FaceId = 2
.OnAction = "macro2"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Button 2"
.FaceId = 3
.OnAction = "macro3"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Button 4"
.FaceId = 4
.OnAction = "macro4"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Button 5"
.FaceId = 5
.OnAction = "macro5"
End With
.Visible = True
.Position = msoBarTop
End With

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks AGAIN
Did all that
I then closed all and reopened excel
Attached the xla which I called WWWWWWWWWWWW.xla
Now how do I use it?
Sorry for being a little slow...must be 2004
 
If you install the addin using Tools>Addins... and then go browse and find
the .xla file, it should automatically build the toolbar. You invoke the
macros just by clicking the buttons.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Just a thought, but if all your macros are doing is putting text in, then why
not check out the autocorrect options. That way you can any text you like to
say ac1 ac2 ac2 ac4 etc, and then whenevr you type one of thos ein a cell, it
will replace it with the appropriate text.
 
Back
Top