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]

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.

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]

.... These cboboxes are on an excel spreadsheet ...

. .

[AFSSkier]

Cascading CombBoxes - Cbo.Enable = True/False - Error on RefreshAll

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]

.... These cboboxes are on an excel spreadsheet ...

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.

[NeoPa]

I don't believe so Kevin, but you can refresh each sheet individually, or in a loop, instead.

The Refresh for an individual Pivot Table is RefreshTable().

[AFSSkier]

NeoPa,

Thank you for your suggestion. However, a loop will not work. Because the end user may add several new Pivots & PivotCharts. The reason of the RefreshAll".

I'm providing the user with an on demand refreshable data sheet(s) via an MSAccess connection. The Form (sheet1) has Cascading CboBoxes to aid in the filtering of the inbound data.

Thanks, Kevin

[NeoPa]

However, a loop will not work. Because the end user may add several new Pivots & PivotCharts. The reason of the RefreshAll.

As there is a collection of Sheets in a WorkBook (Sheets), and a collection of PivotTables in a WorkSheet (PivotTables), it seems quite possible to me to process through both collections (one inside the other) and refresh all the items found.

Is there something I'm missing?

[AFSSkier]

NeoPa,

Yes, there is a collection of Sheets and PivotTables in the WorkBook.

SALES DATA (sheet1) - final data imported from Access.
FORM (sht2) w/Active CboBoxes.
PIVOT (sht7) - sample sheet.
PIVOT CHART (sht8) - sample sheet.
(multiple user PivotTables)

Hidden data sheets for Cbos - imported from Access on requery
DEPT (sht3) for cbo1.ListFillRa nge
CAT (sht4) for cbo2.LFR - filtered Requery from result of cbo1.
SUBCAT (sht5) for cbo3.LFR - filtered Requery from result of cbo2.
PROMOD (sht6) for cbo4.LFR - filtered Requery from result of cbo3.

I'm getting a Run-time error 1004 on the RefreshAll_Clic k sub. Its running through the cbo_Change subs. When I comes to the (cbo.enabled property), it errors trying to change the status from true to true or false to false.

I tried moving the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange in the cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's selection values must remain as saved in the Cbos. So when the user reopens the "BabyCatego ry" workbook (for example) in a week or month. All they have to do is RefreshAll to import the new sales data. W/out the need to reselect the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit 
Public blEnabled As Boolean 
Private Sub cbo1_Change() 
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String 
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$D$126") 
RowSrc = rng.Address(External:=True) 

If cbo1.Value = "0" Then 
cbo2.ListFillRange = "" 'reset cbo list to null/blank 
cbo2.Value = "%" 'wild card to Requery All records for CatData 
import 
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 

Else 

cbo2.ListFillRange = RowSrc 
cbo2.ListIndex = 0 'Run-time error 380 on close 
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll 

End If 
End Sub

[NeoPa]

Yes, there is a collection of Sheets and PivotTables in the WorkBook.

No. That's not true. PivotTables is a collection found in a Worksheet object. There is none in a Workbook.

I'm getting a Run-time error 1004 on the RefreshAll_Clic k sub. Its running through the cbo_Change subs. When I comes to the (cbo.enabled property), it errors trying to change the status from true to true or false to false.

As far as I can tell I have absolutely no information of any one of the code parts referred to in this paragraph. How can you expect me to follow even the idea of what you're asking? I'm completely in the dark and I have no idea why you posted the code you have as it seems to bear no relationship to anything explained in the question. I should say the text as I see no question as such.

Furthermore, I see no attempt in the code to follow the direction I gave in my preceeding post (#13).