Intermittent Run-time Error 9

vbones

New Member
Joined
Aug 31, 2010
Messages
4
I'm still new and learning VBA, so please be gentle. I have (attempted) to write a code so that a user can input information on a tab, hit submit, it will transfer the information to the 2nd tab and clear the info from the first tab. Also, the 2nd tab is locked so that the information cannot be altered (except by the person with the password). The spreadsheet was created in Excel 2003. This works fine on my computer, but some other people are getting the "Run-time Error '9': Subscript out of range" error when they try to hit the submit button, but it doesn't always happen. They are using Excel 2007. I don't understand why this is happening. Also, I'm sure my attempt at code could probably be cleaned up a little. Any suggestions?

Thanks!

Here is the code being used: (and if you have suggestions to make it better, I would love to learn!)

Private Sub CommandButton1_Click()
Worksheets("sheet2").Unprotect Password:="help"
nextrow = Worksheets("sheet2").Range("A65536").End(xlUp).Row + 1
Worksheets("sheet2").Cells(nextrow, 1).Resize(1, 16).Value = Array(Worksheets("log").Range("a2"), Worksheets("log").Range("b2").Value, Worksheets("log").Range("c2").Value, Worksheets("log").Range("d2").Value, Worksheets("log").Range("e2").Value, Worksheets("log").Range("f2").Value, Worksheets("log").Range("g2").Value, Worksheets("log").Range("h2").Value, Worksheets("log").Range("i2").Value, Worksheets("log").Range("j2").Value, Worksheets("log").Range("k2").Value, Worksheets("log").Range("l2").Value, Worksheets("log").Range("m2").Value, Worksheets("log").Range("n2").Value, Worksheets("log").Range("o2").Value, Worksheets("log").Range("p2").Value)
MsgBox "Has been submitted"

Worksheets("log").Range("a2:p2").ClearContents
Worksheets("sheet2").Protect Password:="help"
End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
When the error occurs, click Debug. Which line of code is highlighted?
 
Upvote 0
Hi vbones
Welcome to the board

A simpler version of your code:


Code:
Private Sub CommandButton1_Click()

Worksheets("sheet2").Unprotect Password:="help"
nextrow = Worksheets("sheet2").Columns("A").End(xlUp).Row + 1
 
Worksheets("sheet2").Cells(nextrow, 1).Resize(1, 16).Value = _
    Worksheets("log").Range("a2:p2").Value
 
MsgBox "Has been submitted"
Worksheets("log").Range("a2:p2").ClearContents
Worksheets("sheet2").Protect Password:="help"
End Sub
 
Upvote 0
Unfortunately, I haven't been able to get anyone to click the debug to highlight the error. When I got one of the users on the phone to do that, it worked for her with no problem! I can't do it because no matter what I do, it always seems to work for me. I'm going to try again tomorrow.
 
Upvote 0
Thank you for the simplier version of the code! I knew there had to be a way, I just don't know enough yet to be able figure some of it out.
 
Upvote 0
I figured out what the problem was. One of the users had changed the name of the tabs without telling me which, of course, threw everything off! Now that the tabs are named the same as what the code says they are named, everything seems to be working just fine! Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top