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

 

[abdelfattah]

thanks

Going this way would mean that the other two macros can't be started as well

no I don't want it . when press btnDelete should return all of the buttons to enabling
I was asking procedure OnlyBtnSearch is useless after run from the first time , then I can delete this macro(no need anymore) .

 

[HaHoBe]

Hi abdelfattah,

really delete from the module? Why not change the code for btnDelete to either

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 = ""
    End With
  End With
End Sub

which will enable the button but not attach the macro or

Sub btnDelete()
  'code to perform for Delete 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

which makes more sense to me to leave btnSearch disabled and enable btnCopy and attach the macro to that.

To delete code from a module you would need to Enable Trust Access to the VBA Project Object Model in Options and could use a code like

Sub DeleteProcedure()
  Dim blnSucc     As Boolean
  
  Const cstrNameModule As String = "modTest"
  Const cstrNameProc   As String = "btnSearch"
  
  blnSucc = VBE_DeleteProcdure(cstrNameModule, cstrNameProc)
  
  If blnSucc Then
    MsgBox "Procedure deleted"
  Else
    MsgBox "Could not delete Procedure"
  End If
End Sub

Function VBE_DeleteProcdure(NameOfModule As String, strProc As String) As Boolean

  Dim lngStart        As Long
  Dim lngNrLines      As Long
  
  On Error GoTo err_here
  With ActiveWorkbook.VBProject.VBComponents(NameOfModule).CodeModule
    lngStart = .ProcStartLine(strProc, vbext_pk_Proc)
    lngNrLines = .ProcCountLines(strProc, vbext_pk_Proc)
    .DeleteLines lngStart, lngNrLines
  End With
  VBE_DeleteProcdure = True
  Exit Function
  
err_here:
  VBE_DeleteProcdure = False

End Function

where you should alter the name of the module holding the macro to suit.

Ciao,
Holger

 

[abdelfattah]

Ok I choose second choice about button "btnDelete" , but the problem keep buttton "btnSearch" is enabled and I can't press on it , just show selection around the button .
why this case happens with just this button?

 

[HaHoBe]

Hi abdelfattah,

as long as you keep the button visible on the sheet you can klick on it (resulting in running a macro or not if not attached). Maybe alter procedure btnSearch to

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")
      .Visible = False
'      .Font.Color = 8421504
'      .OnAction = ""
    End With
  End With
End Sub

and procedure OnlyBtnSearch to

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

Holger

 

[abdelfattah]

sorry for delaying !
what happens ?!!
it delete button btnSearch.

 

[HaHoBe]

Hi abdelfattah,

it delete button btnSearch.

Definitely not, it just hides the button from view (that's what the command .Visible = False says. And I presented the altered code for making the button visible again.

In my opinion you want to use a function which is available on ActiveX-Controls for Forms-Controls - sorry, I do not know a way to do so. If you keep the button visible any user still can click on it and either start an attached procedure or not. Making it available for one click and setting it not to be visible will not allow any user to click because it seems there is no button to click on.

Holger

 

[abdelfattah]

Hi HaHoBe
strangely, doesn't hide button btnSearch when using OFFICE-VERSION 365 in another laptop , why?

 

[HaHoBe]

Hi abdelfattah,

sorry I don't have any 365 to test so do the macros work as expected means: have you enabled macros in the Options? Allowed macros to work in the workbook?

Holger

 

[abdelfattah]

have you enabled macros in the Options? Allowed macros to work in the workbook?

I'm not sure , but I think if as you say shouldn't work and tell me by message !