VBA Activate a sheet from another Workbook based on Cell Value

[decadence]

Hi I have a lookup workbook with 50+ sheets of data and I would like to activate a specific work sheet from that workbook if a cell in the active sheet containing a specific value matches the sheet name, Is this possible to do this?

 

[mumps]

Can the sheet that is active vary? What cell on the active sheet contains the specific value that matches the sheet name? Is that cell already populated with the specific value or do you enter it when needed to activate the desired sheet?

 

[decadence]

Hi mumps, Yes the active sheet will vary, the cells that contains the specific value can vary but are also unique to the active sheet and the lookup sheet names and yes the cell is already populated with the value required

 

[mumps]

Place this macro in the code module for ThisWorkbook not in a regular module. Go to any sheet and double click the cell that contains the specific value.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Sheets(ActiveCell.Value).Select
End Sub

 

[decadence]

Hi Mumps, This works perfectly. Can this be done between 2 workbooks i.e lookup workbook with 50+ sheets of data and a varying name workbook using the active sheet?

 

[mumps]

By "varying name" do you mean that you want to activate a sheet on any second workbook?

 

[decadence]

Yes I would. I am struggling to activate the lookup workbook sheet based upon part of a cell value from the Active Workbook Active sheet using an array, Also I am not sure how to change the cell value to a range in this instance, I would like to search the whole active sheet to find a word from the array even if there are duplicates of that word and activate the sheet from the workbook lookup. I am going to add more words to find from the array. Hope this makes sense

The code below will activate a sheet if the sheet name matches the cell value.

    Dim sh As Worksheet, shNam As String
    
    Arr = Array("Test", "*TESTED_*")
    If Not IsInArray(range("A1").Value, Arr) Then
    If IsEmpty(ActiveSheet.range("A1")) Then Exit Sub
        shNam = ActiveSheet.range("A1").Value
    If SheetExists(ActiveWorkbook.Name, shNam) Then
        Sheets(shNam).Activate
    Else
    End If
    End If
End Sub

Public Function IsInArray(stringToBeFound As String, Arr As Variant) As Boolean
    Dim i As Integer
    For i = LBound(Arr) To UBound(Arr)
        If Arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

Function SheetExists(wbName As String, shName As String) As Boolean
SheetExists = False
    With Workbooks(wbName)
        For Each sh In .Sheets
            If sh.Name = shName Then
                SheetExists = True
                Exit For
            End If
        Next sh
End With
End Function

 

[mumps]

I don't quite understand what you are trying to do. Do you want to search the active sheet in the active workbook for a word that is in an array and then if it is found, activate the sheet in the lookup workbook that has the same name as the found word? If so, what words that you want to search for are in the array? What is the name of the active workbook including the extension (xls,xlsx,xlsm)?

 

[decadence]

Hi mumps, What you are suggesting is exactly what I am trying to achieve. There will be multiple Active Workbooks/Sheets that will vary in name as there will be varying data which will require a lookup, The lookup Workbook is called "Data Lookup 2018.xlsx". The words in the Array are what I want to find in the active sheet so it can reference against the Lookup sheet name then activate that specific sheet. There will only be one unique word to find in the active sheet

 

[mumps]

The words in the Array are what I want to find in the active sheet

seems to contradict this:

There will only be one unique word to find in the active sheet

The first quote says that there are more than one word (in an array) to find in the active sheet and the second quote says that there is only one unique word to find in the active sheet. Could you please clarify in detail?