Working with Visual Basic Check bo contrls

[Nayan]

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

 

[Joel]

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]

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]

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]

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]

Post you question as a new Posting

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