Subscript out of range error when using nested if statement

Hi,

I have a time tracker program which works this way. I have a Userform which has 2 textboxes. User enter his employee id on textbox1 then enters a valid break time code which are 1,2 and 3 on textbox2. If User typed 1 then first breaktime start time will be pasted on the worksheet. When User returns after break he then enters his employee id again and then type 1 to end his first breaktime, the end time will be pasted on the worksheet. My program works fine however, I am trying to place a code which will prompt the user to end his first break time before entering another valid break code. Kindly check my code below as I am receiving an error message. 'Run-time error 9' Subscript out of range. This thing is driving me nuts. Any advise or can you tweak my coding vba experts? Thanks

Please Login or Register  to view this content.

Regards,

Brad

Hello Bradshaw,

Where do you declare the variable CurRow and where do you assign the value?

Sincerely,
Leith Ross

Hi Leith,

Thanks for the quick response. CurRow is declared as:

Please Login or Register  to view this content.

Regards,

Brad

Hello Brad,

I am guessing that CurRow is zero (has no value assigned) and that is why you are getting the "Subscript out of range" error. There is no row numbered zero. Be sure a row number has been assigned to CurRow before calling the macro.

Sincerely,
Leith Ross

Hi Leith,

To give you an idea on my problem. I have attached a sample workbook. Thanks

Regards,

Brad

Hello Bradshaw,

Part of the problem was you were using the TextBox_Change() event. I changed this to TextBox_AfterUpdate() You don't want the code to run every time a character is entered, only after the user has finished the entry. I am still looking at the message prompt portion. By the way, CurRow is initialized by TextBox1.

Sincerely,
Leith Ross

Hi Leith,

Thanks for responding. I tried using the TextBox_AfterUpdate() event but the cursor don't automatically move on the next box and it doesn't even displays the employee data.

Regards,

Brad

hi,

I have tried Leith's suggestion on changing textbox_change() event to Afterupdate event but what happens is that when the user enters employee id, it no longer automatically moves to the next textbox just like how the program works on my test file. Any other ideas excel gurus? I appreciate your assistance.

Regards,

Brad

Hello Brad,

I have been working on your code and made quite a few changes to get it working. Test this out and let me know how this works for you.

Sincerely,
Leith Ross

hi Leith,

Thank you for spending time in helping me with this. I made some testing on the testfile you created and these are my findings:

1. When user enters employee id, the cursor no longer moves to textbox2 unless you press the enter key. (Probably because of the AfterUpdate event, I think)

2. Once user press 1, then start time will be pasted on the worksheet which is correct. But the problem here now is, even the end time appears right after the start time is pasted on the worksheet in which it should not be. User needs to re enter his employee id and the same break code he press when he started his break to be able to paste the end time on the worksheet. Like if user pressed 1 for start time, user needs to press 1 again to log the end time. If the user accidentally press 2 without pressing 1 (which was the break code when he logs the start time) then an error or prompt should appear stating that he needs to log the end time first by pressing 1 before entering other break codes.

I'm sorry if I have given you a hard time on this. This was also driving me nuts when making the code. Any other advice how to accomplish this? Thanks again.

Regards,

Brad

Hello Brad,

Your are correct about the cursor not moving to TextBox2 because of the AfterUpdate event.

The code moves to TextBox2 on my computer and does not enter the start time in the end time cell. I am running Excel 2003 on Windows XP. What is your setup?

Sincerely,
Leith Ross

hi Leith,

The start time is being pasted on the start time cell same goes with the end time being pasted on the end time cell. The time are being pasted on their respective corresponding cells. The only problem is that, when user enters 1 then start time is being log and at the same time, the end time prompt also appears, even the error prompt when textbox1 is empty also appears simultenously which should not be the case.

When user enters breakcode 1 for the first time, the start time logs on the sheet. When user enters breakcode 1 for the second time, the end time logs on the sheet. However, if user accidentaly enters breakcode 2, an error prompt should appear stating that user needs to enter 1 to log the end time of breakcode 1 before entering another break code.

I am using excel 2003 on Windows 2000.

Thank you for keeping with me on this.

Regards,

Brad

Hello Brad,

There shouldn't be any conflicts with this Excel 2003 code running on Windows 2000, but I'll check it out because I am not experiencing those problems running the workbook on my current configuration.

Sincerely,
Leith Ross

Hi Leith,

You're right. I myself test it several times just to make sure that it works as it shlould be but unfotunately not. Thanks again for helping me out.

Regards,

Brad

Hi,

Anyone have ideas regarding my inquiry? Im not sure if I can use a select case inside an if and else function. Still I cannot make this thing work. Still on the same inquiry and error.

Regards,

Brad

Hi,

anyone can help me tweak my code, please? Info regarding this is found at the first part of my post.

Please Login or Register  to view this content.