Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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 "Jim" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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? "Patrick Molloy" wrote in message ... 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 "Jim" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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. -- HTH... Jim Thomlinson "Jim" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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. "Patrick Molloy" wrote: 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 "Jim" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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. "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Jim" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
you assigned the macro to the control?
"Jim" wrote in message ... 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. "Patrick Molloy" wrote: 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 "Jim" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control vs. Active X Controls
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. Jim wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active X Controls Tabbing from Form Field 2 Form Field | Excel Discussion (Misc queries) | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
View Control of Form Controls | Excel Programming | |||
Controls Toolbox control vs Form Toolbox control | Excel Programming | |||
Tool Tip Text for Form control/ Active-X control | Excel Programming |