VBA headache!

[Mattastic]

I am trying to run some VBA (Not my strongpoint), which when i had i on a form control button worked fine! But once i flipped to ActiveX, I am struggling to get it working.

Can anyone see the issue? It fails at highlighted line.

If there is a cleaner way of doing the same I would be happy to hear any suggestions too.

Private Sub MyButton_Click()
'Saves filename as value of A1 plus the current date plus changes all INDIRECT refrences to static data
Dim newFile As String, fName As String
fName = Range("A1").Value
newFile = fName & " " & Format$(Date, "dd-mm-yyyy")

'turn off alerts for SAVEAS
Application.DisplayAlerts = False

'SAVE file
ChDir _
"C:\Users\Public\Documents"
ActiveWorkbook.SaveAs Filename:=newFile

'turn alerts back on
Application.DisplayAlerts = True

'select copy from data
Sheets("ALN").Select
Range("A1:Q52").Select (Note: This was origonally cells.select)
Selection.Copy

'select copy to array
Sheets(Array("ALN", "CAS", "CWO", "DEI", "EON", "GMC", "HBM", "HDM", "HPU", "IDD", "KOE", _
"LAE", "LYP", "MEN", "MLP", "PAC", "PFR", "POE", "RUL", "SAR", "SCG", "SON", "TRF", "VAT", _
"VEL", "WAO", "WGV")).Select

'paste special values
Sheets("ALN").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'back to front page
Sheets("ScoreCard").Select

'delete button on new file
Dim Btn As Object
ActiveSheet.Shapes("MyButton").Delete

End Sub
​

Thanks,

Matt

 

[xenou]

As a general rule I don't put code in buttons and it has always made my life easier!

Try this:

IN BUTTON:

Private Sub MyButton_Click()
    Call MyMacro1
End Sub

In Standard Module:

Public Sub MyMacro1()
'//All your code ....
End Sub

Buttons just don't seem to "see" everything in your project although there are probably ways to make it work, my strategy is just to have the button call the code.
Though I don't really get what's going on with deleting "MyButton" - if you are trying to delete a button that's running code then I really don't know what to expect - I guess we'll find out.

 

[Mattastic]

Thanks XEONU,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

So you approach works perfectly....<o></o>

The reason for the button delete, which work as it should; Each week the macro is run to change 1400 INDIRECT references to static data. Once run, I don’t want it run again on the output file - so the simple approach seemed to be the button delete.<o></o>

I decided it was simple and clean, and to be honest if the user is clever enough to run the macro again without the button they would be smart enough to mess with it anyway.<o></o>
Again thanks for the help!!<o></o>