|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to filter data before it is pulled into combobox/listbox AND remove data from listbox
Hi guys,
I have recently been building a call tracker (using a userform to obtain data of companies to ring (in certain dates etc) and submit new data to the call records). I am almost finished but there are a couple of obstacles I can't seem to overcome and unsure if it is just me or if they aren't doable. I've only just started learning VBA as I go with this so not sure what is and isn't possible but haven't been able to find an answer on google etc:
Thank you if you are able to help at all! |
#2
|
|||
|
|||
For your first point, try changing the CompanyName MatchRequired to False and using CompanyName_BeforeUpdate to check for match.
Code:
Private Sub CompanyName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim myString As String Dim i As Long Dim strFound As Boolean 'check if combobox value exists in .list myString = Me.CompanyName.Text strFound = False With Me.CompanyName 'Loop through combobox list For i = 0 To .ListCount - 1 If .List(i) = myString Then strFound = True Exit For End If Next i End With If Not strFound Then 'entry does not exist If Len(myString) > 0 Then MsgBox "The entered value is not in the list" Cancel = True 'keeps you in the combo Me.CompanyName = "" Exit Sub Else 'clear the call history Me.CallHistory.Clear Exit Sub End If Else 'entry exists Call CompanyName_Click End If End Sub |
#3
|
|||
|
|||
Thanks! This works perfectly!! Much appreciated.
Don't suppose you have any other genius ideas for the other points? |
#4
|
|||
|
|||
For point 2, after determining arrUniqueCompanies you could try this to determine the latest date and what goes into the 'Daily List'
Code:
' Find latest 'date to be called' by filtering table and using subtotal With Sheets("Data").ListObjects("tblMain").Range For i = LBound(arrUniqueCompanies) To UBound(arrUniqueCompanies) company = arrUniqueCompanies(i) 'remove any existing filters .AutoFilter 'filter for company .AutoFilter Field:=4, Criteria1:=company, Operator:=xlFilterValues 'the latest date dateToBeCalled = WorksheetFunction.Subtotal(104, Sheets("Data").ListObjects("tblMain").ListColumns(8).DataBodyRange) 'Populate the 'Daily Call' list box If dateToBeCalled <= Date Then DailyList.AddItem arrUniqueCompanies(i) DailyList.List(i, 1) = Format(dateToBeCalled, "dd-mmm-yy") End If 'remove filter .AutoFilter Next i End With |
#5
|
|||
|
|||
Oh thank you (again)! This is great and works perfectly. I'm so impressed
1 questions though, my listbox is generated under userform_initialise, when I click sumbit or even close and reopen the userform, it hasn't updated the daily list (presumably because the userform is just hiding and showing not re-initialising), is there a way I can get a 'refresh' button or assign to the open userform button to do the same? I.E. Company A is on daily call list for today, I call them and they ask for a callback in 2 weeks. When I submit, and maybe close and reopen the form, they are still in the call list until I save-close-reopen the workbook. |
#6
|
|||
|
|||
Point 3:
How about adding a 'Do Not Call' column which gets an 'X' written to it for every record of that particular company? Something along the lines of this within the SubmitBtn_Click procedure Code:
' Dim oLo As ListObject Set oLo = ActiveSheet.ListObjects(1) 'first table of active sheet ' ' '------------ CALLBACK OPTION CHOICE--------------- If CallbackY.Value = True Then Cells(lngrow, 7) = "Yes" ElseIf CallbackN.Value = True Then Cells(lngrow, 7) = "No" 'add X to 'DoNotCall' for all rows of this company With oLo.Range .AutoFilter 'remove any existing filters .AutoFilter Field:=4, Criteria1:=CompanyName.Value, Operator:=xlFilterValues oLo.ListColumns(13).DataBodyRange.Cells.Value = "X" .AutoFilter 'remove filter End With Else End If ' Move the code from UserForm_Initialize to a procedure of its own and have UserForm_Initialize call that procedure. You can then refresh (re-initialize) from anywhere by calling that procedure. For example at the end of CloseBtn_Click just before hiding the form, at the end of ResetBtn_Click and SubmitBtn_Click. Have a look at the attached file and see if it does the things you're after. I unchecked the vba reference to Microsoft Windows Common Controls 6.0 (SP6) in order to run your file on my computer. |
#7
|
|||
|
|||
Was the previous post just a waste of time ?
|
#8
|
|||
|
|||
Omg no this is perfect thank you. I didn't get the email notification that anyone had replied!
Did you self teach yourself VBA? Everything you've done on here for me is seriously appreciated and really impressive! |
#9
|
|||
|
|||
Everything with Excel has pretty much been trial and error.
Always found macros more interesting than formulas, would see a question in the forums that sounded interesting and dissect how others approached solving it. Anyway, glad I was able to assist and good luck with your project. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Listbox or Combobox population | wannaExcel | Excel Programming | 4 | 04-06-2019 02:53 PM |
Add-In creates ListBox from XLS, but data not saved in ppt | barbet | PowerPoint | 0 | 05-26-2016 02:30 AM |
This is a Userform LIstbox queston: A variable does not set to the value of a listbox | CatMan | Excel Programming | 14 | 08-18-2014 08:14 PM |
This is a Userform LIstbox queston: A variable does not set to the value of a listbox | CatMan | Excel | 1 | 08-08-2014 09:41 AM |
Data from Excel to Listbox multiselect to Word | marksm33 | Word VBA | 2 | 02-18-2014 08:30 AM |