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" <(E-Mail Removed)> wrote in message
news:885A477B-FAF8-40BB-90B0-(E-Mail Removed)...
> 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