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

**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