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

[Brooks70459]

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.

 

[ColinKJ]

Hi Brooks70459,

If you want to take a look, I put together an example WB named
"Enter In C - TabName In G.xls" which you can download from:

http://www.box.net/shared/yyuzgkly1r

If you enter something in a cell in Column C, the Tab name will appear in Column G in the same row.

ColinKJ

 

[Brooks70459]

I finally opted for this solution. This worked great. I found the Private / Sub area to be very powerful and I see the potential in using it.

Thanks (both of you)

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]

Colin,

Can I do the same thing using filename (parsing-out the .xls) in lieu of tab name?

I want to add it to the code if possible?

Good luck and thanks

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

 

[ColinKJ]

Try replacing the code you have with this:

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

ColinKJ

 

[Brooks70459]

That worked great Colin; Thanks. Would I add a trim right function here, or would it be cleaner to simply add it as a seperate (hand driven) macro?

I want to remove the .xls extention.

-Brooks

 

[lenze]

Just use

If Target.Column = 3 Then Cells(Target.Row, 7) = LEFT(ThisWorkbook.Name,LEN(ThisWorkBook.Name)-4)

You can also use FullName to get the path!

lenze