Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Close DropDown of ComboBox2 & Open ComboBox1 on Error

Newbie
  
Join Date: Jul 2006
Posts: 7
#1: Sep 23 '09
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo2_MouseDown(ByVal Button As Integer, _
  2.   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  3.  
  4.     If Cbo1.Value = "0" Then
  5.  
  6.         MsgBox "Error, choose ComboBox1, first"
  7.         Cbo2.ListIndex = 0
  8.  
  9. 'I want to deselect the Cbo2 DropDown
  10.         Cbo2.DropDown = False  'Compile error here
  11. 'Select Cbo1, like Select.Range
  12.         Cbo1.DropDown
  13.  
  14.     Else
  15.         Cbo2.ListIndex = 0
  16.     End If
  17. End Sub
--
Thanks, Kevin
best answer - posted by AFSSkier
Thanks to NeoPa, OldBirdman & FishVal's suggestions, I was able to get a resolution to my VBA code issue for Excel.

The original question was how do you "Close DropDown of Cbo2 & Open Cbo1 on Error"? I resolved this by disabling Cbo2 (Enabled = False), until a selection is made in Cbo1.

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.

The following code is what I'm using.

Thank you for all of your help & efforts,
Kevin

Expand|Select|Wrap|Line Numbers
  1. Option Explicit  
  2. ‘Public blEnabled As Boolean    ‘this did not seem to make any difference to the Run-time errors  
  3. Private Sub cbo1_Change()  
  4. ‘If blEnabled Then Exit Sub    ‘this did not seem to make any difference to the Run-time errors  
  5.  
  6. Dim rng As Range, RowSrc As String  
  7. Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$D$126")  
  8. RowSrc = rng.Address(External:=True)  
  9.  
  10. If cbo1.Value = "0" Then  
  11. cbo2.ListFillRange = "" 'reset RowSrc for cbo list to null/blank  
  12. cbo2.Value = "%" 'wild card to Requery All records for CatData import  
  13.  
  14. On Error Resume Next    'to resolve unnecessary Run-time errors
  15. cbo2.Enabled = False 'Run-time error 1004 on RefreshAll  
  16. cbo3.Enabled = False 'Run-time error 1004 on RefreshAll  
  17. cbo4.Enabled = False 'Run-time error 1004 on RefreshAll  
  18. On Error GoTo 0
  19.  
  20. Else  
  21. cbo2.ListFillRange = RowSrc    ‘from sheets("CatData").Range  
  22. On Error Resume Next    'to resolve unnecessary Run-time errors
  23. cbo2.ListIndex = 0 'Run-time error 380 on close  
  24. cbo2.Enabled = True 'Run-time error 1004 on RefreshAll  
  25. On Error GoTo 0
  26. End If
  27. ‘similarly continued through cbo[2,3,4]_Change subs for Cascading effect  
  28. End Sub

NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#2: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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!
Site Addict
  
Join Date: Mar 2007
Location: Oakland, California, USA
Posts: 522
#3: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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.
Expand|Select|Wrap|Line Numbers
  1. SendKeys "{F4}"
FishVal's Avatar
Expert
  
Join Date: Jun 2007
Location: Israel
Posts: 2,584
#4: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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

Any operation with MSForms.Combobox.Locked property makes dropdown list dissapear.

e.g.
the following code does nothing, but calling "Lock" property which results in dropdown list disappearing.
Expand|Select|Wrap|Line Numbers
  1. dummy = <combobox ref>.Locked
  2.  
FYI: DropDown is a method of MSForms.Combobox 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
Expand|Select|Wrap|Line Numbers
  1. With <combobox ref>
  2.     .Value = .Value
  3. End With
  4.  
does the same.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#5: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Quote:

Originally Posted by FishVal View Post

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

Expand|Select|Wrap|Line Numbers
  1. With <combobox ref>
  2.     .Value = .Value
  3. End With
  4.  
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's Avatar
Expert
  
Join Date: Jun 2007
Location: Israel
Posts: 2,584
#6: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Quote:

Originally Posted by afsskier View Post

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

. .
Newbie
  
Join Date: Jul 2006
Posts: 7
#7: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Cbo1_Change()
  2.     If Cbo1.Value = "0" Then
  3.         Cbo2.ListIndex = 0
  4.         Cbo3.ListIndex = 0
  5.         Cbo4.ListIndex = 0
  6.         'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub)
  7.         Cbo2.Enabled = False
  8.         Cbo3.Enabled = False
  9.         Cbo4.Enabled = False
  10.     Else
  11.         Cbo2.ListIndex = 0
  12.     'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub)
  13.         Cbo2.Enabled = True
  14.  End If
  15. End Sub
  16.  
  17. Private Sub CmdRefreshAll_Click()
  18. 'Refresh data sheets from outside data sources & Pivotsheets
  19.     Application.ScreenUpdating = False
  20.     ActiveWorkbook.RefreshAll
  21. 'Add RefreshAll date & time to Form page
  22.     Sheets(1).Select
  23.     Range("H1").Value = "Refresh All Date: "
  24.     Range("H2").Value = Now
  25.     Application.ScreenUpdating = True
  26. MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated"
  27. End Sub
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#8: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Quote:

Originally Posted by FishVal View Post

Quote:

Originally Posted by afsskier View Post

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

Ah. I missed that.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#9: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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).
Newbie
  
Join Date: Jul 2006
Posts: 7
#10: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#11: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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().
Newbie
  
Join Date: Jul 2006
Posts: 7
#12: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#13: Sep 24 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Quote:

Originally Posted by AFSSkier View Post

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?
Newbie
  
Join Date: Jul 2006
Posts: 7
#14: Sep 27 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

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.ListFillRange
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_Click 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 "BabyCategory" 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).

Expand|Select|Wrap|Line Numbers
  1. Option Explicit 
  2. Public blEnabled As Boolean 
  3. Private Sub cbo1_Change() 
  4. If blEnabled Then Exit Sub
  5.  
  6. Dim rng As Range, RowSrc As String 
  7. Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$D$126") 
  8. RowSrc = rng.Address(External:=True) 
  9.  
  10. If cbo1.Value = "0" Then 
  11. cbo2.ListFillRange = "" 'reset cbo list to null/blank 
  12. cbo2.Value = "%" 'wild card to Requery All records for CatData 
  13. import 
  14. cbo2.Enabled = False 'Run-time error 1004 on RefreshAll 
  15. cbo3.Enabled = False 'Run-time error 1004 on RefreshAll 
  16. cbo4.Enabled = False 'Run-time error 1004 on RefreshAll 
  17.  
  18. Else 
  19.  
  20. cbo2.ListFillRange = RowSrc 
  21. cbo2.ListIndex = 0 'Run-time error 380 on close 
  22. cbo2.Enabled = True 'Run-time error 1004 on RefreshAll 
  23.  
  24. End If 
  25. End Sub
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#15: Sep 27 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Quote:

Originally Posted by AFSSkier View Post

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.
Quote:

Originally Posted by AFSSkier View Post

I'm getting a Run-time error 1004 on the RefreshAll_Click 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).
Newbie
  
Join Date: Jul 2006
Posts: 7
#16: Oct 9 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Thanks to NeoPa, OldBirdman & FishVal's suggestions, I was able to get a resolution to my VBA code issue for Excel.

The original question was how do you "Close DropDown of Cbo2 & Open Cbo1 on Error"? I resolved this by disabling Cbo2 (Enabled = False), until a selection is made in Cbo1.

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.

The following code is what I'm using.

Thank you for all of your help & efforts,
Kevin

Expand|Select|Wrap|Line Numbers
  1. Option Explicit  
  2. ‘Public blEnabled As Boolean    ‘this did not seem to make any difference to the Run-time errors  
  3. Private Sub cbo1_Change()  
  4. ‘If blEnabled Then Exit Sub    ‘this did not seem to make any difference to the Run-time errors  
  5.  
  6. Dim rng As Range, RowSrc As String  
  7. Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$D$126")  
  8. RowSrc = rng.Address(External:=True)  
  9.  
  10. If cbo1.Value = "0" Then  
  11. cbo2.ListFillRange = "" 'reset RowSrc for cbo list to null/blank  
  12. cbo2.Value = "%" 'wild card to Requery All records for CatData import  
  13.  
  14. On Error Resume Next    'to resolve unnecessary Run-time errors
  15. cbo2.Enabled = False 'Run-time error 1004 on RefreshAll  
  16. cbo3.Enabled = False 'Run-time error 1004 on RefreshAll  
  17. cbo4.Enabled = False 'Run-time error 1004 on RefreshAll  
  18. On Error GoTo 0
  19.  
  20. Else  
  21. cbo2.ListFillRange = RowSrc    ‘from sheets("CatData").Range  
  22. On Error Resume Next    'to resolve unnecessary Run-time errors
  23. cbo2.ListIndex = 0 'Run-time error 380 on close  
  24. cbo2.Enabled = True 'Run-time error 1004 on RefreshAll  
  25. On Error GoTo 0
  26. End If
  27. ‘similarly continued through cbo[2,3,4]_Change subs for Cascading effect  
  28. End Sub
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#17: Oct 9 '09

re: Close DropDown of ComboBox2 & Open ComboBox1 on Error

Well Kevin. Kudos for sorting this on your own, and thanks for posting your solution.

Looking through it I had one of those hit myself over the head moments. I've been using the .Address property for a while now, but I never realised it was a function call (Maybe I've got the terminlogy wrong here but I'd have called it a Method). I never realised there were parameters to it whereby one can specify the format of the returned address string. The External parameter particularly would have been pretty handy to know before now. I've always worked within the $A$1 format of the address. I'll know better now, so thanks for that bit of learning :)
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved