Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Removing items from Combo box
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Removing items from Combo box
Resolved · High Priority · Version 2003
Reply Tue 7 Jul 2009, 10:41Delegate Elaine said...
Elaine has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Removing items from Combo box
I'm having trouble adding removing items from a combo box on an excel worksheet. The code (is supposed to) select an advisor name from a list on sheet named Client_Summary. There are several rows for each advisor and the code should just pick one. It does this but does not find them all, it skips some. Once an advisor is picked it populates some agency details onto sheet1 and then adds the customers. My problem is mainly that the code is skipping advisors and also I cannot get any code to work to clear the box prior to populating. I'm getting an endless list of advisors. Code below: If I include the ClearBox procedure I get Run Time Error 70 Permission Denied, however if I remove the call to this procedure, no errors just the problems mentioned above. Any help appreciated, code below:
Private Sub CBAdvisor_Change()
Dim Advisor As String
Dim intRows As Integer
Call AddAdvisor
Call ClearBox
Advisor = Me.CBAdvisor.Value
intRows = Sheets("Client_Summary").Range("A2").CurrentRegion.Rows.Count
For i = 1 To intRows
If Sheets("Client_Summary").Cells(i, 1).Value = Me.CBAdvisor.Value Then
ActiveSheet.Range("C8").Value = Sheets("Client_Summary").Cells(i, 2).Value
ActiveSheet.Range("C10").Value = Sheets("Client_Summary").Cells(i, 3).Value
ActiveSheet.Range("C12").Value = Sheets("Client_Summary").Cells(i, 4).Value
End If
Next i
End Sub
Sub AddAdvisor()
Dim i As Integer
Dim intRows As Integer
intRows = Sheets("Client_Summary").Range("A2").CurrentRegion.Rows.Count
For i = 2 To intRows
If Sheets("Client_Summary").Cells(i, 1).Value = Sheets("Client_Summary").Cells(i - 1, 1).Value Then
i = i + 1
Else
Me.CBAdvisor.AddItem Sheets("Client_Summary").Cells(i, 1)
End If
Next i
intRows = 0
PopulateCollectionFromSheet
CreateClientList
End Sub
Sub ClearBox()
Dim i As Integer
For i = Me.CBAdvisor.ListCount - 1 To 0 Step -1
Me.CBAdvisor.RemoveItem i
Next i
End Sub
For upcoming training course dates see: Pricing & availability
RE: Removing items from Combo box
Hi Elaina, thanks for the query. It's difficult to advise on this code without seeing the data it's dealing with, particularly the formatting applied to your cells. However, the problems you are having with clearing your ComboBox may be because you have explicitly declared your Adviser variable as a string but the data then passed to that variable is a mixture of data types. Have a look at this forum post which describes a similar situation and the resolution:
http://www.ozgrid.com/forum/showthread.php?t=82802
The skipping of rows you describe sounds like there is something going on with your source data. Format the entire Adviser name column as text and make sure your the data types you are manipulating in your code are "like for like". Let us know how you get on.
Hope this helps,
Anthony
Reply Fri 10 Jul 2009, 09:09Delegate Elaine said...
RE: Removing items from Combo box
Hi Anthony,
Thanks for your response. I seem to have resolved the combo box clearing problem, but still have the problem of it skipping items in the list used to populate it. I can send the whole file, once I've made some changes to it for DPA reasons. What is best way to send this to you? The problem is driving me insane - I see no reason for it.
Thanks
Elaine
RE: Removing items from Combo box
Hello,
Sorry for the delay in responding.
Did you ever send Anthony the file (ie. did he contact you outside the forum at all?)
If not, you can e-mail the file to forum
, quote the URL of this forum post, and I will forward it to Anthony.
Regards, Rich
Reply Tue 1 Sep 2009, 09:40Delegate Elaine said...
RE: Removing items from Combo box
Sorry, I should have marked this as resolved! I managed to fix this problem, but I'm still not sure how - just got lucky. I changed data formats and the order of some statements and it worked. I don't think there was anything wrong with the code, just the ordering of what I was trying to do.
Thanks for your help.
Elaine
|