In the Airbus A320s I have piloted, no you cannot turn the no smoking signs off even if you put the switch to off position. I am pretty sure, back in the day when smoking was allowed you could do it and there maybe some older MSNs that allows the pilots to switch off the no smoking signs. In later models it is always on. I am not sure what exactly is a later model. I have flown A320s built as early as 2005 and the no smoking cannot be turned off in them.
The reason why there is an off position in the no smoking switch is because in Airbus aircraft it performs some other function as well and that is, it controls the aircraft emergency EXIT lights. You can see these lights in the aircraft ceiling and main entrance and emergency exit doors. From an operational point of view, during cockpit preparation we put the no smoking switch to auto position. When in auto position, with landing gear extended the emergency exit lights come on. Once the landing gear is retracted after take off, the exit lights go off. Anyone who has flown an Airbus would have most probably heard a 'bing' right after take off. This is the chime that comes on when the exit lights are turned off. When you are coming in for landing and when the landing gear is extended again the exit lights are turned on and the distinct 'bing' is heard.
An A320 EXIT light.
Once the flight is done with we do an aircraft securing checklist and this involves turning off the no smoking switch. One might wonder why we do that even though it does not turn off the no smoking signs. The thing is, if the switch is kept in auto or on, the exit lights remains on as long as the aircraft is powered. This prevents the internal emergency light battery packs from charging, because with the lights on, the charging is automatically inhibited. The last thing you want is a drained battery pack in an emergency evacuation. Now, that would not be fun, would it? Panicking passengers in a totally dark aircraft!