Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

disable and enable button without use ActiveX

0

hi experts

I  no  know  what   I 'm asking     it's  possible  so   i  have   button   not ACTIVE X linked  with  a simple  macro   so  what  I   want    if    I  write  in  cell A1=  YES   then  enable    the   button  and  run  the  macro   and  if  cell A1 is   empty   then  should disable    the  button  and  when  press  the  button     it  shouldn't  run  the   macro   and   if  it's  possible   prevent  select  the  button      if  I  select right   click  or  use   design mode

this  is  what   i have  so  far  but  not  what  I  want 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
Dim bbt As Button
Set bbt = ActiveSheet.Buttons("Button 1")
bbt.Enabled = True
Else
bbt.Enabled = False
End If
End Sub

thanks  in advance 

excel
MS Office Version: 2010
posted by: speed (rep: 40) on Wed May 19, 2021 at 4:26 am
Answer
Discuss

Answers

0
Selected Answer

All new buttons are of ActiveX type. Form controls are a legacy of older versions of Excel. Don't use them unless you are planning to use the workbook with Excel versions older than 2007.

The attached workbook is a copy of the one you posted. In it I made your idea work. The trick is not to disable the button but to disable the button's action, meaning the macro will run when the button is clicked but it will do nothing if A1 is blank. You can change the test to require specifically that A1 should contain the word "yes".

I have added an ActiveX button and connected it to the same macro called "Welcome". The difference is that the form control is assigned a macro by name, which must be located in a standard code module, whereas the ActiveX control has many pre-prepared event macros which you can select in th worksheet's code module.

In Design Mode you can view the properties of either control. The ActiveX control has several more properties. One of them is TakeFocusOnClick. This isn't to enable or disable the button, which can also be done using the Enabled property, but to prevent selection of the button. This is useful because you can click the button and continue working in the sheet without losing your focus.

posted by: Variatus (rep: 4889) on Wed May 19, 2021 at 8:16 pm
Discuss

Discussion

thanks 
but  I'm  not  sure  if  I  understand   TakeFocusOnClick  works  for  prevent selection of the button    actually  when  Design Mode  turns  on   it  can still   select  the  button  even  change  the  value  to  false from properties  
speed (rep: 40) May 20, '21 at 6:15 am
That's correct. In Design Mode the normal functioning of the control is suspended.
Variatus (rep: 4889) May 20, '21 at 7:46 am
but  I  see  this  is not  usefuls  when  change  to  false  and  doesn't  work  when Design Mode  turn  on .  so  whats  the  purpose  from  it     it  doesn't  seem to  successes  with  Design Mode  turns  on so  what's  the  differnce  if  I  select the  command button   when  the  value=TRUE  or FALSE in properies   when  Design Mode  turns  on  it ?  in all about  the button  remain selected 
speed (rep: 40) May 20, '21 at 8:31 am
You only turn on Design Mode when you want to change the buttons design. While working with the application Design Mode is always turned off.
When TakeFocusOnClick = True the button will become selected when you click it. But the button only runs a macro and after running the macro you may want to have the cell selected on which you were working. For me, in 90% of all cases I want the button not to take the focus. If you don't find the feature useful, don't use it.
Variatus (rep: 4889) May 20, '21 at 8:39 am
thanks  very  much  for  your  answering and  time 
speed (rep: 40) May 20, '21 at 9:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login