Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
I have several combo boxes on a user form that I am using to populate cells
in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
Let me clarify.
I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
You can set the MatchEntry proprety to 1 - Match Entry Complete and then put
an empty value at the top of the list. With Me.ComboBox1 .AddItem "" .AddItem "AAA" .AddItem "ABC" .AddItem "BBB" .AddItem "BCD" .AddItem "BCCEE" End With I believe this will allow you to do what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Let me clarify. I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
Thanks, Chip.
I've been working with Access for so many years, I'm going to have to train myself to think "outside the box" with Excel. -- Email address is not valid. Please reply to newsgroup only. "Chip Pearson" wrote: You can set the MatchEntry proprety to 1 - Match Entry Complete and then put an empty value at the top of the list. With Me.ComboBox1 .AddItem "" .AddItem "AAA" .AddItem "ABC" .AddItem "BBB" .AddItem "BCD" .AddItem "BCCEE" End With I believe this will allow you to do what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Let me clarify. I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
Chip,
That worked for initial data entry, but if I want to delete an entry, and leave it blank, that technique does not work. Dale -- Email address is not valid. Please reply to newsgroup only. "Chip Pearson" wrote: You can set the MatchEntry proprety to 1 - Match Entry Complete and then put an empty value at the top of the list. With Me.ComboBox1 .AddItem "" .AddItem "AAA" .AddItem "ABC" .AddItem "BBB" .AddItem "BCD" .AddItem "BCCEE" End With I believe this will allow you to do what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Let me clarify. I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
Dale,
I'm not sure if I understand. I think I was mistaken on the Match Entry setting. It should be 1 - frmMatchEntryComplete. Also, you'll want to set AutoWordSelect to False and Style to 2 - frmStyleDropDownList. As long as you don't delete the empty string element of the list, it should work even if you delete an item from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Chip, That worked for initial data entry, but if I want to delete an entry, and leave it blank, that technique does not work. Dale -- Email address is not valid. Please reply to newsgroup only. "Chip Pearson" wrote: You can set the MatchEntry proprety to 1 - Match Entry Complete and then put an empty value at the top of the list. With Me.ComboBox1 .AddItem "" .AddItem "AAA" .AddItem "ABC" .AddItem "BBB" .AddItem "BCD" .AddItem "BCCEE" End With I believe this will allow you to do what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Let me clarify. I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Property Value error in UserForm combo box
Chip,
Appreciate your help, but I finally went with the custom validation function I previously mentioned. What I wanted was a way to skip one of the combo boxes, but also wanted to be able to delete from the box, if the user changed their mind and wants to leave that entry blank. I tried changing the style to a DropDownList but that was balking when my code tried to fill it in with an empty string. My current settings a MatchEntry: 1 fmMatchEntryComplete MatchRequired:False Style: 0 - frmStyleDropDownCombo Then, in the AfterUpdate event of each combo I have code that lets an empty string pass and checks to see if the entered value IsInList(Value, Range). If it fails both of these tests, it displays an error message and returns control to the combo box. Public Function IsInList(SomeValue As String, ListRange As String) Dim rng As Range Dim intMatch As Integer 'On Error GoTo ErrorHandler Set rng = Worksheets("Settings").Range(ListRange) On Error Resume Next intMatch = WorksheetFunction.Match(SomeValue, rng, 0) If Err.Number = 0 Then IsInList = True Else IsInList = False End If Set rng = Nothing End Function -- Email address is not valid. Please reply to newsgroup only. "Chip Pearson" wrote: Dale, I'm not sure if I understand. I think I was mistaken on the Match Entry setting. It should be 1 - frmMatchEntryComplete. Also, you'll want to set AutoWordSelect to False and Style to 2 - frmStyleDropDownList. As long as you don't delete the empty string element of the list, it should work even if you delete an item from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Chip, That worked for initial data entry, but if I want to delete an entry, and leave it blank, that technique does not work. Dale -- Email address is not valid. Please reply to newsgroup only. "Chip Pearson" wrote: You can set the MatchEntry proprety to 1 - Match Entry Complete and then put an empty value at the top of the list. With Me.ComboBox1 .AddItem "" .AddItem "AAA" .AddItem "ABC" .AddItem "BBB" .AddItem "BCD" .AddItem "BCCEE" End With I believe this will allow you to do what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Dale Fye" wrote in message ... Let me clarify. I found the MatchRequired and Style properties, and I really want to keep the MatchRequired set to true. Is there a way to allow a blank entry in a field and still keep MatchRequired as true? I know I can change MatchRequired to false, and then write some code for the AfterUpdate of the combo to test to see whether the entered value is in the list that populated the combo, but would like to avoid this extra coding if possible. Thanks -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I have several combo boxes on a user form that I am using to populate cells in a hidden worksheet. After one of these combo boxes gets the focus, I am unable to move the focus to another control on the form if I don't select a value from the combo box. This error also occurs after I have seleted a value from the combo an then gone back and deleted it. The dialog box does not indicate a particular error number, and only gives the OK button to exit. To move my cursor out of the control, I have to escape out, which voids the other changes I have made to that "record". I'm sure there is a property (AllowNulls) or something like that that I am missing on these combo boxes, would someone please enlighten me? Dale -- Email address is not valid. Please reply to newsgroup only. |
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 | |||
Could not set the ControlSource property. Invalid property value error | 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 | |||
Invalid Property Value Error | Excel Programming |