Anyone going bald :D

Lets take a step back for a moment and concentrate on the Excel Form.........

FrmLogin (let say)

From all the info you have given we only need 3-4 things on this form
1) txtUsername = TextBox
2) txtPassword = Textbox
3) cmdLogin = CommandButton
4) cmdCancel = CommandButton


NEXT......

Now your trying to do lookups and use ranges which you need data on a worksheet to do (looking at your first code snip)..........so your trying to dump the data from your username database(access) into a sheet first and then do a lookup from the sheet is that right?
(not ideal in a real world scenario i must say haha)

Really this part needs to be clarified, what is your goal.......Load the database into a table.....before or after the OK button is clicked......or are you trying to send the username/password to the database and get an OK/NOTOK response...........

these answers lead to COMPLETELY different Things that need to be done for all this to work......so let us know