Code to Add-In Help

swolfe0124

New Member
Joined
Apr 11, 2013
Messages
23
Hello Everyone,
I'm having a bit of problem converting a pagebreak code that I wrote into an add-in. my issue is that when i save it as an add-in and send it to someone they can activate it; however, it doesn't do anything. It does not show up anywhere or anything..... this is my first time working with add-ins so any/all help is WELCOME and APPRECIATED!!!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
You should send them a physical copy of the add-in, and they should store that in a permanent place on their PC. Then they need to go in to the Add-in Manager in Excel and browse to your add-in and activate it (Options, Add-Ins, Manage Add-Ins)
 

swolfe0124

New Member
Joined
Apr 11, 2013
Messages
23
Thank you ChrisM for the quick response. That part I'm ok with. What i'm having a problem with is writing in a code that will create a command button of sorts upon opening and then deleting it upon closing. I've come up with a code but now I'm getting an error message upon using it that says Object Variable or with Block Variable not set......
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Oh ok, thought you had an add-install question. Can't help until you post some code to look at, you're not giving too much to go on.
 

swolfe0124

New Member
Joined
Apr 11, 2013
Messages
23

ADVERTISEMENT

Sorry, i'm really new to this. listed below is my code for the command bar up and closing..

' Constants
Private Const COMMANDBAR_NAME As String = "Variable"
Private Const BUTTON_CAPTION As String = "Variable"
' Open
Private Sub Workbook_Open()
' Variables
Dim objCommandBar As CommandBar
Dim objButton As CommandBarButton
' Try to get the Commandbar (if it exists)
On Error Resume Next
Set objCommandBar = Me.CommandBars(COMMANDBAR_NAME)
On Error GoTo 0

' Was the commandbar available?
If (objCommandBar Is Nothing) Then
' Create the commandbar
On Error Resume Next
Set objCommandBar = Application.CommandBars.Add(Name:=COMMANDBAR_NAME, Position:=msoBarTop, Temporary:=True)
On Error GoTo 0

' Valid commandbar?
If (Not objCommandBar Is Nothing) Then
' Add the buttons to the command bar
With objCommandBar
' Add button
Set objButton = objCommandBar.Controls.Add(Type:=msoControlButton, Temporary:=True)

' Set the button properties
With objButton
.Style = msoButtonIconAndCaption
.Caption = BUTTON_CAPTION
.FaceId = 4
.TooltipText = "ThisWorkbook.macroname"
.OnAction = "ThisWorkbook.macroname"
End With

' Show the command bar
.Visible = True
End With
End If
End If
End Sub
' Before Close
Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
' Try to remove the iTrade command bar
Call Application.CommandBars(COMMANDBAR_NAME).Delete

' Restore error handling
On Error GoTo 0
End Sub
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Oh, this is a command bar question, I thought you were saying command button earlier. I am not a super expert on command bars in add-ins, though my personal add-in does have one. Here's how mine works, but again keep in mind I don't claim to be an expert in this area.

My add-in has two subs:

Sub Auto_Open()

Sub Auto_Close()

I don't use the Workbook_Open and Close events like you have.

Code:
Sub Auto_Open()

Dim cCustomMenu As CommandBarControl
Dim cMenuItem As CommandBarControl

'add new custom menu
Set cCustomMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)

With cCustomMenu
    .BeginGroup = True
    .Caption = "MyMenu"
End With

'add menu items
Set cMenuItem = cCustomMenu.Controls.Add
With cMenuItem 'paste special
    .Caption = "Paste Special: CTRL-A"
    '.Style = msoCaption
    .OnAction = "'" & ThisWorkbook.Name & "'!PasteSpecial"
End With
End Sub

Code:
Sub Auto_Close()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Delete the controls.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Dim ctrl As CommandBarControl

For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls
    If ctrl.Caption = "MyMenu" Then ctrl.Delete
Next ctrl

End Sub

That is code for a custom menu, not a command bar, but hopefully that gets you pointed in the right direction.
 

swolfe0124

New Member
Joined
Apr 11, 2013
Messages
23
Thanks! I'll try and play around with this one. If anyone can figure out why i was receiving the error message when running my original one it would be greatly appreciated!! hate to create something and it not work properly :)
 

swolfe0124

New Member
Joined
Apr 11, 2013
Messages
23
Heads Up! I was receiving this error because my macro was saved in ThisWorkbook and not a module. Moved it to a Module and it worked PERFECT!!! thank you for all your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top