Only Activate Control when Checkbox = True

[CorrieAnn Gillen]

I have a dashboard I am creating for a client. I Only want the buttons (controls) to be active if the associated checkbox = true. I have read tons of code samples online, but for some reason, they don't work with MS Excel 2010. Here are a few versions of what I have tried:

Private Sub Workbook_Open()
'ActiveSheet.Shapes("btnInternal").ControlFormat.Enabled = False
'ActiveSheet.Shapes("btnExternal").ControlFormat.Enabled = False
'ActiveSheet.Shapes("btnMapping").ControlFormat.Enabled = False
'ActiveSheet.Shapes("btnRDF").ControlFormat.Enabled = False
End Sub
Sub BtnOff()
'If cbInternal = False Then
' ActiveSheet.Shapes("btnInternal").ControlFormat.Enabled = False
' ActiveSheet.Shapes("btnInternal").DrawingObject.Font.ColorIndex = 16
' Else
' ActiveSheet.Shapes("btnInternal").OnAction = ActiveWorkbook.btnInternal & "btn_PRKLogInternalTab"
' ActiveSheet.Shapes("btnInternal").DrawingObject.Font.ColorIndex = 1
End Sub
Sub HideInternalBtn()
' Dim WS As Worksheet: Set WS = ActiveSheet
' Dim Shp As Shape: Set Shp = WS.Shapes("btnInternal")
'If cbInternal = True Then
' Shp.Visible = True
'End If
'If cbInternal = False Then
' Shp.Visible = False
'End If
End Sub
Sub HideLikeFundBtn()
If Range("c14").Select = True Then
'If cbExternal = True Then
Call btn_LikeFundTab
Else
If Range("c14").Select = False Then
'If cbExternal = False Then
Call GreyOutbtnLikefundTab
End If
End Sub

--------------------------
cbExternal or anything of the like are the names of my check boxes.

btn* are the names of my buttons.

The point is that I don't want the buttons/controls to be clickable unless they select the check box next to them first.

The best I can make happen so far is to get the font to turn grey "implying" that the button is inactive, but it still works. Then I tried one where Shp.Visible = False, but that only deleted the shape and now it is gone. Grr.

 

[NARAYANK991]

Hi Ann ,

Are your controls Form controls or ActiveX controls ?

Using the .Enabled property of ActiveX controls ensures that they will not respond to user inputs.

Narayan

 

[CorrieAnn Gillen]

They are form controls

 

[NARAYANK991]

Hi CorrieAnn ,

Form controls have 2 properties ; the .Enabled property retains the control on the sheet even when you toggle it False , while the .Visible property obviously hides ( not deletes ) the control when you toggle it False.

Toggling the .Enable property False prevents the user from clicking on the control.

Toggling the .Visible property True brings the control back on the sheet.

I don't see any problem in using either of these properties to get what you want.

Narayan

 

[CorrieAnn Gillen]

Keeping my desired result in mind, this is where I am landing:

Sub BtnToggle()
With ActiveSheet.Shapes("btnInternalMerger")
If ActiveSheet.Range("Y15") = False Then
.DrawingObject.Font.ColorIndex = 15
Call DoNothing
Else
.DrawingObject.Font.ColorIndex = 1
  Call btn_PRKLogInternalTab
End If
End With
End Sub

 Sub DoNothing()
End Sub

I decided to test for the "TRUE" "FALSE" condition of the check box. This just seemed easier than calling on the checkbox itself. That is the Y15 reference in my code.

So, if the checkbox is FALSE (unchecked) grey out the button and call on the DoNothing Sub. ----This part works GREAT!!! The button does nothing when clicked and is greyed out.

However, if the condition is TRUE, clicking on the checkbox automatically runs the btn_PRKLogInternalTab Sub. My intended result was to assign and activate the btn_PRKLogInternalTab sub to the btnInternal control. I do not want the sub to run when the checkbox is clicked.

Can someone help me with this last piece?

 

[CorrieAnn Gillen]

OK, I got it. For those wanting to know:

Sub BtnToggle()
 
'This just toggles the ColorIndex
With ActiveSheet.Shapes("btnInternalMerger")
If ActiveSheet.Range("Y15") = False Then
.DrawingObject.Font.ColorIndex = 15
Else
.DrawingObject.Font.ColorIndex = 1
End If
End With
End Sub

Sub btn_PRKLogInternalTab()
 
'This looks to cell Y15 to run the macro.
If ActiveSheet.Range("Y15") = True Then
  Sheets("PRK Log IN-ternal").Select
  Range("A1").Select
End If
End Sub

There are many ways to skin a cat!