I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.

In your first sentence you say that you type the entry in C5. In your last
sentence, you say that a VLOOKUP formula put the information in C5. It
can't be both.
You say that Excel does not "see" the C5 entry. What do you mean? What
does Excel do when you type the C5 entry and hit Enter? Is there an error
message? You should also add the following line as the first line of your
macro to handle the situation of the user deleting the contents of C5.
If Target="" Then Exit Sub
HTH Otto
"Mike R" wrote in message
...
I have been using the following code to pull information from a cell to
name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5.
I
have tried to change the reference from "C5" to "DataSheet!B3" and that
does
not work either. I have tried to copy and paste / Paste special / pasted
link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with
a
VLOOKUP formula to the sheet tab?

Mike R.

Otto

Thanks for the post back and I am sorry for the lack of communication.
In the beginning I would type into C5 and the code named the tab. This shows
me the code does work. Then I added a worksheet containing a table.
This table contains the information I want on all employees.
I used VLOOKUP to fill C5. The formula now in C5 is:
=VLOOKUP('Data Input'!B6,'Data Input'!$B$6:$I$17,2,FALSE).
Now I do get the desired information into C5 but the name of the Tab does
not change. There are no errors or indicators that something is not working.
The formula does what the formula is supposed to do. But for reasons not
known to me the code does not "see" "trigger" or whatever it does to rename
the tab. I hope this helps, and thanks for anything you can offer.

Mike R.


"Otto Moehrbach" wrote:

In your first sentence you say that you type the entry in C5. In your last
sentence, you say that a VLOOKUP formula put the information in C5. It
can't be both.
You say that Excel does not "see" the C5 entry. What do you mean? What
does Excel do when you type the C5 entry and hit Enter? Is there an error
message? You should also add the following line as the first line of your
macro to handle the situation of the user deleting the contents of C5.
If Target="" Then Exit Sub
HTH Otto
"Mike R" wrote in message
...
I have been using the following code to pull information from a cell to
name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5.
I
have tried to change the reference from "C5" to "DataSheet!B3" and that
does
not work either. I have tried to copy and paste / Paste special / pasted
link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with
a
VLOOKUP formula to the sheet tab?

Mike R.

The reason this macro is no longer working is that it is dependent on the
Worksheet_Change event firing. You are no longer changing anything in the
target cell - the value may change but the formula is static. You need to
find a new way to activate the change to the sheet name.

Hope this helps
Rowan

"Mike R" wrote:

I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.

Rowan, Thanks for the post I appreciate it!
I have come to the same conclusion, but not being a VBA guy I have no idea
of how to get it to trigger. I don't really care what the method is, just
the results, Is there any other way to get the the contents of C5 to the
Sheet tab?

"Rowan" wrote:

The reason this macro is no longer working is that it is dependent on the
Worksheet_Change event firing. You are no longer changing anything in the
target cell - the value may change but the formula is static. You need to
find a new way to activate the change to the sheet name.

Hope this helps
Rowan

"Mike R" wrote:

I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.

Mike

You could use the Worksheet_Calculate event. This is triggered every time
any cell on the sheet is calculated - which will happen every time your
vlookup result changes. So the macro would look something like:

Private Sub Worksheet_Calculate()
If Not IsError(Range("C5").Value) Then
If Range("C5").Value < Me.Name And _
Range("C5").Value < Empty Then
Me.Name = Range("C5").Value
End If
End If
End Sub

Place this in the sheets code module (the same place as the Worksheet_Change
event is located.

Hope this helps
Rowan

"Mike R" wrote:

Rowan, Thanks for the post I appreciate it!
I have come to the same conclusion, but not being a VBA guy I have no idea
of how to get it to trigger. I don't really care what the method is, just
the results, Is there any other way to get the the contents of C5 to the
Sheet tab?

"Rowan" wrote:

The reason this macro is no longer working is that it is dependent on the
Worksheet_Change event firing. You are no longer changing anything in the
target cell - the value may change but the formula is static. You need to
find a new way to activate the change to the sheet name.

Hope this helps
Rowan

"Mike R" wrote:

I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.

Rown Thanks for the post, and the code does EXAXTLY what I need!!!!
I did find the following code to place in the Data Input Sheet and it would
also work if I could figure out how to start it on C6 and not A4. I will just
place your code in the necessary places and give you the biggest thanks
possible!!!! This little addition concludes this project. (except for making
it presentable)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
If Not Intersect(Target, Range("A4").Resize( _
Worksheets.Count, 1)) Is Nothing Then
On Error Resume Next 'in case invalid sheet name
For i = 2 To Worksheets.Count
Sheets(i).Name = Cells(i + 2, 1).Value
Next i
On Error GoTo 0
End If
End Sub

Thanks again

Mike R.
"Rowan" wrote:

Mike

You could use the Worksheet_Calculate event. This is triggered every time
any cell on the sheet is calculated - which will happen every time your
vlookup result changes. So the macro would look something like:

Private Sub Worksheet_Calculate()
If Not IsError(Range("C5").Value) Then
If Range("C5").Value < Me.Name And _
Range("C5").Value < Empty Then
Me.Name = Range("C5").Value
End If
End If
End Sub

Place this in the sheets code module (the same place as the Worksheet_Change
event is located.

Hope this helps
Rowan

"Mike R" wrote:

Rowan, Thanks for the post I appreciate it!
I have come to the same conclusion, but not being a VBA guy I have no idea
of how to get it to trigger. I don't really care what the method is, just
the results, Is there any other way to get the the contents of C5 to the
Sheet tab?

"Rowan" wrote:

The reason this macro is no longer working is that it is dependent on the
Worksheet_Change event firing. You are no longer changing anything in the
target cell - the value may change but the formula is static. You need to
find a new way to activate the change to the sheet name.

Hope this helps
Rowan

"Mike R" wrote:

I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.

You're welcome.

"Mike R" wrote:

Rown Thanks for the post, and the code does EXAXTLY what I need!!!!
I did find the following code to place in the Data Input Sheet and it would
also work if I could figure out how to start it on C6 and not A4. I will just
place your code in the necessary places and give you the biggest thanks
possible!!!! This little addition concludes this project. (except for making
it presentable)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
If Not Intersect(Target, Range("A4").Resize( _
Worksheets.Count, 1)) Is Nothing Then
On Error Resume Next 'in case invalid sheet name
For i = 2 To Worksheets.Count
Sheets(i).Name = Cells(i + 2, 1).Value
Next i
On Error GoTo 0
End If
End Sub

Thanks again

Mike R.
"Rowan" wrote:

Mike

You could use the Worksheet_Calculate event. This is triggered every time
any cell on the sheet is calculated - which will happen every time your
vlookup result changes. So the macro would look something like:

Private Sub Worksheet_Calculate()
If Not IsError(Range("C5").Value) Then
If Range("C5").Value < Me.Name And _
Range("C5").Value < Empty Then
Me.Name = Range("C5").Value
End If
End If
End Sub

Place this in the sheets code module (the same place as the Worksheet_Change
event is located.

Hope this helps
Rowan

"Mike R" wrote:

Rowan, Thanks for the post I appreciate it!
I have come to the same conclusion, but not being a VBA guy I have no idea
of how to get it to trigger. I don't really care what the method is, just
the results, Is there any other way to get the the contents of C5 to the
Sheet tab?

"Rowan" wrote:

The reason this macro is no longer working is that it is dependent on the
Worksheet_Change event firing. You are no longer changing anything in the
target cell - the value may change but the formula is static. You need to
find a new way to activate the change to the sheet name.

Hope this helps
Rowan

"Mike R" wrote:

I have been using the following code to pull information from a cell to name
the sheet tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ' are we in client
cell name?
If ActiveSheet.Name = Sheet1.Name Then ' in case of grouped sheets
ActiveSheet.Name = Target.Value
End If
End If
End Sub


This code has always worked as long as I type the information into C5, in
this case that is the name of an employee. I built a table with all the
employee information I wanted and used VLOOKUP to place the data in the
relative sheets. Now the code does not "see" the information placed in C5. I
have tried to change the reference from "C5" to "DataSheet!B3" and that does
not work either. I have tried to copy and paste / Paste special / pasted link
and then reference that cell in the code. that does not work either. Is
there anyway to get the information in C5 that has been placed there with a
VLOOKUP formula to the sheet tab?

Mike R.