looking for a way to ref / link tab name in a cell

Brooks70459

New Member
Joined
Jun 25, 2009
Messages
43
I hope that I am explaining this correctly.

I am about to audit a factory. I am building a standard form in an Excel workbook to collect the data. I will initially have fifteen workbooks. Each workbook will represent a specific area in the factory. Each workbook has about fifty worksheets, each a different (sub)assembly.

What I would like to do is make a template that would have a function that would populate a cell in (let's say Column G) with its respective worksheet tab name. It would be nice to this automatically occur when data is entered into Col. C the same row.

Your help is appreciated.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Brooks70459,

I hope I've understood your requirements correctly.

try putting this in each worksheet "Private Sub Worksheet_Change"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Cells(Target.Row, 7) = ActiveSheet.Name
End Sub

When you make an entry in a cell in column C, the Sheet Name is automatically entered into Column G on the same row.

Regards

ColinKJ
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If I have understood correctly, right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(, 4).Value = Me.Name
End Sub
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Use the following formula
=MID(CELL("filename",INDIRECT("a1")),FIND("]",CELL("filename",INDIRECT("a1")))+1,255)
I hope that I am explaining this correctly.

I am about to audit a factory. I am building a standard form in an Excel workbook to collect the data. I will initially have fifteen workbooks. Each workbook will represent a specific area in the factory. Each workbook has about fifty worksheets, each a different (sub)assembly.

What I would like to do is make a template that would have a function that would populate a cell in (let's say Column G) with its respective worksheet tab name. It would be nice to this automatically occur when data is entered into Col. C the same row.

Your help is appreciated.
 

Brooks70459

New Member
Joined
Jun 25, 2009
Messages
43

ADVERTISEMENT

Thanks Colin,

:eek: Call this a senior moment or just way too much coffee, but whre in the heck do I acceess "Private Sub Worksheet_Change".

I cannot for the life of me remember where I need to go yet alone what question to ask... I looked in both the VBA editor and the Macro record. I am simply brain dead. :confused:




Hi Brooks70459,

I hope I've understood your requirements correctly.

try putting this in each worksheet "Private Sub Worksheet_Change"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Cells(Target.Row, 7) = ActiveSheet.Name
End Sub

When you make an entry in a cell in column C, the Sheet Name is automatically entered into Column G on the same row.

Regards

ColinKJ
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
I am curious. Given a non-programmatic solution and the problem you are having using the code, why not use the formula approach? Much simpler, easier to maintain, and no hassles with "this workbook contains macros that might be malicious" warnings!

Thanks Colin,

:eek: Call this a senior moment or just way too much coffee, but whre in the heck do I acceess "Private Sub Worksheet_Change".

I cannot for the life of me remember where I need to go yet alone what question to ask... I looked in both the VBA editor and the Macro record. I am simply brain dead. :confused:
 

Brooks70459

New Member
Joined
Jun 25, 2009
Messages
43

ADVERTISEMENT

Hi Brooks70459,

I hope I've understood your requirements correctly.

try putting this in each worksheet "Private Sub Worksheet_Change"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Cells(Target.Row, 7) = ActiveSheet.Name
End Sub

When you make an entry in a cell in column C, the Sheet Name is automatically entered into Column G on the same row.

Regards

ColinKJ
Okay, remebered whare to go on to input this but still nada. Nothing happened when I typed values into Cell C.

I tried it in both a new Module and as a Class,
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Brooks70459,

If you Right Click on the Tab, then select View Code.

You should see a drop down Box with (General) showing in it. Click in there, then select Worksheet.

This should show you the Private Sub "Worksheet_SelectionChange"

You should now see a drop down Box with "SelectionChange" in it. Click in to this, then select "Change".

This should give you the "Private Sub Worksheet_Change".

Just paste the code in here.

ColinKJ
 

Brooks70459

New Member
Joined
Jun 25, 2009
Messages
43
tushram,

Thanks, I inputted the formula into the cell just as you have it, plus Cntrl-****f-Enter and go the curly-ques and nothing happened. The result was simply the formula as text.



Use the following formula
=MID(CELL("filename",INDIRECT("a1")),FIND("]",CELL("filename",INDIRECT("a1")))+1,255)
 

Brooks70459

New Member
Joined
Jun 25, 2009
Messages
43
Eureka! :ROFLMAO: Thanks

I still would like to understand the programmatic solution though.


Use the following formula
=MID(CELL("filename",INDIRECT("a1")),FIND("]",CELL("filename",INDIRECT("a1")))+1,255)

I am curious. Given a non-programmatic solution and the problem you are having using the code, why not use the formula approach? Much simpler, easier to maintain, and no hassles with "this workbook contains macros that might be malicious" warnings!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top