Close DropDown of ComboBox2 & Open ComboBox1 on Error
I have several cascading ComboBoxes & would like to close Cbo2 & open Cbo1
when there is an error. These CboBoxes are on a sheet, not a UserForm. I have the following code, if the Dropdown is opened it requeries Cbo2 to ListIndex = 0. But I also want it to close (unselect, undrop list) Cbo2 & open (select, dropdown) Cbo1 on MsgBox error. This insures the user selects property of Cbo1 first. Private Sub cbo2_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Cbo1.Value = "0" Then MsgBox "Error, choose ComboBox1, first" Cbo2.ListIndex = 0 'I want to deselect the Cbo2 DropDown Cbo2.DropDown = False 'Compile error here 'Select Cbo1, like Select.Range Cbo1.DropDown Else Cbo2.ListIndex = 0 End If End Sub -- Thanks, Kevin |
Close DropDown of ComboBox2 & Open ComboBox1 on Error
**Resolved with Enabled = False/True property in a Change sub**.
The MouseDown event wasn't working like I wanted. So, I chang to a Change sub. Also, there seems to be no way to cancel the Dropdown explicitly. By disabling the cbo2 with Enabled = False. Then Enabled = True when a value is selected in cbo1. However to resolve the Run-time errors, I used "On Error Resume Next" and "On Error GoTo 0". I know its not a good idea to use, but the Enable Properties Run-time errors during the RefreshAll and Close are resolved by them. Option Explicit Private Sub cbo1_Change() Dim rng As Range, RowSrc As String Set rng = ActiveWorkbook.Worksheets("sheet1").Range("$A$1:$D $126") RowSrc = rng.Address(External:=True) If cbo1.Value = "0" Then cbo2.ListFillRange = "" 'reset RowSrc for cbo list to null/blank cbo2.Value = "%" 'wild card to Requery All records for cbo2 data import On Error Resume Next 'to resolve unnecessary Run-time errors cbo2.Enabled = False 'Run-time error 1004 on RefreshAll cbo3.Enabled = False 'Run-time error 1004 on RefreshAll cbo4.Enabled = False 'Run-time error 1004 on RefreshAll On Error GoTo 0 Else cbo2.ListFillRange = RowSrc €˜from sheets("sheet1").Range On Error Resume Next 'to resolve unnecessary Run-time errors cbo2.ListIndex = 0 'Run-time error 380 on close cbo2.Enabled = True 'Run-time error 1004 on RefreshAll On Error GoTo 0 End If €˜similarly continued through cbo[2,3,4]_Change subs for Cascading effect End Sub -- Thanks, Kevin "AFSSkier" wrote: I have several cascading ComboBoxes & would like to close Cbo2 & open Cbo1 when there is an error. These CboBoxes are on a sheet, not a UserForm. I have the following code, if the Dropdown is opened it requeries Cbo2 to ListIndex = 0. But I also want it to close (unselect, undrop list) Cbo2 & open (select, dropdown) Cbo1 on MsgBox error. This insures the user selects property of Cbo1 first. Private Sub cbo2_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Cbo1.Value = "0" Then MsgBox "Error, choose ComboBox1, first" Cbo2.ListIndex = 0 'I want to deselect the Cbo2 DropDown Cbo2.DropDown = False 'Compile error here 'Select Cbo1, like Select.Range Cbo1.DropDown Else Cbo2.ListIndex = 0 End If End Sub -- Thanks, Kevin |
All times are GMT +1. The time now is 12:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com