Add-in not showing in Macro's list

[samfolds]

Hello,

I have recently discovered the beauty of add-ins, but I've got a small problem :

I saved my add-in workbook as an xla in the appropriate folder and I enabled the add-in in the workbooks, but when I open a new workbook, the sub I had made does not appear in the Macro's list (Alt+F8).

When I go in the VBA environment (Alt+F11), I see my sub in the workbook [Name].xla.xls...

Is there anyway I can make the sub appear in the Macro's list?

Thanks in advance!

 

[sykes]

.....may not be the problem at all, but when you say your Add-In's called "[name].xla.xls" ....... if it is, then it's not in the Add-In file extension format. It needs to be simply "[name].xla"

....also just confirm you're not running Office 2007 - as this needs to be a .xlam file extension.

 

[samfolds]

Ok, sorry for not expressing myself correctly.

1) When I save the file, it's in .xla (Exemple "Formatting.xla"). When I open the VBA Editor, the "spreadsheet" from which the Add-in takes its source code is called "Formatiing.xla.xls".

2) I don't run Office 2007.

To add a little more meat to the bones, the add-in runs perfectly fine when I press the "Play" button in the VBA Editor. It works even if the add-in module is not in the new spreadsheet (The one I want to use the add-in in).

I'll copy my code below, but I doubt it will do any good as this is the add-in itself.

-----------------------------------------------------------------------

Sub Formatting_From_SAS()

Application.ScreenUpdating = False

Dim RowsCount, ColumnsCount As Integer
Dim i, j As Integer
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
WS.Activate '*IMPORTANT* Macro will not work without this line

Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit

Cells.Select
With Selection
.RowHeight = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.NumberFormat = "#,##0"
.Interior.ColorIndex = xlNone

With .Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End With


RowsCount = ActiveSheet.UsedRange.Rows.Count
ColumnsCount = ActiveSheet.UsedRange.Columns.Count

For i = 4 To RowsCount

For j = 3 To ColumnsCount

Cells(i, j).Value = Cells(i, j).Value * 1

Next j

Next i


Next WS

Application.ScreenUpdating = True

End Sub

 

[mooseman]

I saved my add-in workbook as an xla in the appropriate folder

What's the appropriate folder?
None of my add-ins show up in the VBA editor with .xls, they are all .xla.
If you want to see the macro in the Alt-F8 window, just save it in your personal workbook.

 

[sykes]

I don't think the workbook will work as an Add-In unless the file extension's correct, and the name ending in .xls is wrong - it needs to end in .xla

 

[samfolds]

Ok "my" appropriate folder is : ...\Application Data\Microsoft\AddIns.

When I choose the Addins extension (.xla), I get directly to this file (as if office wanted me to put it there).

When you say "just save it in your personal workbook", where would that be? And will the Add-in always show up when I open Excel ?

Many thanks.

 

[Weaver]

Macro's in an .xla (.xlam) don't show up in the macro list.

Record a simple macro and when offered the choice of where to store it, choose 'personal macro workbook'. Now you have a personal.xls.

Transfer all your macros to this workbook and they'll show up in the macro list (alt+F8)

For i = 4 To RowsCount
For j = 3 To ColumnsCount
Cells(i, j).Value = Cells(i, j).Value * 1
Next j
Next i

should be replaceable by

with range(cells(4,3),cells(rowscount,columnscount)
    .value = .value
end with

 

[samfolds]

Weaver, if I do save my workbook in my "personnal macro workbook", I won't be able to transfer it to others right?

Because the final goal of the project is to make this macro available for people in my office to work with on a regular basis.

Is there a solution that allows me to make my macros public to anyone and that act as an add-in?

Thanks.

PS : Also, thank you for the improvement in the code, I'll try it right away. (Note that the *1 is mandatory though, as the numbers imported in the spreadsheet are not formatted as numbers)

 

[samfolds]

Just a bump, I really need help guys

 

[mooseman]

Save your macros in a blank workbook.... name it Sams_Macros.xls and save it to a network drive somewhere were everyone can access it and when they open it they will have access to your macros. You can eve hide the workbook so when they open it they won't see the workbook, but will see the macros.
Other than that, you'd have to learn to make menus bars/buttons and assign your macro there through the add-in...... that's most likely best for you, but will take some trial and error on your part to really understand how it works.