disable a button

C

Carlee

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
 
J

JLGWhiz

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

JLGWhiz

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

CommandButton.visible = False
 
N

Norman Jones

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
'<<=============
 

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

Similar Threads


Top