advice activex button vs form button on protected sheet

Hi all,

i've recently tried to use an ActiveX button in my workbook, which i wanted to put on a hidden row in a protected sheet. I know about the excel problem (the button disappears when i hide the row), so i'm asking for an advice, i want to use a button after all, but without hiding the row.

Is there any other problem i might be facing if i use an ActiveX button on a protected sheet? Should i use a form button instead?

Not quite sure how to proceed, so i need your input on the matter, since this ActiveX is so new to me...

Thank you in advance!

Hi,

'Active X' tends to be unpredictable and fragile. I avoid 'Active X' unless there is no other alternative. When I do use 'Active X', I create the 'Active X' controls using VBA code, so if there is a problem, it is relatively painless the recover. In addition, 'Active X' is not backward compatible from Excel 2010 to Excel 2003. I have no experience with other Excel versions.

I use Forms Controls or UserForms before I even consider 'Active X'.

Lewis

hi,

thank you for your response. I have tried to avoid using controls, but now it seems I have no choice.
I will open a new thread, because yesterday I tried using Forms Controls and I stumbled upon another problem, I'm using Excel 2010 and I can't disable the button with the .disable property. Is it a general problem (an excel bug yet to be solved) ? Has anyone found a way around it, because I didn't find one...

Thank you again for your help.

I was able to duplicate your apparent Forms Control .enabled anomaly in Excel 2010. Apparently the .enabled property works as expected in Excel 2003 (tested by me) and in Excel 2007 (anecdotal evidence from another thread).

I was successful in disabling a Forms control using the .enabled property as a software switch (see the attached file and the code that follows). Another alternative is the hide (.visible = False) the CommandButton when it is supposed to be disabled.

In an ordinary code module such as Module1:

Option Explicit

Sub CommandButton1EventHandler()
  
  Dim bCommandButtonEnabled As Boolean
  
  bCommandButtonEnabled = ActiveSheet.Shapes("Button 1").ControlFormat.Enabled

  If bCommandButtonEnabled = True Then
    MsgBox "CommandButtonEventHandler() activated on " & Now() & "."
  End If
  
End Sub

Sub DisableCommandButton()

  'Locked property enables Button attributes to be changes (e.g. change text)
  'Enabled property enables / disables Click Events
  ActiveSheet.Shapes("Button 1").Locked = True
  ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False
  
  Debug.Print ActiveSheet.Shapes("Button 1").Locked
  Debug.Print ActiveSheet.Shapes("Button 1").ControlFormat.Enabled
  
  MsgBox "The 'Enabled' Property set to 'False' for 'Button 1'."

End Sub

Sub EnableCommandButton()

  'Locked property enables Button attributes to be changes (e.g. change text)
  'Enabled property enables / disables Click Events
  ActiveSheet.Shapes("Button 1").Locked = True
  ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = True
  
  Debug.Print ActiveSheet.Shapes("Button 1").Locked
  Debug.Print ActiveSheet.Shapes("Button 1").ControlFormat.Enabled

  MsgBox "The 'Enabled' Property set to 'True' for 'Button 1'."

End Sub

Lewis

thanks, it works perfectly. This is exactly what i needed.

Thank you for all your help.