Check if cellvalue is equal to any tab/worksheet name in Workbook

[Golaidron]

Hi Guru's and Master's in Excel and VBA

In my workbook I created new tabs in based on the cell value in D3 and a macro attached to a command button.

The question is:

1. Is it possible to get Excel with vba or formula to check if the value D3.value is equal to any of my existing tabs? (Hope to avoid duplicates of tab names when i add the next one.)
2. If the value in D3 corresponds with any of my existin tabs, can i get a warning? Messagebox or D3's backgroundcolor turns Red?
3. If I change the value again in D3, can I get Excel to check name vs tabs again?

Hope to hear from you soon

Best regards

Golaidron

 

[mumps]

Maybe you can adapt this code:

Sub test()
    If Evaluate("isref('" & Range("D3") & "'!A1)") Then
        MsgBox ("Sheet exists.")
    Else
        MsgBox ("Sheet does not exist.")
    End If
End Sub

 

[Golaidron]

Hi mumps.
That macro work great without any changes, thanks alot.

If I like this macro to run automatic when i change the value in D3, is that possible?

Best regards

Golaidron.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change the value in D3 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "D3" Then Exit Sub
    If Evaluate("isref('" & Target & "'!A1)") Then
        MsgBox ("Sheet exists.")
    Else
        MsgBox ("Sheet does not exist.")
    End If
End Sub

 

[Golaidron]

Omg. You are awsome.
Thanks alot.
Worked perfect.

 

[mumps]

You are very welcome.