Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges
determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
IIRC, this happens when the linkedcell or listfillrange is on the same sheet.
(I'm not sure which one--or if it's both). Can you move those linkedcells and listfillranges to another sheet? Or even drop the linkedcells and listfillranges and do everything in code? R Ormerod wrote: I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
Actually, I was incorrect below - the workbook contains 3 x worksheets, as
follows: Cover - combo boxes and linked cells Report - formulae referencing the linked cells on the Cover worksheet Data - list fill ranges for all the combo boxes in the Cover worksheet How does this affect things? Doing everything in code is an option but I don't really have the time! -- R Ormerod "Dave Peterson" wrote: IIRC, this happens when the linkedcell or listfillrange is on the same sheet. (I'm not sure which one--or if it's both). Can you move those linkedcells and listfillranges to another sheet? Or even drop the linkedcells and listfillranges and do everything in code? R Ormerod wrote: I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
What happens when you move the linked cells to a different sheet?
R Ormerod wrote: Actually, I was incorrect below - the workbook contains 3 x worksheets, as follows: Cover - combo boxes and linked cells Report - formulae referencing the linked cells on the Cover worksheet Data - list fill ranges for all the combo boxes in the Cover worksheet How does this affect things? Doing everything in code is an option but I don't really have the time! -- R Ormerod "Dave Peterson" wrote: IIRC, this happens when the linkedcell or listfillrange is on the same sheet. (I'm not sure which one--or if it's both). Can you move those linkedcells and listfillranges to another sheet? Or even drop the linkedcells and listfillranges and do everything in code? R Ormerod wrote: I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
I'll set up all the linked cells in the Data worksheet and test it.
Unfortunately I haven't got time to complete this today and know that these threads go 'dead' after a few days. If I reply to this message next week will you still pick it up? -- R Ormerod "Dave Peterson" wrote: What happens when you move the linked cells to a different sheet? R Ormerod wrote: Actually, I was incorrect below - the workbook contains 3 x worksheets, as follows: Cover - combo boxes and linked cells Report - formulae referencing the linked cells on the Cover worksheet Data - list fill ranges for all the combo boxes in the Cover worksheet How does this affect things? Doing everything in code is an option but I don't really have the time! -- R Ormerod "Dave Peterson" wrote: IIRC, this happens when the linkedcell or listfillrange is on the same sheet. (I'm not sure which one--or if it's both). Can you move those linkedcells and listfillranges to another sheet? Or even drop the linkedcells and listfillranges and do everything in code? R Ormerod wrote: I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box VBA error
Someone will pick it the thread.
R Ormerod wrote: I'll set up all the linked cells in the Data worksheet and test it. Unfortunately I haven't got time to complete this today and know that these threads go 'dead' after a few days. If I reply to this message next week will you still pick it up? -- R Ormerod "Dave Peterson" wrote: What happens when you move the linked cells to a different sheet? R Ormerod wrote: Actually, I was incorrect below - the workbook contains 3 x worksheets, as follows: Cover - combo boxes and linked cells Report - formulae referencing the linked cells on the Cover worksheet Data - list fill ranges for all the combo boxes in the Cover worksheet How does this affect things? Doing everything in code is an option but I don't really have the time! -- R Ormerod "Dave Peterson" wrote: IIRC, this happens when the linkedcell or listfillrange is on the same sheet. (I'm not sure which one--or if it's both). Can you move those linkedcells and listfillranges to another sheet? Or even drop the linkedcells and listfillranges and do everything in code? R Ormerod wrote: I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges determining the values in the drop-down list and Linked Cells where the results of the selection are sent in 2 x other worksheets respectively. When I attempt to Close or Save the workbook with the active worksheet being either of those other than the cover, the Run Time Error 1004: Select method of Range class failed occurs. Debug always selects the Private Sub ComboBox_Change() code for one particular combo box: Sheets("Cover").Range("C13").Select This is identical to the code for all the other boxes (apart from the cell reference), the idea being that as soon as a selection has been made the combo box contents are deselected and the cursor moves to the cell below the box. The following code was recommended by this forum which resolved the problem: Application.Goto Sheets("Cover").Range("C13"), Scroll:=False However, this new code means that the contents of this combo box after drop-down selection are still highlighted and can be edited. I changed the combo box Style to 2-fmStyleDropDownList, but this still means that any other value in the list can be selected (e.g. by using the arrow keys). Another problem has now occurred when I attempt to Save As. The run time error as above occurs, but debug now highlights the Private Sub code for all of the other combo boxes in turn which still have the original code. I can change the code for all the other boxes to overcome this, but this raises two questions: 1) How can I add to the new code to deselect the combo box contents? 2) How could Save As cause the run time error in all the other boxes but Close or Save only in one? -- R Ormerod -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error using a combo box with an assigned macro in Excel | Excel Worksheet Functions | |||
Error using a combo box with an assigned macro in Excel | Excel Discussion (Misc queries) | |||
Unexpected #N/A error from INDEX-MATCH combo | Excel Worksheet Functions | |||
Combo Box List out of range error. | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) |