Proteger macros con contraseña VBA

Existen varias manera de hacerlo. Te comento una que utilizo bastante a menudo.

En el ThisWorkbook ponemos lo siguiente:

Private Sub Workbook_Open()
Application.Visible = False
Ingreso.Show
End Sub

En el botón aceptar del UserForm del ingreso debes poner:

Private Sub CommandButton2_Click()
Dim Usuario ' Variable que alojará el usuario que eligió el operador
Dim Contrasenia 'Variable que alojará el valor de la contraseña del usuario que está registrada en la base
Dim DatoEncontrado 'Variable que alojará la dirección (Celda) que contiene el valor de Contraseña registrado en la Base
'Pasar el valor del usuario elegido por el operador a la variable Usuario
Usuario = Me.txtUsuario
'usuarioexistente indica la cantidad de Usuarios que coinciden con la condición
usuarioexistente = Application.WorksheetFunction.CountIf(Range("D51:D60"), Me.txtUsuario.Value)
Set Rango = Range("D51:D60")
'Si el Usuario o la contraseña (cualquiera de los dos) es vacío
'solicita al operador que incluya un valor y posiciona el cursor en el cuadro de usuario (ComboBox)
If Me.txtUsuario.Value = "" Or Me.txtPassword.Value = "" Then
MsgBox "Por favor introduce usuario y contraseña", vbExclamation, blog
Me.txtUsuario.SetFocus
ElseIf usuarioexistente = 0 Then
'Si es cero quiere decir que el usuario no existe en la base (Contó cero usuarios con ese nombre)
'Entonces informa al operador acerca de esa situación
MsgBox "El usuario '" & Me.txtUsuario & "' no existe", vbExclamation, blog
ElseIf usuarioexistente = 1 Then
'Si es igual a uno quiere decir que encontró uno y entonces lo busca
DatoEncontrado = Rango.Find(What:=Me.txtUsuario.Value, MatchCase:=True).Address
'Carga la variable Contraseña con el valor alojado en la base como Contraseña
'Para unificar tipos de datos le antepone un apostrofe de manera que quede de tipo String
'No se utiliza la función str() porque en el caso de que la contraseña sea un String esa función no puede ser utilizada.
Contrasenia = "'" & Range(DatoEncontrado).Offset(0, 1).Value
If Range(DatoEncontrado).Value = Me.txtUsuario.Value And Contrasenia = "'" & Me.txtPassword.Value Then
'Si Usuario Elegido es igual a Usuario de la Base Y Contraseña ingresada es igual a la contraña de la base
'la validación es correcta.
Range("F2").Value = "Usuario: " & Range(DatoEncontrado).Offset(0, -1).Value
MsgBox "Bienvenido usuario: " & Usuario, vbCritical + vbOKOnly, "Validación Correcta" & blog
'Al ser correcta la validación muestra la aplicación y descarga el formulario Ingreso
Application.Visible = True
Unload Me
Else
'Si la validación es incorrecta informa al operador y sale de la rutina
MsgBox "La contraseña es inválida", vbExclamation, blog
Exit Sub
End If
End If
End Sub

La imagen del login que yo utilizo la podes encontrar en:

http://grupobioarg.blogspot.com.ar/2012/07/principales-pantallas-de-la-aplicacion.html

Los rangos utilizados son:

ID = B1:B10
NOMBRE = C1:C10
USUARIO = D1:D10
CONTRASEÑA = E1:E10

Como veras es un tema bastante extenso pero es sumamente útil y lograble.