Macro File NOt Found Error when I open on another machine

[BigMeat]

Hi

I have a Excel file (mybook.xls) which has a button which calls a macro. Hwen I try and open the spreadsheet from another machine and click the button I get a "File not found" error.

I think I know what the problem is....If I go to the Developer tab and select Macro and in the Macro In dropdown list I can see an old excel spreddsheet name in the dropdown list. How do I get rid of this reference? Could this be reason for the file not found error?

Many thanks in advance

[RichardWinter]

I'm not familiar - yet - with Excel 2007 but I guess this hasn't changed.
Make sure that your macro is in a modul of your mybook.xls.
Go into design mode and activate your butto.
Go to Show Code
In the code section of your sheet there should be something like:
Private Sub CommandButton1_Click
<<your macro path\name>>
End Sub

Make shure that you only have the name of the macro in there. It should then call the sub in your workbook ... if it is there.
If not go to the book where it is included, go into the VBA editor, select the modul with the macro, Right mouse click -> export
Then go to your mybook.xls -> VBA editor -> import the file your just exported.
You should be alle set then.

Hope this helps

Richard

[BigMeat]

Hi Richard

Thanks ever so much for the post/. I tried the above but still no joy. I have made my workbook available here

Basically it has a button in the Sheet called Main, when teh button called "Add Another Tender" is called it should show a message box, eventually I want it to unhode a column but I I cant even get it to show a message box yet

Any help would be greatly appreciated, im pulling mu hair out

Many thanks in advance

[RichardWinter]

Not sure what the culprit is with your workbook. It could be some kind of corruption.
I have copied the sheets to a new book and attached a generic macro to your shape.
This works and I hope you can start from there.

General recommendation: Put your macros in a separte modul (as done in the attached book) and leave the sheet module for code related to WorksheetEvents.

Hope this helps

Richard

[BigMeat]

Hi Richard

Thanks ever so much, you are a star! I think I have worked out why im getting that file not found error. I think some of the VLOOKUPS and Graph data is looking for another sheet as I have renamed the file a few times in my attempts to get it working. For example I have a VLOOKUP Data Validation dropdown list and I noticed that some of these had changed to an actual file reference. Also the Graph data was document relative rather than sheet relative. Is there an easy way to change the links back rather than actually going to each cell?

Also I have another niggle with graph positioning. Is there a way I can lock the position of a graph to a cell. For example I want the top left corner of the graph to be locked with cell F1. In my spreadsheet column E is hidden. When the column is unhidden the graphs overlays over column E and does not shift accross with column F.

Thanks ever so much for all your help

[BigMeat]

Hi Richard

Managed to solve my chart position problem, I added the below to fix the position

ActiveSheet.Shapes("Chart 1").Left = Cells(2, 2).Left
ActiveSheet.Shapes("Chart 1").Top = Cells(2, 2).Top


Cheers

[RichardWinter]

Good you solved your graph issue - I'm no graph expert and it would have been another thread anyway -
Regarding Links you can go to Edit -> Links. There you can change the source of the link.

Think you can mark this as solved.

Have fun
Richard

[BigMeat]

hi Richard

I just tried Edit > Links and when I change the link it crashes Excel? So I renamed the Excel to file it was looking for instead.

Thanks again for all your help

By the way how do I mark this thread as solved?

Cheers