+ Reply to Thread
Results 1 to 7 of 7

How can I code a Userform to provide dropdown boxes to filter data?

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Lichfield, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    5

    How can I code a Userform to provide dropdown boxes to filter data?

    Dear forum, I am new to this and this is my first post.

    I have attached a file of dummy data to explain what I am trying to do.

    I am the owner of a shared spreadsheet on a network drive accessed by approximately 160 project managers (PM), the majority of whom manage more than 1 project. The spreadsheet has 5 columns of data:

    Project Manager Name
    Project Number
    Project Name
    Forecast Spend
    Costs to Date

    I am attempting to create a userform that will be invoked automatically on file open, wherein the PM will find his/her name in a drop down box (populated from column A). This then needs to populate a second dropdown box with only those Project Numbers that are managed by that PM (column B). I have added Project Name to the form as a check for the PM only and has no further function, but needs to be populated from column C.

    Upon choosing a project number, i want the 'Current Forecast Spend' textbox to show the current forecast spend (populated from column D). If the PM wants to update this, he is to input his new forecast spend in a second text box, the contents of which will be updated back onto the worksheet (back to column D). Same procedure for "Costs to date" (column E).

    My actual real data sheet has about 3000 rows, which varies from month to month as projects are completed or added.

    I've made a start, but I'm afraid i need as much help as i can get!! I understand this may be a very big ask for a first post.

    I guess the first problem i have is how to display only unique PM Names from Column A in the Project Manager Name dropdown box. I've managed (after searching through many of the other threads on this site) to create some code that does indeed populate the first drop down, but shows all of the values in col A, not just the unique values.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: How can I code a Userform to provide dropdown boxes to filter data?

    Unique list.
    Please Login or Register  to view this content.
    All items that match Unique list.
    Please Login or Register  to view this content.
    Fill Text boxes with relevant data of Combobo
    Please Login or Register  to view this content.
    Commandbutton to move data to sheet.
    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    08-09-2012
    Location
    Lichfield, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    5

    Re: How can I code a Userform to provide dropdown boxes to filter data?

    David

    Many many thanks. Great response and yout solution works a treat. I'll add the command button to the user form to write the data back to the sheet.

    Regards

    Gary

  4. #4
    Registered User
    Join Date
    08-09-2012
    Location
    Lichfield, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    5

    Thumbs up Re: How can I code a Userform to provide dropdown boxes to filter data?

    Just found the "Rate Thread" button! Have rated this response as 'Excellent"

  5. #5
    Registered User
    Join Date
    08-09-2012
    Location
    Lichfield, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    5

    Re: How can I code a Userform to provide dropdown boxes to filter data?

    Apologies, marked this as solved and do actually have a couple of questions, so have marked as unsolved for the time being.

    Q1 - Having added a command button to update data, how can i re-use the userform to amend other rows of data without closing and then re-loading the user form?

    Q2 - how can i amend the following lines to get input to cell in 'Accounting" format?:


    Please Login or Register  to view this content.
    Best regards
    Last edited by Cutter; 08-13-2012 at 04:46 PM. Reason: Added code tags

  6. #6
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: How can I code a Userform to provide dropdown boxes to filter data?

    Q1. Just pick another User and/or project Number.
    Q2. Use the format command. aCell.Offset(, 4).Value = Format(Me.TextBox3, "0.00")

    One change to CBox2
    Please Login or Register  to view this content.
    Add the For/Next (to clear the textboxes) just after the Combobx2.Clear in the Combobox1_Change event.
    Last edited by Tinbendr; 08-13-2012 at 04:33 PM.

  7. #7
    Registered User
    Join Date
    08-09-2012
    Location
    Lichfield, England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    5

    Re: How can I code a Userform to provide dropdown boxes to filter data?

    Many thanks David

    works perfectly now.

    Gary

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1