Command Button Macro Link Broken

[PumaMan]

Hello,

I have a 07 Excel file that has some command buttons that call very simple Macros on click. This sheet is accessible through an intranet site and functions with no problems for 07 users. Whenever someone with Excel 03 accesses the file, it converts it and opens the file up in Excel instead of the web browser. That's perfectly fine with me and what I want to happen.

However, when those 03 users then click the command button, the link to the Macro is broken for some reason. The user received a "#ref' cannot be found" error. This used to work with no problems, so I'm at a loss as to why it's happening now.

Misc Info: The sheet has 9 tabs and these buttons reside on tab #7. I looked at the VBA project model and the macros are stored in Module #7.

I've tried so many things, but can't figure it out. I appreciate any help. Let me know if I can provide anything additional to help.

Desperately yours,
John

 

[Smitty]

Welcome to the Board!

If the code explicitly refers to the file name, then the likely culprit is the difference in file extensions between '03 & '07 (.xls vs. .xlsm).

Can you post the offending code?

 

[PumaMan]

Hi Smitty Ji,

The Macro that the button calls contains very simple code that goes to a cell location, selects those cells, copies the content, and the returns home. here is the code:

Sub ChangeTitle()
Range("AA53:AC53").Select
Selection.Copy
Range("D3:F3").Select
Call NotesCopiedConf
End Sub

Sub NotesCopiedConf()
MsgBox ("Notes copied - Ready to paste.")
End Sub

Let me know what else I can provide that can be of help.

Thanks!

 

[Smitty]

Sorry for not getting back earlier, but I was out of town for a few days. I don't see any reason for the code to fail, but I'd probably go with this just to eliminate the selecting:

Sub ChangeTitle()
  Range("AA53:AC53").Copy Range("D3")
  Call NotesCopiedConf
End Sub

Note that there's not really any need to have the MsgBox in a second sub, unless you're calling it from other places as well.

HTH,

 

[PumaMan]

Hi Smitty,

Thanks, but the VBA code isn't what isn't working for me. It's the link between the command button and the code. I right-click the command button, select "Assign Macro" and select the saved Macro. I can then click the button and run whatever the code is (functional or not). For some reason though, when someone on office 2003 opens the file (2007), that link between the button and the Macro is broken. I can relink it and it works fine, but it breaks again when I save the file.

Note: This does not happen if someone on 2007 opens the file, but our users are all on 2003 and I need the file saved on 2007 format so that when the file is opened from the web, it opens in Excel and not in a web browser.

Any ideas?

Thanks again,
Elbio

 

[Smitty]

What happens if you draw the button in '03? It's possible that if you used an '07 object when it gets converted to '03 that's what's breaking the link. Outside of that I can't really think of any good reason for the link to be breaking. The code is simple enough, so I don't think that's it.

 

[xenou]

I right-click the command button, select "Assign Macro" and select the saved Macro.

I've also found such "assigned" macros do not travel well if you move or rename a workbook.

Possibly try an ActiveX command button instead. Another option might be to see if the file works better as an .xls (2003 file type).

 

[PumaMan]

Hi xenou,

Thanks for the suggestion. I need the file in 2003 format so that it opens in Excel instead of a web page (file is being access on SharePoint), but I'm trying the ActiveX control like you suggested. I have a strong feeling that will resolve it! I'll let you know.

Regards,
Elbio

 

[PumaMan]

sadly, the active x control suffers from the same issue. Once an '03 user opens the file and it converts, the active x button turns into an image and loses the tie to any VBA code on the workbook. I'm very confused because this is a new problem.

My previous versions didn't experience this and were saved in the same format and accessed in the same way. Something got messed up along the way and I'd just like to know what

 

[xenou]

Are you also "assigning" the macro to the ActiveX control?