Since you have set  the password  for each sheet  :
This clearly indicates that only an authorised person who knows the password
for the sheet should be able to save any changes made on it.Thus it will ask
for a password each time, when someone tries to save the changes made in that
sheet.

Whereas if you have set the password for VBA properties  of Excel Project:
This means that the person who knows the password could only view the VBA
code.