execute an Add In

[Blake 7]

Hi Guys.

I have tried to create an add in >

Open Workbook > Developer > Visual Basic >

Sub move()
Columns("A:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

Save as addin in Addins folder

I located in and ticked it in addins available

I then opened the book which I wanted to run it on and zip, zero nada! do i need some sort of execute code?

Thanks alot for looking

[tigeravatar]

Blake 7,

An add-in is basically a hidden workbook. So the code needs to be told to run on a different workbook otherwise it will run on its own, hidden workbook and you won't see anything happen like you've described. If you want its code to run on the currently open workbook, the code should be told to run on the ActiveWorkbook. Like so:

Sub move()
    ActiveWorkbook.ActiveSheet.Columns("A:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

Hope that helps,
~tigeravatar

[Blake 7]

Hey tiger - thanks thats great ! you have really saved me a ball ache after an hour of googling!! thanks bud

[Blake 7]

Hi - there folks.

I went to the addin folder and inserted

Sub move()
    ActiveWorkbook.ActiveSheet.Columns("A:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

in This Workbook

Saved and closed, but the only way i can get it to work is by Developer tab > Visual Basic > hitting the execute button.

I was under the impression that i could run an add in from the add in box by checking it and pressin ok???

Anyone know what i'm doing wrong???

cheers

[Colin Legg]

Hi,

I was under the impression that i could run an add in from the add in box by checking it and pressin ok???

xla and xlam are effectively workbooks with all of the sheets hidden (as Tiger mentioned). When you tick it in the add-in box and click OK, it installs the add-in. For xla and xlam, "installing the add-in" basically means that the add-in workbook is opened in the Excel instance: the only code that would run in the add-in at this stage would be like these:

• Workbook_Open()
• Workbook_AddinInstall()
• Auto_Open()

Once the add-in is opened, it means that the code within it, such as your Move procedure, will be available for use. A lot of people design an interface - such as a commandbar with buttons on - which is created when the add-in is opened, so that people can easily run the bit of code they want.

[Blake 7]

Hi Colin - thanks alot for that.

I now understand that I need to create this cmd button for my colleagues so as that they can run the macro.

Will start googling "command bar for add-in's!! looks like i'll miss dinner tonight!!

Cheers Colin for the detailed explanation!

[tigeravatar]

Blake 7,

Sorry I didn't get back to this thread sooner. In the .xla file, in the ThisWorkbook event module, you can use the following to create a button:

Private Sub Workbook_AddinInstall()

    On Error Resume Next

    'First thing the code does is remove the button if it hasn't been removed already
    Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

    'The above line results in an error if it has already been removed
    'So this On Error Resume Next ignores if there's an error and just resumes starting below

    Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
    With MyMacro
        .Caption = "My Macro Button Name"
        .Style = msoButtonCaption
        .OnAction = "MyMacro" 'In your case this would be "move"
    End With

End Sub

Of course, only having one-way functionality gives your addin a bad image. So you can use the AddinUninstall event to remove your button so that uninstallation is seamless and painless for the end user:

Private Sub Workbook_AddinUninstall()
    
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete
    
End Sub

Alternately, instead of using a button they press, you could simply put in a line to assign your add-in macro to a keyboard shortcut. This method doesn't require either of the above, but without the button its not apparent how to get the macro to run. So the following is only recommended if you're the one using it, not others:

Private Sub Workbook_Open()

    On Error Resume Next
    Application.OnKey "^{e}", "MyMacro"    'Assigns the macro to keyboard shortcut ctrl+e

End Sub

Hope that helps,
~tigeravatar

[Blake 7]

EDit: I couldn't wait! my wife has given me permission to stay up!!

Hey tiger (can't believe that i'm calling a bloke "tiger"!) - thanks v much for your comprehensive response. it's 21:35 here so will try your approach tomorrow morning and let you know how I get on! am looking forward to it!

cheers dude

[Blake 7]

Hi --

My ThisWorkbook now looks like this.

Sub move()
ActiveWorkbook.ActiveSheet.Columns("A:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

Private Sub Workbook_AddinInstall()

    On Error Resume Next

    'First thing the code does is remove the button if it hasn't been removed already
    Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

    'The above line results in an error if it has already been removed
    'So this On Error Resume Next ignores if there's an error and just resumes starting below

    Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
    With MyMacro
        .Caption = "My Macro Button Name"
        .Style = msoButtonCaption
        .OnAction = "MyMacro" 'In your case this would be "move"
    End With

End Sub

Private Sub Workbook_AddinUninstall()
    
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete
    
End Sub

So now i need to find out how to create a cmd button and where to put it!!! cheers!

[Blake 7]

Hi Guys - update time!

I applied the following code to a workbook and saved as an add-in. I then checked it on the addins available bit and got the new addins tab on my ribbon with the my macro button name.
The problem occurs when i hit the button - i get the msg cannot run the macro moveXLAMMy Macro The Macro may not be available in this workbook or Macros may be disabled.

I have them enabled so it must be the first one!

Can anyone suggest a way out or what i have done wrong. Its the first time I have tried this so the water is a little (alot) murky!

Cheers

Sub move()
ActiveWorkbook.ActiveSheet.Columns("B:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

Private Sub Workbook_AddinInstall()

On Error Resume Next

'First thing the code does is remove the button if it hasn't been removed already
Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

'The above line results in an error if it has already been removed
'So this On Error Resume Next ignores if there's an error and just resumes starting below

Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "My Macro Button Name"
.Style = msoButtonCaption
.OnAction = "MyMacro" 'In your case this would be "move"
End With

End Sub

Private Sub Workbook_AddinUninstall()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

End Sub

[Blake 7]

Hi Guys - am having great difficulty here in spite of the kind help i have already recieved from Tiger and Colin yet I am still not able to achieve my desired result which is:

To run this code

Sub move()
    ActiveWorkbook.ActiveSheet.Columns("A:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

Via a command button on the ribbon.

I need to set this up for collegues.

Sub move()
ActiveWorkbook.ActiveSheet.Columns("B:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

So far I have entered this code in ThisWorkbook

Private Sub Workbook_AddinInstall()

On Error Resume Next

'First thing the code does is remove the button if it hasn't been removed already
Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

'The above line results in an error if it has already been removed
'So this On Error Resume Next ignores if there's an error and just resumes starting below

Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "My Macro Button Name"
.Style = msoButtonCaption
.OnAction = "MyMacro" 'In your case this would be "move"
End With

End Sub

Private Sub Workbook_AddinUninstall()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("MyMacro").Delete

End Sub

But am getting an error message "The Macro may not be available in this workbook or Macros may be disabled"

Any help appreciated as am struggling here. Cheers

[tigeravatar]

Blake 7,

The problem is you didn't adjust the sample code for your needs. I named everything "MyMacro", and your macro is called "move" so to make it work for you, you can use the following:

Private Sub Workbook_AddinInstall()

    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Move").Delete
    
    Set Blake7Macro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
    With Blake7Macro
        .Caption = "Move"
        .Style = msoButtonCaption
        .OnAction = "move"
    End With

End Sub

Same goes for the Uninstall portion:

Private Sub Workbook_AddinUninstall()

    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Move").Delete

End Sub

~tigeravatar

[Blake 7]

I feel like a right dufus - i'm new to this stuff but intend to learn pronto!!! cheers tiger man will check this out now!

[Blake 7]

Hiya - can you please have a look at the attachment -

I keep getting the error msg " cannot run the macro move xlam move. the macro may not be available in theis workbook or all macros may be disabled. "

macros are not disabled.

So sorry to bother you. but would love to get this sorted and learn from it!!!

[tigeravatar]

Blake 7,

Attaching a file doesn't do much when it comes to an addin
I have created a .xla file. Here's the link to it from my dropbox: http://db.tt/SRPiGZZ

Give that one a try. It worked fine for me on your test file.
~tigeravatar

[Blake 7]

Thank you very very much! dude........ you have no idea how much googling I did today to try and resolve this issue!

Thanks for your patience.

I can see from your file what I did wrong. i put all code in ThisWorkbook whereby from the looks of it i should have put the macro code in a module!!

However, going through this has inspired me to get booked on a vba course.

Thanks v much.