Display Tab name in cell with additional text 2

[03Explorer]

We have spreadsheets that are check lists. Each tab name is the name of a form being tested. I would like to copy the first tab and replace the sheet name. A particular cell or multiple cells read the sheet name and append to additional text in a cell.

Example: Sheet1Name = "Deviation Form"
Sheet1.Row2.column2 = '="Validation for form named: '" & sheetName & "'"
output for row2.column2 = Validation for form needed: 'Deviation Form'

I did find something some vba code some time ago, but I can't find my notes or the source.

It is to make our validation process a little smoother when we do up to 20 forms and have to go into each cell and enter the new sheetname along with renaming the sheet as well.

Thanx

 

[03Explorer]

I failed to mention this is for Excel 2003. Some things are assumed in my head and never find their way out... LOL

 

[SkipVought]

Hi,

Something like this...

dim ws as worksheet
for each ws in worksheets
  ws.cells(2,2).value = "Validation for form named: " & ws.name
next

Skip,

_[red][/red]

 

[03Explorer]

I thought I had a function I called that returned the sheet name into the cell. Instead of VBA posting the value... the cell called for the value.

Does that make sense?

 

[PHV]

A starting point (need error handling):
Public Function myTest()
Application.Volatile
myTest = "Validation for form named: '" & Application.Caller.Parent.Name & "'"
End Function

Then in B2: =myTest()



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

If you thought you had a FUNCTION, then why didn't you state that up front?

Please be CLEAR, CONCISE and COMPLETE.

Skip,

_[red][/red]

 

[GlennUK]

A function eh? Like the CELL function, do you mean? To make a cell formula like this maybe:

="Validation for form named: '" & RIGHT(CELL("Filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) & "'"

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[03Explorer]

Skip, my apology for not using proper termenology.

Glenn, my goal is to use the name that is statically placed for the Tab name. your code looks like it is trying to do something with a filename.

the process currently is

1. copy tab1 / sheet1 (not sure which termonology to use sheet or tab) as a new sheet/tab.
2. rename the tab
3. go to cell b2 and change the static text that contains the same name as the tab/sheet name.
4. repeat 1-3 for additional tabs/sheets (normally up to 10)

I want to eliminate item 3 with a function or dynamic code that the cell contains most of the text and calls for the tab/sheet name. This seems like it should not be very difficult, but it is.

Thank you for your help.

 

[PHV]

Have you tried to play with my suggestion ?

 

[03Explorer]

I did play with your suggestion.

I opened a new excel document. ALT+F11 for VBA screen. I inserted a module under VBAProject(Book1). I copied and paste:

Public Function myTest()
Application.Volatile
myTest = "Validation for form named: '" & Application.Caller.Parent.Name & "'"
End Function

closed the VBA screen. Went to cell A1 and typed:

=myTest()

Okay this is where funny gets the shaking head at the monitor saying it works this time... but, i will try and change the code so the text is in the cell and the lookup 'tab name' is the dynamic code in the cell. I would rather have a formula that contains static text and a function call to get the 'tab name'.

again... walking through with the directions above is a good method to test something that did not work the day before. argggg.

Thank you, PHV, for the code

 

[CBasicAsslember]

Run this after you have created all the sheets and renamed the tabs.

Sub TabSheetNames()
Dim i As Integer

' Loop through all the sheets
For i = 1 To Sheets.Count
    ' Put sheet tab name into cell B1 on each sheet.
    Sheets(i).Range("B1") = Sheets(i).Name
Next i

End Sub

 

[03Explorer]

I like that... Good for a different application I have in mind (after thought from seeing your code)

the gears are working.

My intentions are to make work do work for me, while I find more work, or different work, to work on while automating that work as well.