USERFORM runtime error 9: subscript out of range
Hello
I am new to Experts Exchange, I came across it when searching the internet for help with VBA UserForm coding. I landed on https://www.experts-exchange.com/questions/28706684/VBA-Code-to-Populate-Userform-with-Existing-Records-Edit-them-and-add-them-back-to-the-worksheet.html which not only answered my search but helped me to complete my UserForm. Basically, the form on that thread was exactly what I was doing. So I downloaded the solution and set about completing my form, copying and editing the code.
All was going well considering I haven’t used VBA for years (10+) and my coding skills are a bit rusty. My problem is when I test my form I get “runtime error 9: subscript out of range” for the SEARCH, ADD and UPDATE buttons. Set ws = Sheets("Sheet4") seems to be causing the problem.
I’ve also noticed that if I start the form from any other sheet than the sheet with the data in it the form is basically empty. Since I have set the Workbook to open on the USER GUIDE sheet, as this has a button to open the form, this causes a problem.
Well as I say I’m a bit rusty and I’ve been at this for days, reading and re-reading the code, search after search online as well as VBA Help, and now I think I have gone code blind (can’t see the wood for the trees) and my rusty skills are making it hard to find what’s wrong. If someone could take a look at the attached file and point me in the right direction I would be most grateful.
The above thread was solved by @Subodh_Tiwari_(Neeraj) and @Roy_Cox – if either of them are about perhaps they could take a look and advise.
Many Thanks
SME-Datafile-sample-v2.xlsm
I am new to Experts Exchange, I came across it when searching the internet for help with VBA UserForm coding. I landed on https://www.experts-exchange.com/questions/28706684/VBA-Code-to-Populate-Userform-with-Existing-Records-Edit-them-and-add-them-back-to-the-worksheet.html which not only answered my search but helped me to complete my UserForm. Basically, the form on that thread was exactly what I was doing. So I downloaded the solution and set about completing my form, copying and editing the code.
All was going well considering I haven’t used VBA for years (10+) and my coding skills are a bit rusty. My problem is when I test my form I get “runtime error 9: subscript out of range” for the SEARCH, ADD and UPDATE buttons. Set ws = Sheets("Sheet4") seems to be causing the problem.
I’ve also noticed that if I start the form from any other sheet than the sheet with the data in it the form is basically empty. Since I have set the Workbook to open on the USER GUIDE sheet, as this has a button to open the form, this causes a problem.
Well as I say I’m a bit rusty and I’ve been at this for days, reading and re-reading the code, search after search online as well as VBA Help, and now I think I have gone code blind (can’t see the wood for the trees) and my rusty skills are making it hard to find what’s wrong. If someone could take a look at the attached file and point me in the right direction I would be most grateful.
The above thread was solved by @Subodh_Tiwari_(Neeraj) and @Roy_Cox – if either of them are about perhaps they could take a look and advise.
Many Thanks
SME-Datafile-sample-v2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'll take a look as soon as I can.Busy week so far.
ASKER
@Martin_Liss
I did think that was the problem, however I only tried it as a solution for one of the buttons and when it didn't work I convinced myself I was wrong. Now that I have followed your advice all buttons seem to be working except the Save button, which if I open the form using the button on the User Guide sheet it still adds the new record to the User Guide sheet.
It might be something to do with the initialize code which still refers to Sheet4 but I'm not sure how to fix it:
Private Sub UserForm_Initialize()
Dim lr As Long
Set rData = Sheet4.Range("A1").Current
currentrow = 2
txtURN.SetFocus
LoadBoxes
End Sub
@Norie
I don't have a sheet named Sheet4 however the Master List sheet, which has my data, is Sheet4. I had used Sheet4 in the code as I thought that would overcome any problems should anyone change the Master List sheet name in the future.
Thanks.
I did think that was the problem, however I only tried it as a solution for one of the buttons and when it didn't work I convinced myself I was wrong. Now that I have followed your advice all buttons seem to be working except the Save button, which if I open the form using the button on the User Guide sheet it still adds the new record to the User Guide sheet.
It might be something to do with the initialize code which still refers to Sheet4 but I'm not sure how to fix it:
Private Sub UserForm_Initialize()
Dim lr As Long
Set rData = Sheet4.Range("A1").Current
currentrow = 2
txtURN.SetFocus
LoadBoxes
End Sub
@Norie
I don't have a sheet named Sheet4 however the Master List sheet, which has my data, is Sheet4. I had used Sheet4 in the code as I thought that would overcome any problems should anyone change the Master List sheet name in the future.
Thanks.
If the forename of the sheet you want to use was Sheet4 you would just use Sheet4k not Sheets("Sheet4").
Which sheet are you referencing in the code for the Save button?
Which sheet are you referencing in the code for the Save button?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Martin_Liss
That did the trick - when I use the button to open the form from the User Guide the Add button saves the new data to the Master List. Many thanks.
However, now I've thoroughly tested the form - adding and updating data - I have a brand new problem. The locked pink fields in the form contain formulas in the Master List data sheet. I don't have time to code the formulas so I had copied them into blank rows - which does cause a problem when using the go to Last button but that can be lived with - thinking this wouldn't be a problem when using the form. Boy was I wrong. When I Add or Update the formulas are lost and any results produced in existing rows are converted to text.
Any suggestions how to overcome this, the formula results are critical for the user to see on the form and they won't be allowed access to the data sheet to insert any missing formulas.
Thanks.
That did the trick - when I use the button to open the form from the User Guide the Add button saves the new data to the Master List. Many thanks.
However, now I've thoroughly tested the form - adding and updating data - I have a brand new problem. The locked pink fields in the form contain formulas in the Master List data sheet. I don't have time to code the formulas so I had copied them into blank rows - which does cause a problem when using the go to Last button but that can be lived with - thinking this wouldn't be a problem when using the form. Boy was I wrong. When I Add or Update the formulas are lost and any results produced in existing rows are converted to text.
Any suggestions how to overcome this, the formula results are critical for the user to see on the form and they won't be allowed access to the data sheet to insert any missing formulas.
Thanks.
I’m not at home right now so I’ll take a look when I get home this evening, but s new problem should be a new question.
I'm sorry but I don't understand the problem at all. For example what does "The locked pink fields in the form contain formulas in the Master List data sheet" mean.
In any case if you'd like my help with this new problem then please close this question and open a new one to deal with it.
If you don't know how to close a question then please see this EE article.
In any case if you'd like my help with this new problem then please close this question and open a new one to deal with it.
If you don't know how to close a question then please see this EE article.
ASKER
Thanks for all your help Martin I will close this question and open a new one for the new issues.
ASKER
Very quick, clear-cut help provided.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Which sheet is the data for the userform on?
Whichever sheet that is if it's not the active sheet when you open the userform you will have problems, for example the empty form you mention.
To combat that problem you need to make sure all range/cell references in the userform code have appropriate worksheet references.
Without worksheet references VBA will assume you are referring to ranges/cells on the active sheet.