PDA

View Full Version : Unknown external link to macro-workbook problem



sjohnp2112
02-21-2017, 09:36 AM
Hey all,

I'm relatively new to VBA but I'm confounded at the following issue I'm having.

I created a .xlsm file with some macros. Here is the process I used to attempt to share it on the web: 1) I thoroughly checked the test file for any external links to other workbooks, I found and fixed some by searching manually, but I also inspected the document using Excel's utility, and Kutool's. In short all external reference links were found and broken. I can open the file, everything works great, including two the two simple macros I added to the ribbon.

Currently the file is named 'budget template working.xlsm'. 2) I saved the file to another folder with the filename 'budget template sample.xlsm'. Zipped up the file and uploaded to Google Drive. Then downloaded file, and unzipped it. It opened fine as expected - however every time I click on either two of my macros on the ribbon, the original file 'budget template working' keeps opening - I don't want that to happen, I'm pulling my hair out. I want to get rid of this unknown link. If I rename "budget template working" to "budget template working2" for example, then I get a message in budget template sample like "Excel can't find 'budget template working.xlsm. Is it possible it was moved, renamed, or deleted? So there's some kind of hidden link I'm unable to solve. I'm wondering if it's related to absolute/relative path references but not sure. Any kind help, suggestions ect. from anyone I would be most grateful. If somehow it is code-related, here's the code to the macro in question:



Sub CopyMonthColumns()
' CopyMonthColumns Macro
'
Application.ScreenUpdating = False
If Month(Now) = 2 Then
Worksheets("Feb").Activate

Dim sourceColumnA2M As Range
Dim targetColumnD2M As Range
Dim sourceColumnC2M As Range
Dim targetColumnE2M As Range

Set sourceColumnA2M = Worksheets("Feb").Columns("A")
Set targetColumnD2M = Worksheets("Feb").Columns("BT")
Set sourceColumnC2M = Worksheets("Feb").Columns("AG")
Set targetColumnE2M = Worksheets("Feb").Columns("BU")

sourceColumnA2M.Copy Destination:=targetColumnD2M
sourceColumnC2M.Copy Destination:=targetColumnE2M

Sheets("Feb").Select
targetColumnE2M.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BQ1<>0"

With Selection
.Font.Color = RGB(0, 0, 0)
.Font.Bold = True
End With

With Selection
.Interior.Color = RGB(255, 255, 255)
End With
MsgBox "Copy successful."

Application.ScreenUpdating = True
End If
End Sub



Thanks, sjohnp2112

Jan Karel Pieterse
02-22-2017, 06:02 AM
Have you added those ribbon buttons manually through Excel's user interface? If so, those buttons will keep pointing to the workbook containing the macro you originally pointed it to.
To add custom ribbon tabs and buttons to a file which travel with the file you need quite a bit more. Ron de Bruin lists a lot there is to know about that here:
http://www.rondebruin.nl/win/section2.htm