Hi,

I use the following code to copy the worksheet selected in a listbox, to a
new workbook. Problem, this code copies a button on this worksheet, and i
don't want it to be copied. Can I prevent this?

the button on the sheet is called 'cmdMainMenu'

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
MsgBox "A copy of " & Me.lstExportData.Value & " has been pasted to your
desktop"

End Sub

--
Carlee

Sheets(1).cmdMainMenu.Hide

"Carlee" wrote:

Hi,

I use the following code to copy the worksheet selected in a listbox, to a
new workbook. Problem, this code copies a button on this worksheet, and i
don't want it to be copied. Can I prevent this?

the button on the sheet is called 'cmdMainMenu'

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
MsgBox "A copy of " & Me.lstExportData.Value & " has been pasted to your
desktop"

End Sub

--
Carlee

That probably should be Sheets(1).Controls.cmdMainMenu.Hide. Anyhow, hide
the button if you don't want to copy it.

"Carlee" wrote:

Hi,

I use the following code to copy the worksheet selected in a listbox, to a
new workbook. Problem, this code copies a button on this worksheet, and i
don't want it to be copied. Can I prevent this?

the button on the sheet is called 'cmdMainMenu'

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
MsgBox "A copy of " & Me.lstExportData.Value & " has been pasted to your
desktop"

End Sub

--
Carlee

You might have to use the visible property with the button. I think hide
only applies to the user form.

CommandButton.visible = False

"Carlee" wrote:

Hi,

I use the following code to copy the worksheet selected in a listbox, to a
new workbook. Problem, this code copies a button on this worksheet, and i
don't want it to be copied. Can I prevent this?

the button on the sheet is called 'cmdMainMenu'

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
MsgBox "A copy of " & Me.lstExportData.Value & " has been pasted to your
desktop"

End Sub

--
Carlee

Hi Carlee,

Try:

'=============
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
.Sheets(1).OLEObjects("cmdMainMenu").Delete
.SaveAs Filename:=WB2.Sheets(1).Name & ".xls"
.Close
End With
MsgBox "A copy of " & Me.lstExportData.Value _
& " has been pasted to your desktop "
End Sub
'<<=============


---
Regards,
Norman


"Carlee" wrote in message
...
Hi,

I use the following code to copy the worksheet selected in a listbox, to a
new workbook. Problem, this code copies a button on this worksheet, and i
don't want it to be copied. Can I prevent this?

the button on the sheet is called 'cmdMainMenu'

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
MsgBox "A copy of " & Me.lstExportData.Value & " has been pasted to
your
desktop"

End Sub

--
Carlee