Someone on the Drupal Dev list asked what to do when his custom module's CSS wasn't showing up. This is the quick checklist I sent in reply:

In order:

0. Make extra-sure your module's enabled on admin/build/modules

1. Check your drupal_add_css() call. Should look roughly like:
drupal_add_css(drupal_get_path('module', 'MYMODULE') .'/css/MYMODULE.css');

2. Make sure you're doing drupal_add_css() inside hook_init()