Hi,

Can anyone help? i have this code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub

But it is giving me this error message

Error Number:- 91

Error:= Object Variable or With block variable not set

Thanks Guys i know i can always count on you.

Cheers


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Two questions:

#1 - this code is in the Workbook's code module, right?
#2 - What is in cell B9?

If B9 is empty you can get an error (I actually get 1004), but... knowing
what's in B9 will help.

What is it you are attempting to do here? I realize you're trying to set
the sheet's tab name to the right 4 characters of cell B9, but I'm curious as
to why it is attached to the _SheetCalculate function, which could result in
it running quite often.

"Tivpine" wrote:

Hi,

Can anyone help? i have this code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub

But it is giving me this error message

Error Number:- 91

Error:= Object Variable or With block variable not set

Thanks Guys i know i can always count on you.

Cheers


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi,

B9 has my student matriculation numbers UJ/2004/SS/3333, i actually have it
feeding from another worksheet called "class list" i.e formula ='Class
List'!B9. What am hoping to achieve here is have the four last numbers "3333"
written to the sheet tab. Yes it is in workbook code module. I could'nt
figure out whether it should be a workbook or worksheet/calculate level
activity.

Thanks

Tivpine


"Tivpine" wrote:

Hi,

Can anyone help? i have this code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub

But it is giving me this error message

Error Number:- 91

Error:= Object Variable or With block variable not set

Thanks Guys i know i can always count on you.

Cheers


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Here's something you might try and see how it works: try using the individual
worksheet's _Activate() property. If you have lots of students to do this
with, you could set up one blank one with the code in it and then just copy
it as a new sheet for new class members. It won't necessarily automatically
update when a change is made on the source sheet (Class List), but it will
update when you click on the sheet to activate it and look at it. But then
it doesn't look like _SheetCalculate on the sheet in question when when you
change the value back in the Class List sheet either. This would require you
to choose a sheet in order to update the tab name - and if you're already on
a sheet and just type in the formula into it's cell, you'd have to click
another and then come back to it to see the tab name change. This code would
be duplicated in each sheet's code module:

Private Sub Worksheet_Activate()
If Not IsEmpty(Range("B9")) Then
If Range("B9") 0 then
On Error Resume Next
ActiveSheet.Name = Right(Range("B9"), 4)
On Error GoTo 0
End If
End If
End Sub

In the meantime, I found this code to work within the
Workbook_SheetCalculate() event code section - how often it gets called or
how much time it eats up just depends on the amount of calculation going on
on those sheets. But it could also cause some strange things to happen on
other sheets, such as the Class List sheet itself?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Not IsEmpty(Sh.Range("B9")) Then
If Sh.Range("B9").Value 0 Then
On Error Resume Next
Sh.Name = Right(Sh.Range("B9"), 4)
On Error GoTo 0
End If
End If
End Sub




"Tivpine" wrote:

Hi,

B9 has my student matriculation numbers UJ/2004/SS/3333, i actually have it
feeding from another worksheet called "class list" i.e formula ='Class
List'!B9. What am hoping to achieve here is have the four last numbers "3333"
written to the sheet tab. Yes it is in workbook code module. I could'nt
figure out whether it should be a workbook or worksheet/calculate level
activity.

Thanks

Tivpine


"Tivpine" wrote:

Hi,

Can anyone help? i have this code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub

But it is giving me this error message

Error Number:- 91

Error:= Object Variable or With block variable not set

Thanks Guys i know i can always count on you.

Cheers


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi,

Thank YOU sooooooooooooooooooooooo much the later part of your suggestion
worked. Except that the Error Message is still popping up when Excel starts
up.

Cheers

"JLatham" wrote:

Here's something you might try and see how it works: try using the individual
worksheet's _Activate() property. If you have lots of students to do this
with, you could set up one blank one with the code in it and then just copy
it as a new sheet for new class members. It won't necessarily automatically
update when a change is made on the source sheet (Class List), but it will
update when you click on the sheet to activate it and look at it. But then
it doesn't look like _SheetCalculate on the sheet in question when when you
change the value back in the Class List sheet either. This would require you
to choose a sheet in order to update the tab name - and if you're already on
a sheet and just type in the formula into it's cell, you'd have to click
another and then come back to it to see the tab name change. This code would
be duplicated in each sheet's code module:

Private Sub Worksheet_Activate()
If Not IsEmpty(Range("B9")) Then
If Range("B9") 0 then
On Error Resume Next
ActiveSheet.Name = Right(Range("B9"), 4)
On Error GoTo 0
End If
End If
End Sub

In the meantime, I found this code to work within the
Workbook_SheetCalculate() event code section - how often it gets called or
how much time it eats up just depends on the amount of calculation going on
on those sheets. But it could also cause some strange things to happen on
other sheets, such as the Class List sheet itself?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Not IsEmpty(Sh.Range("B9")) Then
If Sh.Range("B9").Value 0 Then
On Error Resume Next
Sh.Name = Right(Sh.Range("B9"), 4)
On Error GoTo 0
End If
End If
End Sub




"Tivpine" wrote:

Hi,

B9 has my student matriculation numbers UJ/2004/SS/3333, i actually have it
feeding from another worksheet called "class list" i.e formula ='Class
List'!B9. What am hoping to achieve here is have the four last numbers "3333"
written to the sheet tab. Yes it is in workbook code module. I could'nt
figure out whether it should be a workbook or worksheet/calculate level
activity.

Thanks

Tivpine


"Tivpine" wrote:

Hi,

Can anyone help? i have this code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Right(Sh.Range("B9"), 4)
End Sub

But it is giving me this error message

Error Number:- 91

Error:= Object Variable or With block variable not set

Thanks Guys i know i can always count on you.

Cheers


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc