Seems like this issue with the "Dome/Map/Ceiling Light not working when the door is opened... but works when you press the lens button to turn it on/off with the door closed" is a common problem.
Today, I fixed mine in a 2006 Honda Accord EX V6 Automatic (226,000 miles) with the aid of this you tube video,
Remove both lenses.
Remove both screws/bolts.
Use a pry tool in the space toward the front of the car to loosen and lower the base unit. Once it's loose, I slid the unit to the right to give space for the switch to slide out. There was no need to unplug the base unit (as shown in the video).
The switch will slide left (for the driver's side) or right (for the passenger's side). There was enough space to slide the switch out with the use of a pry tool. It is held in place by 3 prongs.
In Summary, You do not need to change out the entire base unit (MSRP $55.98), the issue appears to be in the switch located in the base unit (Dorman Part #924-798). Mine came in a kit of 2 switches. I bought them on ebay for under $14.00 (free shipping).