Do not export buttons to new workbook

[Carlee]

Good Morning,

I use the following code to export a specific sheet in my workbook, to a new
workbook. All works great, however, i do not want to export the buttons on
the orignal sheet. Can anyone suggest how i can modify this code to achieve
this?

Many thanks in advance,


Module Code:
Sub SendToDesktop(WB As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim myPath As String
Dim myShortcutPath As String
Dim sStr As String

With WB
myPath = .FullName
sStr = "\" & Left(.Name, Len(.Name) - 4)
End With

Set oWSH = CreateObject("WScript.Shell")

With oWSH
myShortcutPath = .SpecialFolders.Item("Desktop")
Set oShortcut = .CreateShortcut _
(myShortcutPath & sStr & ".lnk")
End With

With oShortcut
.TargetPath = myPath
.Save
End With

Set oWSH = Nothing
End Sub
*******************************************

On Click Event Code:

Private Sub cmdExport_Click()
Dim SH As Worksheet
Dim WB As Workbook
Dim WB2 As Workbook

Set WB = ThisWorkbook

WB.Sheets(Me.lstExportData.Value).Copy
Set WB2 = ActiveWorkbook

With WB2
'.SaveAs Filename:=WB2.Sheets(1).Name & ".xls"
Call SendToDesktop(WB2)
.Close
End With
End Sub

 

[Norman Jones]

Hi Carlee,

Assuming that the buttons in question are from the
Control Toolbox, Try replacing your cmdExport_Click
code with the following version:

'=============>>
Private Sub cmdExport_Click()
Dim SH As Worksheet
Dim WB As Workbook
Dim WB2 As Workbook
Dim oleObj As OLEObject

Set WB = ThisWorkbook

WB.Sheets(Me.lstExportData.Value).Copy

For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.CommandButton Then
oleObj.Delete
End If
Next oleObj
Set WB2 = ActiveWorkbook

With WB2
'.SaveAs Filename:=WB2.Sheets(1).Name & ".xls"
Call SendToDesktop(WB2)
.Close
End With
End Sub
'<<=============

 

[Carlee]

thank you so much....worked like a charm!