VBA changing Worksheet Names from a Lookup formula (list)

[MickiZ]

Hi,
I'm trying to solve a problem with what I hope will be an easy fix. I manage a lab where we have to do lots of data entry for multiple tests. I create excel templates that the user (lab techs) must fill-in each template's set up page with the QA tracking ID (its' a combination of the Lab notebook,Page, & code i.e., EXP20-AA1234-A, EXP20-AA1234-B, etc.). Depending on the test method and the associated instrument, each data entry processes differ.

For one such test method, I need the template to rename the individual worksheets (tabs) used for importing the data into for each unique code. Leaving this up to the lab techs has proven problematic, in that they frequent forget to rename each tab; or they enter the wrong sample ID.

On the setup tab, I've got a formula that creates each sample name to be used; and named each of the resulting names for lookup (e.g., first generated name ="Tab_01", second generated name ="Tab_02", etc. up to Tab_25), and then I've linked each with a formula in cells A1 for each of the 25 tabs in the workbook template.

The problem is... the VBA code below only works with hard text in cell A1 but not a lookup formula. Is there some way to write the VBA code so that if the lookup value does not equal "Tab_01", etc. (for each of the 25 tabs) that it will change the sheet name to the resulting tab ID name? -or possibly be written to execute a macro to paste value(s) once it's changed?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("A1")
End If
End Sub

I'm not proficient with VBA code, so detailed solution example would be appreciated.
Thanks in advanced for any assistance.
Michele

 

[JackDanIce]

Welcome to the board.

I think you need to use Worksheet_Calculate for this, try:

Private Sub Worksheet_Calculate()

    ActiveSheet.Name = Range("A1").Value

End Sub

What you were using, Worksheet_Change, only triggers when the cell contents change, i.e. why when you hard code A1

When you use a formula, the formula doesn't change, just it's result - which is what's displayed in the cell. E.g. if A1 is =X+Y, even if X and Y change, the formula doesn't not.

Comparison, person wears an outfit on day 1 then a new outfit on day 2
Your code is waiting for *the* person to change (e.g hair colour, height, weight, etc) but your expectation is "Why doesn't changing their clothes trigger the action I want?"

Hence difference between Worksheet_Change vs Worksheet_Calculate.

 

[MickiZ]

Hi, I tried your recommendation but it still didn't work. Here's how I've setup my workbook... Spreadsheet Setup TAB has a column that's normally hidden from user called Worksheet Tab Name that defines the unique ID's required by the Test Instrument's data exporting capabilities. As shown below, the first name is called TabName_01 for the first sample condition (A). I've written the formula so that when the template and/or subsequent sample conditions are "blank", the default tab name is just the Sheet number (1-25). Each sheet is assigned the appropriate "TabName_nn". When the value is changed as shown in the second illustration (Sheet1), the TabName_01 correctly updates the A1 cell to the lookup value but the tab name did not change despite the updated VBA Code for the Worksheet_Calculate ( ).

Am I missing something in applying your suggestion to use this feature?

 

[JackDanIce]

Hi Mickiz I tried to mimic your set up as best I could, using info from this link: Dynamic Worksheet Tab Names (Microsoft Excel)

Setup
In sheet1, I named a cell TabName1 with formula "="Sheet"&Row()+20"
In sheet2, I entered "=TabName1" into A1 and this code into the worksheet object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    
    On Error GoTo Badname
    ActiveSheet.Name = Left$(Target.Value, 31)
    On Error GoTo 0
    Exit Sub
    
Badname:
    MsgBox "Invalid Sheet name", vbExclamation + vbOKCancel, "Invalid Sheet Name"
    
End Sub

It works whenever I select a cell on the sheet and triggers the rename.

 

[MickiZ]

Hey ... thanks for taking the time to help me find a solution (much appreciated).

I've updated my workbook with the VBA code accordingly and it works!

 

[JackDanIce]

You’re welcome, glad it’s resolved