Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
Hello All,
{Excel 2007, PC, novice} I have a userform which contains a ComboBox showing the bound column data. It supplies two Text boxes with data from the two columns not shown. ----'Text Box Values will reflect Credit Card Choice Private Sub cmb_Purch_CreditCard_Change() txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) End Sub ====================================== The ComboBox Enabled property is set to False ,and the row source is set to a range on a lookup sheet - only to be activated when the user chooses the Credit Card method of payment. This allows Credit Card related info to be visible and data to be entered into other text boxes. ----'Activate Credit Card ComboBox Private Sub cmb_PaymentMethod_Change() If cmb_PaymentMethod.Value = "CC" Then cmb_Purch_CreditCard.Enabled = True cmb_Purch_CreditCard.BackColor = &H80000005 Else: cmb_Purch_CreditCard.Enabled = False cmb_Purch_CreditCard.BackColor = &H8000000B End If End Sub =================================== Adding the data to the spreadsheet works fine (relevant code shown):. ----'Payment Methods ..Cells(lRow, 24).Value = Me.cmb_PaymentMethod.Value ..Cells(lRow, 25).Value = Me.cmb_Purch_CreditCard.Value ..Cells(lRow, 26).Value = Me.txt_Purch_SecCode.Value ..Cells(lRow, 27).Value = Me.txt_Purch_CCExpire.Value ================================================ When I run a Command button to ‘Clear Form’ but not close the form, I receive a ‘Runtime error 381’ Could not get the column property. Invalid property array.’ The button and the code are located on/in the user form, not in a module Rather than flowing through this code in the Sub for ‘Clear Form’ (relevant code shown): ---'Clear Payment Methods cmb_PaymentMethod.Value = "" cmb_Purch_CreditCard.Value = "" txt_Purch_CCExpire.Value = "" txt_Discounts.Value = "" txt_Notes.Value = "" The code jumps out and goes to the second line of the other Sub routine: ---'Text Box Values will reflect Credit Card Choice Private Sub cmb_Purch_CreditCard_Change() txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) End Sub Does this need to be in the module for intializing the form? I'm learning this as I go along, but I’m at a loss trying to figure this one out. Any ideas? Thanks! Mark Mace |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
Hi Mark,
Not certain this is the answer your second question but I think that in making changes to the object with code then that change is recognized and the change event code triggers. Try inserting Application.EnableEvents = False just before clearing the values and then after that code has run insert Application.EnableEvents = True. Now a warning when using this code. If your code fails due to error before turning events back on then the events remain off until you restart Excel or use code to turn events back on. (This can occur during testing or other time due to unforseen code problems). Set up a sub as follows and you can just run it from the VBA editor if required. Sub Re_EnableEvents() Application.EnableEvents = True End Sub If you use error routines then you should always call this sub in the error routine. -- Regards, OssieMac " wrote: Hello All, {Excel 2007, PC, novice} I have a userform which contains a ComboBox showing the bound column data. It supplies two Text boxes with data from the two columns not shown. ----'Text Box Values will reflect Credit Card Choice Private Sub cmb_Purch_CreditCard_Change() txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) End Sub ====================================== The ComboBox Enabled property is set to False ,and the row source is set to a range on a lookup sheet - only to be activated when the user chooses the Credit Card method of payment. This allows Credit Card related info to be visible and data to be entered into other text boxes. ----'Activate Credit Card ComboBox Private Sub cmb_PaymentMethod_Change() If cmb_PaymentMethod.Value = "CC" Then cmb_Purch_CreditCard.Enabled = True cmb_Purch_CreditCard.BackColor = &H80000005 Else: cmb_Purch_CreditCard.Enabled = False cmb_Purch_CreditCard.BackColor = &H8000000B End If End Sub =================================== Adding the data to the spreadsheet works fine (relevant code shown):. ----'Payment Methods ..Cells(lRow, 24).Value = Me.cmb_PaymentMethod.Value ..Cells(lRow, 25).Value = Me.cmb_Purch_CreditCard.Value ..Cells(lRow, 26).Value = Me.txt_Purch_SecCode.Value ..Cells(lRow, 27).Value = Me.txt_Purch_CCExpire.Value ================================================ When I run a Command button to €˜Clear Form but not close the form, I receive a €˜Runtime error 381 Could not get the column property. Invalid property array. The button and the code are located on/in the user form, not in a module Rather than flowing through this code in the Sub for €˜Clear Form (relevant code shown): ---'Clear Payment Methods cmb_PaymentMethod.Value = "" cmb_Purch_CreditCard.Value = "" txt_Purch_CCExpire.Value = "" txt_Discounts.Value = "" txt_Notes.Value = "" The code jumps out and goes to the second line of the other Sub routine: ---'Text Box Values will reflect Credit Card Choice Private Sub cmb_Purch_CreditCard_Change() txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) End Sub Does this need to be in the module for intializing the form? I'm learning this as I go along, but Im at a loss trying to figure this one out. Any ideas? Thanks! Mark Mace |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
Thanks very much Ossie! I'll give it a try and get back to you. Mark On Apr 27, 11:49*pm, OssieMac wrote: Hi Mark, Not certain this is the answer your second question but I think that in making changes to the object with code then that change is recognized and the change event code triggers. Try inserting Application.EnableEvents = False just before clearing the values and then after that code has run insert Application.EnableEvents = True. Now a warning when using this code. If your code fails due to error before turning events back on then the events remain off until you restart Excel or use code to turn events back on. (This can occur during testing or other time due to unforseen code problems). Set up a sub as follows and you can just run it from the VBA editor if required. Sub Re_EnableEvents() * * * Application.EnableEvents = True End Sub If you use error routines then you should always call this sub in the error routine. -- Regards, OssieMac " wrote: Hello All, {Excel *2007, PC, novice} I have a userform which contains a ComboBox *showing *the bound column data. *It supplies two Text boxes *with data from the two columns not shown. ----'Text Box Values will reflect Credit Card Choice *Private Sub cmb_Purch_CreditCard_Change() * * txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) * *txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) * End Sub ====================================== The ComboBox *Enabled property is set to False ,and the row source is set to a range on a lookup sheet - only to be activated when the user chooses the Credit Card method of payment. This allows Credit Card related info to be visible and data to be entered into other text boxes. ----'Activate Credit Card ComboBox Private Sub cmb_PaymentMethod_Change() *If cmb_PaymentMethod.Value = "CC" Then * * * * * *cmb_Purch_CreditCard.Enabled = True * * * * * * cmb_Purch_CreditCard.BackColor = &H80000005 * *Else: cmb_Purch_CreditCard.Enabled = False * * cmb_Purch_CreditCard.BackColor = &H8000000B End If End Sub =================================== Adding the data to the spreadsheet *works fine (relevant code shown):.. ----'Payment Methods ..Cells(lRow, 24).Value = Me.cmb_PaymentMethod.Value ..Cells(lRow, 25).Value = Me.cmb_Purch_CreditCard.Value ..Cells(lRow, 26).Value = Me.txt_Purch_SecCode.Value ..Cells(lRow, 27).Value = Me.txt_Purch_CCExpire.Value ================================================ When I run a Command button to ‘Clear Form’ but not close the form, *I receive a ‘Runtime error 381’ *Could not get the column property. Invalid property array.’ *The button and the code are located on/in the user form, not in a module Rather than flowing through this code in the Sub for ‘Clear Form’ (relevant code shown): ---'Clear Payment Methods * * * * cmb_PaymentMethod.Value = "" * * * * cmb_Purch_CreditCard.Value = "" * * * * txt_Purch_CCExpire.Value = "" * * * * txt_Discounts.Value = "" * * * * txt_Notes.Value = "" The code jumps out and goes to the second line of the other Sub routine: ---'Text Box Values will reflect Credit Card Choice *Private Sub cmb_Purch_CreditCard_Change() * * txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) * * txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) * End Sub Does this need to be in the module for *intializing the form? I'm learning this as I go along, but I’m at a loss trying to figure this one out. Any ideas? Thanks! Mark Mace |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
Hi Ossie,
I tried the suggestion you had made and the code still locked up at the same point: ---'Text Box Values will reflect Credit Card Choice Private Sub cmb_Purch_CreditCard_Change() txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) 'Right here it stopped' txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) End Sub I tried various locations w/in the 'Clear Form' sub but to no avail. If the problem has to do with an invalid array, how would I overcome this? I checked Chuck Pearson's site and several others regarding this, but it's a bit over my head. I also checked out 'Column Property', since this is the main response. I checked other posts and they seemed to have the same problems. When I tried to adapt or to literally implement their code solutions, it was a no go for me. Is something like this more appropriate? with Me.cmb_Purch_CreditCard txt_Purch_SecCode =.Column(1, ListIndex) txt_Purch_CodeLoc =.Column(2, Listindex) End with Thanks again for your help. Mark On Apr 28, 12:09*pm, " wrote: Thanks very much Ossie! I'll give it a try and get back to you. Mark On Apr 27, 11:49*pm, OssieMac wrote: Hi Mark, Not certain this is the answer your second question but I think that in making changes to the object with code then that change is recognized and the change event code triggers. Try inserting Application.EnableEvents = False just before clearing the values and then after that code has run insert Application.EnableEvents = True. Now a warning when using this code. If your code fails due toerrorbefore turning events back on then the events remain off until you restart Excel or use code to turn events back on. (This can occur during testing or other time due to unforseen code problems). Set up a sub as follows and you can just run it from the VBA editor if required. Sub Re_EnableEvents() * * * Application.EnableEvents = True End Sub If you useerrorroutines then you should always call this sub in theerror routine. -- Regards, OssieMac " wrote: Hello All, {Excel *2007, PC, novice} I have a userform which contains a ComboBox *showing *the bound column data. *It supplies two Text boxes *with data from the two columns not shown. ----'Text Box Values will reflect Credit Card Choice *Private Sub cmb_Purch_CreditCard_Change() * * txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) * *txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) * End Sub ====================================== The ComboBox *Enabled property is set to False ,and the row source is set to a range on a lookup sheet - only to be activated when the user chooses the Credit Card method of payment. This allows Credit Card related info to be visible and data to be entered into other text boxes. ----'Activate Credit Card ComboBox Private Sub cmb_PaymentMethod_Change() *If cmb_PaymentMethod.Value = "CC" Then * * * * * *cmb_Purch_CreditCard.Enabled = True * * * * * * cmb_Purch_CreditCard.BackColor = &H80000005 * *Else: cmb_Purch_CreditCard.Enabled = False * * cmb_Purch_CreditCard.BackColor = &H8000000B End If End Sub =================================== Adding the data to the spreadsheet *works fine (relevant code shown):. ----'Payment Methods ..Cells(lRow, 24).Value = Me.cmb_PaymentMethod.Value ..Cells(lRow, 25).Value = Me.cmb_Purch_CreditCard.Value ..Cells(lRow, 26).Value = Me.txt_Purch_SecCode.Value ..Cells(lRow, 27).Value = Me.txt_Purch_CCExpire.Value ================================================ When I run a Command button to ‘Clear Form’ but not close the form, *I receive a ‘Runtimeerror381’ *Could not get the column property. Invalid property array.’ *The button and the code are located on/in the user form, not in a module Rather than flowing through this code in the Sub for ‘Clear Form’ (relevant code shown): ---'Clear Payment Methods * * * * cmb_PaymentMethod.Value = "" * * * * cmb_Purch_CreditCard.Value = "" * * * * txt_Purch_CCExpire.Value = "" * * * * txt_Discounts.Value = "" * * * * txt_Notes.Value = "" The code jumps out and goes to the second line of the other Sub routine: ---'Text Box Values will reflect Credit Card Choice *Private Sub cmb_Purch_CreditCard_Change() * * txt_Purch_SecCode = Me.cmb_Purch_CreditCard.Column(1) * * txt_Purch_CodeLoc = Me.cmb_Purch_CreditCard.Column(2) * End Sub Does this need to be in the module for *intializing the form? I'm learning this as I go along, but I’m at a loss trying to figure this one out. Any ideas? Thanks! Mark Mace- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
Sorry Mark,
Without the workbook to test I can't offer any further ideas. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 381 - Invalid Property Array
No worries Ossie,
I looked at the code where it was locking up and realized that perhaps the Sub: 'Private Sub cmb_Purch_CreditCard_Change()' was the problem. I took a wild guess thinking that when I cleared the form, it activated the 'Change Event' procedure, thereby causing the code to jump out and lock up. I replaced the 'Change' event to a 'Click' event, and it worked w/o a hitch. Thanks for clueing me in on the 'Enable Events' code because I now have another piece of the puzzle to make sense of all of this. Looking forward to finding many more of the pieces. Many Thanks! Mark On Apr 30, 1:14*am, OssieMac wrote: Sorry Mark, Without the workbook to test I can't offer any further ideas. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 380 - Could not set the list property. Invalid property value. | Excel Programming | |||
Runtime Error 380 – Could not set the list property. Invalid property value | Excel Programming | |||
Runtime error 380: Could not set the List property. invalid property value of listbox | Excel Programming | |||
Runtime Error 381 Couldn't set list property, invalid array index | Excel Programming | |||
Runtime Error 381 Couldn't set list property, invalid array index | Excel Programming |