Eu fiz uma pesquisa rápida sobre essa questão e descobri que uma opção, à qual Thales aludiu, seria passar a senha como um argumento para o seu VBA.
'Protect worksheet with a password
Sheets("Sheet1").Protect Password:="myPassword"
'Unprotect a worksheet with a password
Sheets("Sheet1").Unprotect Password:="myPassword"
A desvantagem disso é que sua senha ficará visível para qualquer pessoa que possa abrir seu código. Então você pode querer criar uma segunda senha para proteger seu código de macro se você seguir essa rota. Veja o link abaixo.
EDIT: Com base no seu comentário acima, pode nos ajudar a ver seu código, então podemos descobrir onde faz sentido manipular a senha.