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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top