Using RANGE to find a spreadsheet record based on a value in a column

[vancouver_ron]

I've spent hours, cannot solve this so hopefully someone can help. I've tried to simplify the problem from the original xlsm file.

There is a single spreadsheet containing a list of clinician names. Column A is a RECORD NUMBER (unique integer) for a clinician, Column B is the DISCIPLINE of the clinician (WCC, CNE, etc.), Column C is the clinician's NAME, Column D is the DATE ADDED of the record and Column E is the STATUS (ACTIVE, INACTIVE) of the record.

The form to start this simple application is frmStart. Select one of the five disciplines and click "GO". This opens frmClinician and loads the listbox in the form with all the clinicians from the spreadsheet for the selected discipline.

The form allows you to enter new names into the spreadsheet or modify the status of clinicians already entered in the spreadsheet.

The process for entering new names works. No problem there.

The problem is when selecting an existing name in the listbox so I can modify the status of the selected clinician. The problematic code is in the lstClinicians_Click() subroutine. I can get the index number of the selected clinician from the listbox. I can get the record number of the selected clinician from the spreadsheet. I'm trying to use the record number in the RANGE method to get the row number of the selected clinician from the spreadsheet so I can update the status of that clinician in that row.

I have the line of code with the RANGE method commented out right now, but when you remove the comment and then try to select a clinician from the listbox, i get the error.

I have spent hours trying to get this to work. My give up. can you help, please?