disable and enable button inside sheet like commandbutton on userform

[abdelfattah]

Hi

is there any way to disable buttons and enable buttons inside the sheet like disable and enable commandbutton on userform?

I want to enable button name is ("search") and disable buttons names are ("copy","delete") when press button name is ("search") then will button name is ("search") becomes disable and enable buttons names are ("copy","delete") and when press button name is ("copy ") becomes disable and enable button name ("delete") and when press button name is("delete") then will button name is ("delete") becomes disable and enable button name is ("search")
I hope this is logical .
thanks

 

[Jeffrey Mahoney]

Please try this. I created an ActiveX button and named it Search_btn. The first code is stored on the SHEET level module, the second code is stored in a standard module

When a user clicks the search button the first code gets called, disables the search button so it can't run until the search has been completed

Private Sub Search_btn_Click()
  
  ToggleOleEnable Me.Name, ActiveSheet, False 'disable while search is continuing
  
  'Go do other things and come back
  
  ToggleOleEnable Me.Name, ActiveSheet, True  'Search is finished, enable button

End Sub

'BtnNAme Name of button or other control
'Sht is the the sheet the control is on as object
'EnBool is either True for Enabled or False for Disabled
Sub ToggleOleEnable(BtnNAme As String, Sht As Worksheet, EnBool As Boolean)
  Dim oOLE As OLEObject
  For Each oOLE In Sht.OLEObjects
    If oOLE.Name = BtnNAme Then
      oOLE.Enabled = EnBool
      Exit For
    End If
  Next oOLE
End Sub

 

[abdelfattah]

thanks

I created an ActiveX button

no I'm not talking about ActiveX button . I want doing with normal button which use linking with the macro .

 

[HaHoBe]

Hi abdelfattah,

the buttons are named btnSearch, btnCopy and btnDelete in this sample, the codes being called are of the same name.

This code needs to be run first in order to set up the buttons (you may call it from Workbook_Open if opening the workbook):

Sub OnlyBtnSearch()
  'needs to be run in order to set up the buttons accrosingly
  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnDelete")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnSearch")
      .Font.Color = 0
      .OnAction = "btnSearch"
    End With
  End With
End Sub

I altered the request to have only one of the 3 buttons available:

Sub btnSearch()
  'code to perform for Search here

  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 0
      .OnAction = "btnCopy"
    End With
    With .Buttons("btnDelete")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnSearch")
      .Font.Color = 8421504
      .OnAction = ""
    End With
  End With
End Sub

Sub btnCopy()
  'code to perform for Copy here

  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnDelete")
      .Font.Color = 0
      .OnAction = "btnDelete"
    End With
    With .Buttons("btnSearch")
      .Font.Color = 8421504
      .OnAction = ""
    End With
  End With
End Sub

Sub btnDelete()
  'code to perform for Delete here

  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnDelete")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnSearch")
      .Font.Color = 0
      .OnAction = "btnSearch"
    End With
  End With
End Sub

HTH,
Holger

 

[abdelfattah]

@HaHoBe
thanks
shows error Unable to set property OnAction of the Button class in this line

      .OnAction = "btnSearch"

for module Sub OnlyBtnSearch()

 

[HaHoBe]

Hi abdelfattah,

a view to my IDE:

Sample workbook is available here

HTH
Holger

 

[abdelfattah]

thanks for the file
it's works excellently , but I have to studing my bad to understand how your code works .
I will come back to inform you how code goes.

 

[HaHoBe]

Hi abdelfattah,

it's almost the same for all: for the next button in the wanted "chain" change the font colour to black and attach the wanted procedure while for the other 2 the font colour is dimmed and the attached macro is stripped so that every click only one of the three buttons can start a macro.

Holger

 

[abdelfattah]

now it wrorks as I expect .
two questions please?
first : macro for Sub OnlyBtnSearch() , just I need run one time and I don't need anymore?
second if I want enabling all of the buttons , what's the procedure?

 

[HaHoBe]

Hi abdelfattah,

first : macro for Sub OnlyBtnSearch() , just I need run one time and I don't need anymore?

Just change the code for the btnDelete:

Private Sub btnDelete()
  'code to perform for Delete here

  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 8421504
      .OnAction = ""
    End With
    With .Buttons("btnDelete")
      .Font.Color = 8421504
      .OnAction = ""
    End With
'    '/// activate btnSearch
'    With .Buttons("btnSearch")
'      .Font.Color = 0
'      .OnAction = "btnSearch"
'    End With
    '/// do not activate btnSearch
    With .Buttons("btnSearch")
      .Font.Color = 8421504
      .OnAction = ""
    End With
  End With
End Sub

Going this way would mean that the other two macros can't be started as well. By the way: I changed the procedure from Public to Provate so they may not be started via the Macro-Window. If you want anything else feel free to post back and describe what you are looking for.

second if I want enabling all of the buttons , what's the procedure?

I posted procedure OnlyBtnSearch. In a renamed copy of that procedure all you would need to do is copy in the parts for each button where a macro is assigned under OnAction...

Sub ActivateAllButtons()
  'will activate all buttons and attach macros to them
  With Worksheets("Sheet1")
    With .Buttons("btnCopy")
      .Font.Color = 0
      .OnAction = "btnCopy"
    End With
    With .Buttons("btnDelete")
      .Font.Color = 0
      .OnAction = "btnDelete"
    End With
    With .Buttons("btnSearch")
      .Font.Color = 0
      .OnAction = "btnSearch"
    End With
  End With
End Sub

Holger