List Box For Data Entry

  • Hi, Hope All are fine.


    I look for something but didn't know how to do it. Hope someone here will help me.


    I have a datasheet. Where in a specific cell G9:G1200 I have to type-specific words like;

    Checked, Audited, Authorization Needed, Call for Report, Completed so on. If I type a wrong word or in the wrong format then it gives an unexpected result.


    Now can it be Possible that If I select any cell in between G9:G1200 then a list box pops up? There will be my list, if I clicked on the selected item of the list it will unload on that cell and the list box will disappear.


    Hope it is possible.


    Thanks in Advance.

  • Make a list of entries that are allowed somewhere (can be on a separate sheet), then used data validation on all cells in column G. The data validation needs to be set to "List" and the list source would be the list you created.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks, Kjbox for the feedback.


    I do not want to use the data validation list format. Because the front of data validation is so small for large sentences.

    It does not open or show when the cell is selected,

    Mouse scroll or keyboard scroll is not possible.


    If it is possible doing it by list box then I think it will be the faster way of data entry.


    Hope you will help me. I really appreciate the work you do.


    Thanks Again.

  • Hi Mamun,


    You can try the approach in the attachment: Book1.xlsm


    I am not sure if you know how to navigate the code?


    there is code on the worksheet:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("G9:G1200")) Is Nothing Then
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then GoTo Exit_Sub
    UserForm1.Show
    End If
    Exit_Sub:
    End Sub

    Modify the range G9:G1200 as required.

    the this code attached to the userform:



    Just create your list of terms, insert it as a table (with header) and name it then change ws = sheet1 to refer to whichever sheet your table is one, and the Table1 to be whatever you name your table and it should work in whatever context you need.


    Let me know how it goes.

    JD

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!