Do Not Export Button Objects



I use the code below to export a series of sheets to a new workbook. I need
to know how to NOT export the button objects. Everything else is fine, but
not the buttons. Can anyone help me?

Sub Mail_Sheets_Array()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("Daily Report", "T-30 Days", "T-30 Graphs", "Year
at a Glance", "Monthly Graphs")).Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog
that you only
'see when you copy sheets from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheets to values if you want
' For Each sh In Destwb.Worksheets
' sh.Select
' With sh.UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Destwb.Worksheets(1).Select
' Next sh

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Client Report Package for" & " " & Format(Now,

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
On Error Resume Next
.SendMail "", _
"Daily Report Package"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Mark Ivey

When I did something like this in the past... I either hid the buttons or
deleted them from within the code while saving to a new workbook.

Mark Ivey

Mark Ivey


If you created button with the Control Toolbox Toolbar (a true
CommandButton), the following example should shed some light on this issue.

CommandButton1.Visible = False

Mark Ivey

Carlee said:
HI there,

How do i go about hiding the buttons?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question