Macro que al hacer clic en agregar una hoja nueva me aparezca un mensaje que diga estas seguro que deseas agregar una nueva hoja

No soy Dante, pero igual y te puedo ayudar.

Tal vez este código te pueda ayudar!

Sub Agrega_Hoja()
    respuesta = MsgBox("Está seguro que desea agregar una hoja más?", vbInformation + vbYesNo, "Confirma!")
    If respuesta = vbYes Then
        Sheets.Add
        MsgBox "Se ha agregado una hoja más!" & vbCrLf & _
        "Actualmente existen en este libro " & Sheets.Count & " hojas"
    End If
End Sub

Saludos y espero sea de utilidad

Suscríbete a mi canal: http://bit.ly/1KgurfF

Sigueme en Twitter https://twitter.com/RaduNordenhulk

Agregame en Facebook: http://on.fb.me/1oFUXVL

Sigue mis publicaciones en mi blog: http://radunordenhulk.blogspot.com/

En vba no es necesario declarar las variables.

Si tienes puesto Option Explicit, puedes quitar esa línea.

O bien, declara la variable así:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'Por.Dante Amor
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim nueva As String
    Dim res As String
    nueva = Sh.Name
    res = MsgBox("Estas seguro que deseas agregar una nueva hoja", vbQuestion & vbYesNo)
    If res = vbNo Then
        Sheets(nueva).Delete
    End If
End Sub

Más sobre variables:

https://msdn.microsoft.com/es-MX/library/47zceaw7.aspx 

Comentarios sobre la declaración de variables.

1. En vba de excel no es necesario declarar variables, todas se declaran en automático como variant.

https://msdn.microsoft.com/en-us/library/office/gg251448.aspx 

2. Si se declaran las variables deberá realizarse de forma correcta, de lo contrario pueden ocurrir errores.

3. La declaración de las variables se utiliza cuando el programa es muy extenso y se requiere el control de las variables, de esa forma evitarías utilizar variables forma errónea. En este caso la macro es muy pequeña, no es necesario.

https://msdn.microsoft.com/es-mx/library/y9341s4f.aspx 

4. Y la razón más importante para el uso de variables, es para optimizar el uso de la memoria, ya que si no se declaran las variables, cada variable se declara en automático como variant y esto ocupa más cantidad de memoria.

http://aprenderaprogramar.com/index.php?option=com_content&view=article&id=140:tipos-de-variables-en-visual-basic-integer-single-double-string-object-boolean-etc-ejemplos-cu00308a&catid=37:curso-aprender-a-programar-visual-basic-desde-cero&Itemid=61 

':)
':)

Te dejo este código:

Sub Confirmar()

Dim Resp As Byte

Resp = MsgBox("Deseas agregar una hoja?", _

    vbQuestion + vbYesNo, "EXCELeINFO")

If Resp = vbYes Then

    MsgBox "Se eligió continuar...", vbExclamation, "EXCELeINFO"

Sheets.Add

    'Aquí se ejecutaría el código

Else

    MsgBox "Se eligió cancelar...", vbCritical, "EXCELeINFO"

End If

End Sub