disable a button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
That probably should be Sheets(1).Controls.cmdMainMenu.Hide. Anyhow, hide
the button if you don't want to copy it.
 
You might have to use the visible property with the button. I think hide
only applies to the user form.

CommandButton.visible = False
 
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
'<<=============
 
Back
Top