XL2002 - Worksheet_Activate Question...
 

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

XL2002 - Worksheet_Activate Question...
 

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?



Trevor Williams wrote:

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

XL2002 - Worksheet_Activate Question...
 

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" wrote:

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

XL2002 - Worksheet_Activate Question...
 

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

"Dave Peterson" wrote:

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?



Trevor Williams wrote:

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