created .XLA but the form still wants to open original sheet

[DougP]

I guess somewhere in the VBA code or form it is referring to the original Sheet I created the VBA code and form in? I created a form and some VBA code. Then I copied and pasted the VBA code and imported the form in a blank new .XLA file with no spread sheet. If you open the .XLA file there are no sheets.
So when I click the macro button it opens the old spread sheet first then opens the form

Any ideas how to stop it from opening the old spreadsheet?
I wanted to use this form in any spreadsheet. It seems to work in any spreadsheet but it always opens the original sheet too.


DougP, MCP, A+

 

[DougP]

Ok I fgured out that the smilley face button's macro is looking at the following old sheet.
'C:\BarcodeONE\ADVA weekly inventory report 11-04-05-2.xls'!showForm

I would like to reassign it to look at the .XLA addin. but nothign shows in the marcos even though I can see the code when I go to "Tools" menu macros Visual Basic editor.

Any ideas how to assign a button to topne the form?



DougP, MCP, A+

 

[GVF]

I am not exactly getting what your saying but...
If you originally wrote the code for the button on a sheet code sheet, I think you need to put it into a regular module.
Is the button you refer to on a toolbar? Attach it to the add-in file but hide it when the add-in is not open so the code will not run inadvertantly if the button is pushed. You can do this with your Autpen and Auto-CLose procudures.

You cannot open an add-in for editing. I don't get that part. You must open the original .xls file, edit it, and save it as a .xla file which is a special hidden file and it doesn't matter if it has worksheets in it or not since no one can ever see them.

If the button is on a worksheet then when the workbook with the button is opened - set the macro you want the button to run:

ActiveSheet.Shapes("TheButton").OnAction = "MyMacro"

When the workbook closes, disable the button so no code runs if the add-in is not open.

ActiveSheet.Shapes("TheButton").Select
Selection.OnAction = ""

if there is more than one sheet in the file, you will have to run through all the sheets until you come across the one with the button on it.