Default300
Board Regular
- Joined
- Jul 13, 2009
- Messages
- 83
PREAMBLE:
Hi. I'm a self taught Excel enthusiast. Although I have no formal training, quite a few years ago, I used to programme my ZX81 and ZX Spectrum and laid down some tracks in my brain. I have been working with VBA on and off for about a year or two.
This was going to be a desperate plea for help, but while composing my post, I've found the solution that eluded me for days!
So now it is a post to help others in same situation. NB: As I said, I'm no expert, so there may be an additional underlying problem of which I am unaware.
ERROR:
Run-time Error '380':
Could not set the Value property. Invalid property value.
BOTTOM LINE: (If you get this error) Avoid Custom Number Format in Cells of Table to be used as a RowSource, and/or BoundColumn, and/or Value, and/or Control Source for a ComboBox.
I had formatted one column of my Worksheet Data Table with the custom format "000" (for aesthetic reasons). This Data Table was used as a RowSource for a ComboBox. The column in question was used as an "Index No" column, and was also used as the BoundColumn of the ComboBox, returning the Values to the Control which were then sent to the Control Source on another Worksheet. It also interacted with another ComboBox which used the same index number to return data from another column.
PROBLEM: The very weird symptom was that (of the 885 rows) exactly 63 out of the first 99 returned errors, with gaps of 1-2 rows that were fine!
I hypothesised that it was more than a coincidence that the error only occurred between 1-99 (or 001-099 with custom formatting). If I changed the index starting at 11 then OldRows 090 - 099 now had no error (presumably because they were now 100 - 109).
I still do not understand why some rows had no problem. Mine is not to reason why, if I can get on with the rest of the project now! But if someone (presumably a mathematician) wants a challenge here they are:
Error Rows:
1 2 3 4 6 7 8 10 11 12 15 17 19 21 22 24 25 27 29 30 32 34 35 36 37 38 40 41 42 43 45 46 47 48 49 50 51 52 54 55 58 60 61 63 64 65 66 69 70 71 73 75 76 77 78 80 81 82 84 86 88 90 93 94 95 96 97 98 99
Non-Error Rows:
5 9 13 14 16 18 20 23 26 28 31 33 39 44 53 56 57 59 62 67 68 72 74 79 83 85 87 89 91 92
Hi. I'm a self taught Excel enthusiast. Although I have no formal training, quite a few years ago, I used to programme my ZX81 and ZX Spectrum and laid down some tracks in my brain. I have been working with VBA on and off for about a year or two.
This was going to be a desperate plea for help, but while composing my post, I've found the solution that eluded me for days!
So now it is a post to help others in same situation. NB: As I said, I'm no expert, so there may be an additional underlying problem of which I am unaware.
ERROR:
Run-time Error '380':
Could not set the Value property. Invalid property value.
BOTTOM LINE: (If you get this error) Avoid Custom Number Format in Cells of Table to be used as a RowSource, and/or BoundColumn, and/or Value, and/or Control Source for a ComboBox.
I had formatted one column of my Worksheet Data Table with the custom format "000" (for aesthetic reasons). This Data Table was used as a RowSource for a ComboBox. The column in question was used as an "Index No" column, and was also used as the BoundColumn of the ComboBox, returning the Values to the Control which were then sent to the Control Source on another Worksheet. It also interacted with another ComboBox which used the same index number to return data from another column.
PROBLEM: The very weird symptom was that (of the 885 rows) exactly 63 out of the first 99 returned errors, with gaps of 1-2 rows that were fine!
I hypothesised that it was more than a coincidence that the error only occurred between 1-99 (or 001-099 with custom formatting). If I changed the index starting at 11 then OldRows 090 - 099 now had no error (presumably because they were now 100 - 109).
I still do not understand why some rows had no problem. Mine is not to reason why, if I can get on with the rest of the project now! But if someone (presumably a mathematician) wants a challenge here they are:
Error Rows:
1 2 3 4 6 7 8 10 11 12 15 17 19 21 22 24 25 27 29 30 32 34 35 36 37 38 40 41 42 43 45 46 47 48 49 50 51 52 54 55 58 60 61 63 64 65 66 69 70 71 73 75 76 77 78 80 81 82 84 86 88 90 93 94 95 96 97 98 99
Non-Error Rows:
5 9 13 14 16 18 20 23 26 28 31 33 39 44 53 56 57 59 62 67 68 72 74 79 83 85 87 89 91 92
