Form Control vs. Active X Controls

[Jim]

I'm using Excel 2007 and want to put a check box above a range of cells that
I want to assign two macros, one for checked, one for unchecked. I struggled
for hours to get it working until I discovered I was using a Form Control
Check Box, not an Active X Control Check Box.

Using an Active X check box (Developer ribbon, controls, insert active x
check box) and the following code I can made it work:

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Hide_Loan_Details
Else
Show_Loan_Details
End If
End Sub

However I do prefer the Form Control Check box as it looks better, can be
filled with colours, etc. I've tried the following code and cannot make it
work:

Private Sub LoanDetailCheckbox_Change()
If LoanDetailCheckbox.Value = xlOn Then
Show_Loan_Details
Else
Hide_Loan_Details
End If
End Sub

Is it possible to use the Form Control Check box in my worksheet?

 

[Patrick Molloy]

the control needs to be linked to a cell, so when clicked, the target cell
will be TRUE or FALSE
put your code into a standard module as assign your control's Assign Macro
property to it

eg
PUBLIC Sub CheckBox1_Click()
If worksheets("sheet1").Range("A1").Value Then
Hide_Loan_Details
Else
Show_Loan_Details
End If
End Sub

 

[Patrick Molloy]

I used the same procedure name, CheckBox1_Click, for ease of reference, but
I should state that this is a form control, and thus this isn't an "event"
driven code, so that's why is shouldn't be on the worksheet code page, but
it should be in a standard code module.

does this make sense?

 

[Jim Thomlinson]

Controls from the forms toolbar link to code in standard code modules. When
you add the control you will be prompted to link to the macro. The code that
you have is not too far off except that I believe you will need to explicitly
reference the control including the sheet that it is on (not to sure about
that as I generally use the active x controls).

Your reason for wanting to use the forms control makes no sense to me
however. There are a lot more options with the acitive x controls. Put your
sheet inot desing mode. Right click the control and select properties. There
are a lot more options here than for a forms control. Additionally it is easy
to change the properties at run time with code. The forms control formats are
not as easy.

 

[Jim]

Patrick,

I'm struggling with your code, nothing happens. I'm not a VBA guy, please
review:

Public Sub LoanDetailCheckbox_Click()
If Worksheets("Loan Data").Range("$F$2").Value Then
Hide_Loan_Details
Else
Show_Loan_Details
End If
End Sub


The Sheet name is 'Loan Data'. The Form Control Check Box name is
'LoanDetailCheckbox'. The cell where TRUE or FALSE is displayed is F2.

Thanks for the quick reply.

 

[Jim]

Jim,

The suggestion to put the sheet into design mode and accessing the
properties of the Active X control is the solution. I didn't see that option
and thus I could not change the background colours so easily, definately the
way forward, thank you.

 

[Patrick Molloy]

you assigned the macro to the control?

Patrick,

I'm struggling with your code, nothing happens. I'm not a VBA guy, please
review:

Public Sub LoanDetailCheckbox_Click()
If Worksheets("Loan Data").Range("$F$2").Value Then
Hide_Loan_Details
Else
Show_Loan_Details
End If
End Sub


The Sheet name is 'Loan Data'. The Form Control Check Box name is
'LoanDetailCheckbox'. The cell where TRUE or FALSE is displayed is F2.

Thanks for the quick reply.

 

[Dave Peterson]

Personally, I like the controls from the Forms toolbar better. I think that the
behave better and have the added benefit that you can assign the same macro to a
bunch of those controls.

But the code for the Forms toolbar controls go into a General module (not behind
the worksheet, not behind the ThisWorkbook).

And then you can rightclick on the checkbox and choose Assign macro.

But you're right, the controls from the Forms toolbar can't be customized as the
ActiveX/control toolbox toolbar controls.