Hi,

This sets the caption of a checkbox from the Forms toolbar.

With ActiveSheet.Shapes("Check Box 1")
.TextFrame.Characters.Text = Range("B2").Text
End With

You could use the SheetActivate event which is in the Thisworkbook
object. You can use the Sh object to determine which sheet is now active
and populate the controls on it.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

' do some code depending on Sh

End Sub

Cheers
Andy

Naum wrote:
I am building an Excel template. It has one worksheet with data (called
"data") and many worksheets with craphics, built from the "data" sheet. One
sheet with radar-type chart has number of check boxes which control
hiding/unhiding columns. When a cloumn is hided, it does not produce a line
on radar chart or a bar on bar charts. In the legend area user can see names
of all series (=columns), used to build charts. But check boxes have their
texts hard coded.

I need to code a subroutine, which will be executed when a sheet with radar
chart will open. It should assign values to text properties of check boxes
taking, say, text for 1st check box from cell "$I$67", for second from
"$I$68", for 3rd from "$I$69" and so on (I plan to include about 30 of them).
The subroutine can not be executed when the template itself is loaded because
its "data" sheet will be initially empty. Users will PASTE data in it _after_
opening the template (obviously)...

I understand that I can dedicate say 1st checkbox to populate text
properties of other checkboxes, but do not know how to assign a text property
of a check box value from a specific cell on the "data" sheet.

So, my question is actually two fold:

- how to build a code which will be executed upon opening a specific sheet
of a workbook;
- how to set a text property of a check box to value from a specific cell on
the "data" sheet.

Thank you in advance.
Naum