Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-07-2019, 03:39 AM
Izzii0x Izzii0x is offline Windows 10 Office 2016
Novice
 
Join Date: May 2019
Posts: 4
Izzii0x is on a distinguished road
Default 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:
  • I want a match to be required for the Company Name (as this box is the one which transfers to the data table and I only want the user to be able to submit a name which is in the preexisting list. However, when I type in the box and then delete it, it just errors out and doesn't allow me to press reset or close or click anything else. Is there a way that there can be an 'on error' piece of code which just removes the company name from the box if it doesn't match and allows the user to click around and do whatever (e.g. close or reset form) without this constant msg error? I tried making an on error code before but didn't seem to work. Could be a me issue though lol.
  • Currently there is code to populate the 'Daily List' with any companies that have been set to be called that day or earlier, but then the 'date to call' column is then populated with the most recent date written in the 'date to call' column of the data table. However if I make a new record with a future date (e.g. they call the company back and they request a new future call) this company remains in the daily call list because it had a date which was today/earlier, even though the next time they need to call is in the future so they shouldn't appear on the list at all as the latest record states they need to be called in the future. How do I get the latest 'to call' date to trump all previous records so the daily list only shows companies with their most recent 'date to call' as today or earlier? And if this was done, would the daily list remove a company if a new call had been completed for that company? (As the most recent 'date to call' would then not be today?).
  • There may be a time when a company has requested to please not be called again, or the final answer has been reached and thus they don't need to be called again and it would be unprofessional in both circumstances if they were accidentally called again. There is a frame with callback y/n and a date, and I wonder if there is a way where if 'No Callback' is selected, the "No" submitted in the data table causes the combo box 'companyname' to stop giving this company as an option (likewise the daily list, if the company was due to be called that day but has since had a record with 'no callback' selected, can this prevent the daily list from ever returning this company). I have tried a few things but the fact that a company has a previous record with 'yes' callback keeps preventing what I want to achieve.
  • Is there a way to 'refresh' the table that is populating the listboxes/combobox? E.g. I added a record with the date to call back today and nothing re-appears in the daily list box, but if I save and close then reopen excel it is there. Is there a way I can refresh the data so it immediately appears in this box or deleted the company from the daily list if they have now been called and case closed/new future date entered? Or with a click of a button if needed?
Hopefully this all makes sense, but I have attached the document as well.

Thank you if you are able to help at all!
Attached Files
File Type: xlsm Call tracker draft 3.xlsm (200.6 KB, 2 views)
Reply With Quote
  #2  
Old 05-12-2019, 07:53 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 05-13-2019, 08:46 AM
Izzii0x Izzii0x is offline Windows 10 Office 2016
Novice
 
Join Date: May 2019
Posts: 4
Izzii0x is on a distinguished road
Default

Thanks! This works perfectly!! Much appreciated.

Don't suppose you have any other genius ideas for the other points?
Reply With Quote
  #4  
Old 05-14-2019, 07:41 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #5  
Old 05-14-2019, 10:16 PM
Izzii0x Izzii0x is offline Windows 10 Office 2016
Novice
 
Join Date: May 2019
Posts: 4
Izzii0x is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 05-16-2019, 05:04 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
 '
Point 4:
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.
Attached Files
File Type: xlsm Call tracker draft 3b.xlsm (181.5 KB, 1 views)
Reply With Quote
  #7  
Old Yesterday, 06:58 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Was the previous post just a waste of time ?
Reply With Quote
  #8  
Old Yesterday, 07:06 AM
Izzii0x Izzii0x is offline Windows 10 Office 2016
Novice
 
Join Date: May 2019
Posts: 4
Izzii0x is on a distinguished road
Default

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!
Reply With Quote
  #9  
Old Yesterday, 07:25 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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.
Reply With Quote
Reply

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


All times are GMT -7. The time now is 10:06 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft