XL2002 - Worksheet_Activate Question...

[Trevor Williams]

The following code works when run from a Module, but not when it's run from the
Worksheet_Activate area - can you tell me why?

(the Shapes are Check Boxes from the Forms toolbar)

Private Sub Worksheet_Activate()
For i = 1 To 8
ActiveSheet.Shapes("cbx" & i).OLEFormat.Object.Text = Range("myYear")
Next
End Sub

Thanks in advance

Trevor Williams

 

[Dave Peterson]

I didn't try going through the shapes collection.

I'd just use:

Private Sub Worksheet_Activate()
dim i as long
For i = 1 To 8
me.checkboxes("cbx" & i).caption = me.Range("myYear").value
Next i
End Sub

myYear is a single cell range on the same sheet, right?

 

[JLGWhiz]

Using the checkboxes from the forms toolbar, and putting the code in the
module for the sheet containing the checkboxes, it worked for me when I
switched from that sheet to another, then back. It did not work with the
checkboxes from the Control toolbox.

 

[Trevor Williams]

Hi Dave - thanks for the response.

The range named "myYear" is stored on a different sheet, and as I didn't ref
that sheet it returned an error. Your code is a cleaner / quicker version.
Thanks again

Trevor