It's actually very simple.
If you want your password  to be something like: "CreativePassword"
then use:

ActiveWorkbook.Sheets("Current Database").Protect "CreativePassword"
ActiveWorkbook.Sheets("Current Database").UnProtect "CreativePassword"

Remember, that you should also password protect  your vba  module
so that users can't look at the module to determine the password!