1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform Listbox query

Discussion in 'VBA Macros' started by Svmaxcel, Jan 7, 2019.

  1. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    Team,

    Based on earlier suggestion, I have prepared the below code.
    I created a user form, image attached.

    Let me give you an insight into how it works for easy understanding.

    As you see the image, its optional for the user to select the dates.
    User has 2 options Select from List and Instant Search.

    When the user selects Instant Search, text box is focused and user can type the initials of the person, he is looking for and the list box displays 2 rows of Manager Name and Emp Name.

    Problem Area
    Only 2 Columns are displayed, I want 5 columns with the data from the sheet.
    I can change the column count in listbox propertly, but how do i get more data in list box columns.

    The Raw Data i have has Columns starting from A to AB, and i need the data in List box from col I to M.
    Below is the code, and this works fine for 2 columns

    Cannot attach File due to security reason



    Code (vb):
    Private Sub InstantEmp_Change()
    Dim i As Long
    Me.InstantEmp.Text = StrConv(Me.InstantEmp.Text, vbProperCase)
    Me.DataResult.Clear
    For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("I:I"))
    a = Len(Me.InstantEmp.Text)
    If Left(Sheet1.Cells(i, 9).Value, a) = Left(Me.InstantEmp.Text, a) Then
    Me.DataResult.AddItem Sheet1.Cells(i, 10).Value
    Me.DataResult.List(DataResult.ListCount - 1, 1) = Sheet1.Cells(i, 9).Value
    End If
    Next i
    End Sub

    Attached Files:

    ThrottleWorks likes this.
  2. charlesdh

    charlesdh Member

    Messages:
    68
    Would it be possible to redact the file and post it?
  3. charlesdh

    charlesdh Member

    Messages:
    68
    xvmaxcel,
    I think you need to check the "List" box column count. If it's 2 then change it to 5. This may work.
  4. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    i tried to increase the count of list box, but how can i make it to display certain columns.example (F:J)
  5. charlesdh

    charlesdh Member

    Messages:
    68
    Can you post a redacted copy of your file.
  6. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    Sure, I am creating the file with dummy content and will share shortly
  7. charlesdh

    charlesdh Member

    Messages:
    68
    Thanks,
    I just sat down to work on this file and was about to create my own file.
    But, received mail that you were going to send a file.
  8. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    Dear Team,

    I have uploaded the file.

    User can select only 1 option, Either instant search or Select from list

    What i want is --

    1)when i click on Instant search and type employee name, i want data matching to be displayed from Data Sheet Column E to P in DataResult

    2a)when i click on Select from list and fille in LOB Name in Combo box, i want relevant data to come in DataResult (Data Sheet Column E to P)

    2b)Similarly when a user selects LOB Name, Manager Name then relevant info should be displayed in DataResult (Data Sheet Column E to P)

    2c)Similar goes for Lob Name/Manager Name and Emp Name.

    I hope i am trying to make my query understandable to all of you, the coding might be too complex here, but please ask any relevant questions if any

    Attached Files:

  9. charlesdh

    charlesdh Member

    Messages:
    68
    Svmaxcel,
    Are you wanting the "Listbox" to list the required range first and then refresh when the user make their selection and only show only their selection?
  10. charlesdh

    charlesdh Member

    Messages:
    68
    Hi,
    I have a code worked out for the desired selection of the "Instant" search.
    It will populate the listbox. But, at this time I need to modify it so that it will repopulate the list box as you type the desired name to show.
    I will be gone reset of today and tomorrow.
  11. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    Thanks for your help

    Let me give you an example

    User clicks on Instant Search and types ''s', at this time I want all employees name starting with S.

    Now if the user wants to get employees name starting with 'd', he will clear the text box and type d. At this time listbox should refresh and only show employees names starting with D.
  12. Svmaxcel

    Svmaxcel Member

    Messages:
    87
    A
    Hope you are back, please assist.

Share This Page