Hi, Andy,

Sorry it took me so long to respond - get distracted by another project...

I put following code in ThisWorkbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh = "Star-plot" Then
With ActiveSheet.Shapes("Check Box 46")
.TextFrame.Characters.Text =
Worksheets(Summary_Worksheet).Range("AC40").Text
End With
End If

End Sub

The plan was to assign text from AC40 on Summary_Worksheet to the text
property of check box "check box 46". text box is part of a chart, located on
worksheet "Star-plot". When I click (activate) any worksheet, I am getting
error message on the line
If Sh = "Star-plot" Then
Object does not support this property or method. Obviously I do not
understand how to address the parameter I am getting when event is fired (it
is a worksheet name, is it?). Please, make it a bit simpler for the me.

Another line where I do not feel comfortable is

Worksheets(Summary_Worksheet).Range("AC40").Text

Is that a correct way to get value from a cell on another worksheet? Should
it be "Worksheets" or "Worksheet"? Should I put worksheet name
(Summary_Worksheet) in quotes? Double quotes?

Could I instead of putting that code into ThisWorkbook, use Chart_Activate
event and put it right into Star-plot worksheet code? Then I do not have to
determine which worksheet got active and can just grab the value from AC40 of
Summary_Worksheet?

I am posting yet another question (How to change check box name using mouse
and keyboard) - please, take a look when you have a minute.

Thank you very much.
Naum


"Andy Pope" wrote:

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