Close DropDown of ComboBox2 & Open ComboBox1 on Error

[AFSSkier]

I have several cascading ComboBoxes & would like to close Cbo2 & open Cbo1
when there is an error. These CboBoxes are on an Excel spreadsheet, 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

[NeoPa]

There seems to be no way to cancel the .Dropdown explicitly Kevin (not that I could find anyway) however, setting the focus to another control (and then back again if required) seems to do the trick for you.

Good luck & Welcome to Bytes!

[OldBirdman]

It won't work in the MouseDown event, but in the MouseUp you can use SendKeys and send F4. Warning, the F4 will do a dropdown if it is not already dropped down, and I know of no way of determining the state of the dropdown list.

SendKeys "{F4}"

[FishVal]

The following seems to be an undocumented feature - that means you've been warned.

Any operation with MSForms.Combobo x.Locked property makes dropdown list dissapear.

e.g.
the following code does nothing, but calling "Lock" property which results in dropdown list disappearing.

dummy = <combobox ref>.Locked
 

FYI: DropDown is a method of MSForms.Combobo x class. You cannot assign value to it (like you could do with property) and the only thing it does is showing dropdown list.

Regards,
Fish

PS. "Locked" property seems to be not the only option - actually, one from a bunch.
A trivial

With <combobox ref>
    .Value = .Value
End With
 

does the same.

[NeoPa]

@FishVal
I tried that one Fish (some experimenting to see if I could find something), and it didn't work for me. I use Access 2003.

[FishVal]

@afsskier
. .

[AFSSkier]

Instead of the MouseDown sub, I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns.

However I’m getting a “Run-time error 1004” on the Enable = True/False, from my CmdButton sub “CmdRefreshAll_ Click” (see 2nd sub below). It has an ActiveWorkbook. RefreshAll. I even tried reversing the If in the Cbo.

Private Sub Cbo1_Change()
    If Cbo1.Value = "0" Then
        Cbo2.ListIndex = 0
        Cbo3.ListIndex = 0
        Cbo4.ListIndex = 0
        'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub)
        Cbo2.Enabled = False
        Cbo3.Enabled = False
        Cbo4.Enabled = False
    Else
        Cbo2.ListIndex = 0
    'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub)
        Cbo2.Enabled = True
 End If
End Sub
 
Private Sub CmdRefreshAll_Click()
'Refresh data sheets from outside data sources & Pivotsheets
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
'Add RefreshAll date & time to Form page
    Sheets(1).Select
    Range("H1").Value = "Refresh All Date: "
    Range("H2").Value = Now
    Application.ScreenUpdating = True
MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated"
End Sub

[NeoPa]

@FishVal
Ah. I missed that.

[NeoPa]

Kevin,

I have edited two of your posts now that included code without the tags. I left edit comments but it appears you haven't noticed them. Please use the tags in future.

Administrator.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).

[AFSSkier]

Is it possible to disable sheet1 from being Refreshed by an ActiveWorkbook. RefreshAll?

It would resolve the Run-time error in the cbo.Click sub for sheet1. I don't want it to distrub the CboBoxes anyway.

I only want the ActiveWorkbook. RefreshAll to Refresh the data/Pivot sheets.