How to disable a button picked from the Forms list.

[Ben]

Hi,
I have a button on one of my worksheets picked from the Forms list
this control don't have an Enabled property so I would like to know
if it is possible to use VBA to disable it.

I can use the other Activex command button picked from
the control toolbox but that is causing problem with Excel 2000.

Thank you.

 

[Bob Phillips]

Ben,

How about hiding it completely

activesheet.buttons("Button 1").Visible = False

What problems are you having with ActiveX buttons? I have heard of problems
with 2003, but not 2000.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Greg Wilson]

Forms buttons can be disabled but this will only disable the click
functionality. So the appended macros disable and enable the button including
graying of font.

Sub DisableBtn()
With ActiveSheet.Buttons(1)
.Enabled = False
.Font.Color = RGB(140, 140, 140)
End With
End Sub

Sub EnableBtn()
With ActiveSheet.Buttons(1)
.Enabled = True
.Font.Color = vbBlack
End With
End Sub

Regards,
Greg

 

[Ben]

Hi Bob and Greg,

Greg, thanks it worked.

Bob, when you use Activex Buttons on worksheets and you don't
want to save the file when the macro is enable and click on the X
to close the file it always ask you if you want to save the file even
if you put in the Workbook_BeforeClose event code like this
ThisWorkbook.Saved = True