Bom dia!!

Na propriendade Text do TextBox, coloque o text desejado, depois use o código abaixo.

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Me.TextBox1.Value = ""
End Sub

Att

Obrigado, Alexandre.

Aprimorei e ficou dessa maneira:

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If Me.TextBox1.Value = "Digite seu nome aqui" Then
Me.TextBox1.Value = ""
End If
End Sub
Private Sub TextBox1_AfterUpdate()
If Me.TextBox1.Value = "" Then
Me.TextBox1.Value = "Digite seu nome aqui"
End If
End Sub

Assim, caso a pessoa saia da TextBox deixando-a em branco, o valor volta a ser exibido.