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

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

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

Try referencing the Name property.

If Sh.Name = "Star-plot" Then

It's worksheets.
The Text property will use whatever is displayed in the cell.
The Value property will use the value in the cell.

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

If Summary_Worksheet is the name on the sheet tab then it should be in
quotes. Otherwise it will be treated as a variable.

You can only use the Chart_Activate event if the sheet is a chart sheet or
you write class code to capture the events of a chart object.

You could move the code to the Worksheet_Activate event of the worksheet
containing the chart object. But this will only run the code when the select
another sheet and come back to the Star-Plot sheet.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Naum" wrote in message
...
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

YES! Now it works. I referenced the Name property as you suggested and put
Summary_Worksheet in quotes as it is indeed the name on the sheet tab.
The text property works fine for me.
I decided not to play with Chart_Activate event since what I have now works,
but will keep that in mind - Star-plot is a chart sheet. That chart is built
using data from Summary_Worksheet...
Thanks again for your help.

"Andy Pope" wrote:

Try referencing the Name property.

If Sh.Name = "Star-plot" Then

It's worksheets.
The Text property will use whatever is displayed in the cell.
The Value property will use the value in the cell.

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

If Summary_Worksheet is the name on the sheet tab then it should be in
quotes. Otherwise it will be treated as a variable.

You can only use the Chart_Activate event if the sheet is a chart sheet or
you write class code to capture the events of a chart object.

You could move the code to the Worksheet_Activate event of the worksheet
containing the chart object. But this will only run the code when the select
another sheet and come back to the Star-Plot sheet.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Naum" wrote in message
...
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

I am posting two new questions in case you will be able to take a look...
almost done with this Excel project...

How to use color from a datasheet to be applied to same series on different
charts?

How to colntrol location of category X axis labels on a bar chart?

Thanks!

"Naum" wrote:

YES! Now it works. I referenced the Name property as you suggested and put
Summary_Worksheet in quotes as it is indeed the name on the sheet tab.
The text property works fine for me.
I decided not to play with Chart_Activate event since what I have now works,
but will keep that in mind - Star-plot is a chart sheet. That chart is built
using data from Summary_Worksheet...
Thanks again for your help.

"Andy Pope" wrote:

Try referencing the Name property.

If Sh.Name = "Star-plot" Then

It's worksheets.
The Text property will use whatever is displayed in the cell.
The Value property will use the value in the cell.

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

If Summary_Worksheet is the name on the sheet tab then it should be in
quotes. Otherwise it will be treated as a variable.

You can only use the Chart_Activate event if the sheet is a chart sheet or
you write class code to capture the events of a chart object.

You could move the code to the Worksheet_Activate event of the worksheet
containing the chart object. But this will only run the code when the select
another sheet and come back to the Star-Plot sheet.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Naum" wrote in message
...
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