Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
Hi there,
(environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
Checkboxes have a LINKEDCELL property. when this property is used it will
change the value of the cell from TRUE to FALSE. the linkedcell will also cause a worksheet change event to occur. You can write a worksheet chagge function to highlight the entire row. sub worksheet_change(ByRef Target as Range) 'Column E will contained linked cell for the check boxes if Target.Column = 5 then if Target = True Rows(target.row).interior.colorindex = 6 else Rows(target.row).interior.colorindex = 3 end if end if end sub "Nayan" wrote: Hi there, (environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
Thanks Joel,
I looked up at LINKEDCELL property of controls and it seems I can assing a cell with a control and use it as you explained. Its clear that using this method I can write code in the worksheet itself to highlight the row. How can I do this from another addin. Since I want to centralized code for the task. Thanks again Nayan "Joel" wrote: Checkboxes have a LINKEDCELL property. when this property is used it will change the value of the cell from TRUE to FALSE. the linkedcell will also cause a worksheet change event to occur. You can write a worksheet chagge function to highlight the entire row. sub worksheet_change(ByRef Target as Range) 'Column E will contained linked cell for the check boxes if Target.Column = 5 then if Target = True Rows(target.row).interior.colorindex = 6 else Rows(target.row).interior.colorindex = 3 end if end if end sub "Nayan" wrote: Hi there, (environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
You can manually set the linkedcell property from the worksheet instead of
excel. If you add the control toolbox, the property box can be manually set when you are in design mode (triangle on the too bar). The answer to question below depends if you want want the highlighting of the row to occur when the check box is checked or later when you manually run your macro. worksheet change will automatically run when the box is checked or unchecked. From another addin you don't need to use the linkedcell because you can read the value of the checked box directly. The value of the checkbox will be either a True or False. You said you knew which row each checkbox was located. Alternately you can simply check the value (True or False) in each cell of a column (linked column) and check for true or false and highlight the row LastRow = cells(rows.Count,"E").end(xlup).row for RowCount = 1 to LastRow if cells(RowCount,"E").value = true then rows(RowCount).interior.colorindex = 6 else rows(RowCount).interior.colorindex = 3 end if next Row "Nayan" wrote: Thanks Joel, I looked up at LINKEDCELL property of controls and it seems I can assing a cell with a control and use it as you explained. Its clear that using this method I can write code in the worksheet itself to highlight the row. How can I do this from another addin. Since I want to centralized code for the task. Thanks again Nayan "Joel" wrote: Checkboxes have a LINKEDCELL property. when this property is used it will change the value of the cell from TRUE to FALSE. the linkedcell will also cause a worksheet change event to occur. You can write a worksheet chagge function to highlight the entire row. sub worksheet_change(ByRef Target as Range) 'Column E will contained linked cell for the check boxes if Target.Column = 5 then if Target = True Rows(target.row).interior.colorindex = 6 else Rows(target.row).interior.colorindex = 3 end if end if end sub "Nayan" wrote: Hi there, (environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
Joel,
Thanks for the detailed reply. Few points I wanted to share with you. 1) I am adding Check boxes at run time (it can be up to 500) so setting linked cell property manually for all 500 chk boxes may be time consuming. 2) I want a single row to be highlighted when a check box is checked off. So if I set linkedcell property I can trap worksheet change event in the workbook that contains the checkbox. But I want to control this event from external addin. this is where it gets challenging. Any Idea as how can I set background of a row from external addin ? Thanks again Nayan "Joel" wrote: You can manually set the linkedcell property from the worksheet instead of excel. If you add the control toolbox, the property box can be manually set when you are in design mode (triangle on the too bar). The answer to question below depends if you want want the highlighting of the row to occur when the check box is checked or later when you manually run your macro. worksheet change will automatically run when the box is checked or unchecked. From another addin you don't need to use the linkedcell because you can read the value of the checked box directly. The value of the checkbox will be either a True or False. You said you knew which row each checkbox was located. Alternately you can simply check the value (True or False) in each cell of a column (linked column) and check for true or false and highlight the row LastRow = cells(rows.Count,"E").end(xlup).row for RowCount = 1 to LastRow if cells(RowCount,"E").value = true then rows(RowCount).interior.colorindex = 6 else rows(RowCount).interior.colorindex = 3 end if next Row "Nayan" wrote: Thanks Joel, I looked up at LINKEDCELL property of controls and it seems I can assing a cell with a control and use it as you explained. Its clear that using this method I can write code in the worksheet itself to highlight the row. How can I do this from another addin. Since I want to centralized code for the task. Thanks again Nayan "Joel" wrote: Checkboxes have a LINKEDCELL property. when this property is used it will change the value of the cell from TRUE to FALSE. the linkedcell will also cause a worksheet change event to occur. You can write a worksheet chagge function to highlight the entire row. sub worksheet_change(ByRef Target as Range) 'Column E will contained linked cell for the check boxes if Target.Column = 5 then if Target = True Rows(target.row).interior.colorindex = 6 else Rows(target.row).interior.colorindex = 3 end if end if end sub "Nayan" wrote: Hi there, (environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Visual Basic Check bo contrls
Post you question as a new Posting
"Nayan" wrote: Joel, Thanks for the detailed reply. Few points I wanted to share with you. 1) I am adding Check boxes at run time (it can be up to 500) so setting linked cell property manually for all 500 chk boxes may be time consuming. 2) I want a single row to be highlighted when a check box is checked off. So if I set linkedcell property I can trap worksheet change event in the workbook that contains the checkbox. But I want to control this event from external addin. this is where it gets challenging. Any Idea as how can I set background of a row from external addin ? Thanks again Nayan "Joel" wrote: You can manually set the linkedcell property from the worksheet instead of excel. If you add the control toolbox, the property box can be manually set when you are in design mode (triangle on the too bar). The answer to question below depends if you want want the highlighting of the row to occur when the check box is checked or later when you manually run your macro. worksheet change will automatically run when the box is checked or unchecked. From another addin you don't need to use the linkedcell because you can read the value of the checked box directly. The value of the checkbox will be either a True or False. You said you knew which row each checkbox was located. Alternately you can simply check the value (True or False) in each cell of a column (linked column) and check for true or false and highlight the row LastRow = cells(rows.Count,"E").end(xlup).row for RowCount = 1 to LastRow if cells(RowCount,"E").value = true then rows(RowCount).interior.colorindex = 6 else rows(RowCount).interior.colorindex = 3 end if next Row "Nayan" wrote: Thanks Joel, I looked up at LINKEDCELL property of controls and it seems I can assing a cell with a control and use it as you explained. Its clear that using this method I can write code in the worksheet itself to highlight the row. How can I do this from another addin. Since I want to centralized code for the task. Thanks again Nayan "Joel" wrote: Checkboxes have a LINKEDCELL property. when this property is used it will change the value of the cell from TRUE to FALSE. the linkedcell will also cause a worksheet change event to occur. You can write a worksheet chagge function to highlight the entire row. sub worksheet_change(ByRef Target as Range) 'Column E will contained linked cell for the check boxes if Target.Column = 5 then if Target = True Rows(target.row).interior.colorindex = 6 else Rows(target.row).interior.colorindex = 3 end if end if end sub "Nayan" wrote: Hi there, (environment Excel 2003: Windows XP professional) I have a list of Checkbox controls (OLEObjects) in one of the column in workbook1.sheet1. From another addin (say Admin.xla) I want to cotrol click event of a check box that is in workbook1.sheet1. Objective is to change (from Admin.xla) the background color of the row (in workbook1.sheet1) for which the check box is seleted ( in workbook1.sheet1). I am adding these controls at run time when the workbook1.sheet1 is opened. And the number of check boxes added could be from 10 to 500 as per the records in the sheet. Check boxes are named at run time. for eg Checkbox added in row 5 and Col 6 is named as R5C6 programmatically. Any idea ?????? Thanks in advance. Nayan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic code not working when sheet is saved | Excel Discussion (Misc queries) | |||
Visual Basic suddenly stopped working | Excel Programming | |||
Visual basic Check Box and the IF formula | Excel Discussion (Misc queries) | |||
Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
Activate check "Trust access to Visual Basic " in Excel | New Users to Excel |