Code to Add-In Help

[swolfe0124]

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!!!

 

[ChrisM]

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]

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]

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]

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]

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 Autpen()

Sub Auto_Close()

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

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

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]

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]

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!