Button ( form Control ) - to enable & disable based on excel cell value

[Rohith1324]

I have an excel sheet where in C7 cell we will have the values. ( End user to key in ) the values can be "E" or "B"

when the value in C7 is - "E" then the Button1(form Control ) to be enabled.
when the value in C7 is - "B" then the Button1(form Control ) to be disabled

please support

 

[Jimmypop]

Hi there

What about:

Goes to sheet code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Range("C7") = "E" Then
        Call disable_button_1
    ElseIf ActiveSheet.Range("C7") = "B" Then
        Call activate_button_1
    End If
End Sub

Goes into standard module:

Option Explicit
Sub disable_button_1()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 1")
    With myshape
       .ControlFormat.Enabled = False    '---> Disable the button
       .TextFrame.Characters.Font.ColorIndex = 15    '---> Grey out button label
    End With
End Sub
Sub activate_button_1()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 1")
    With myshape
       .ControlFormat.Enabled = True    '---> Enable the button
       .TextFrame.Characters.Font.ColorIndex = 1    '---> Highlight button label
    End With
End Sub

 

[Rohith1324]

Hi there

What about:

Goes to sheet code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Range("C7") = "E" Then
        Call disable_button_1
    ElseIf ActiveSheet.Range("C7") = "B" Then
        Call activate_button_1
    End If
End Sub

Goes into standard module:

Option Explicit
Sub disable_button_1()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 1")
    With myshape
       .ControlFormat.Enabled = False    '---> Disable the button
       .TextFrame.Characters.Font.ColorIndex = 15    '---> Grey out button label
    End With
End Sub
Sub activate_button_1()
    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 1")
    With myshape
       .ControlFormat.Enabled = True    '---> Enable the button
       .TextFrame.Characters.Font.ColorIndex = 1    '---> Highlight button label
    End With
End Sub

Great....this works....thank you so much Jimmy..

 

[Jimmypop]

Great....this works....thank you so much Jimmy..

Pleasure... glad we could help and thanks for the feedback