Announcement

Collapse
No announcement yet.

Display data on user form

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Display data on user form

    Hello guys,

    Here to ask for a simple code on how can i display information on a user form once entered from an input box.

    Example

    A B C
    1 E12858 Jorge Stregan
    2 E112859 Rose Ann

    Result:

    Display all data in a row in userform once any data in A1 entered in input box.

    Thanks.

  • #2
    See this very quick example....

    Hope it helps - the values in the two text boxes change as you tab out of the combobox...
    Attached Files
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Hello WillR,

      Thanks for the code WillR. It works perfectly, but what if i want to display the data in a label not in textbox. i tried changing the code (textbox to label) but it didnt work. Its just a minor tweak but i cant figure it out.

      Comment


      • #4
        Hi rExgiE

        To use a Label you would use code like;


        Label1.Caption="Some Text"

        Replacing Label1 with the name of your label.

        Comment


        • #5
          Thanks Dave,

          Code is running well, instead of using combo box i changed it to textbox so i can enter data data manually. clicking a command button will search and display all data related to that on a labels. i used FIND but i find it confusing: can you enlighten me with this code.

          sample data

          A1 E12345
          B1 Jorge Estregan
          C1 14 Street Policarpio

          Result: If E12345 entered in text box, displays B1 and C1 in labels

          Thanks again in advance

          Comment


          • #6
            Try this. You will need a Textbox to enter your search, and labels for the results.The code will be triggered by the CommandButton. It searches the whole sheet



            Code:
            Private Sub CommandButton1_Click()
            
            Dim fnd As Range ' this will be the samew as your entry in TextBox1
            Dim tbl As Range ' this will be the table to search
            
            Set tbl = Sheet1.Range("A2").CurrentRegion
            
            'run the search
            Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                    , SearchFormat:=False)
            'if the item is not found the user is informed and the textbox cleared
              If fnd Is Nothing Then
              MsgBox "No match found!"
              TextBox1.Value = ""
              
              Exit Sub
            'if the item is found the details will be copied to the labels
              Else: fnd.Activate
              End If
            
                 'now put the information stored adjacent to the found item into label1,etc
            Label1.Caption = fnd.Offset(0, 1).Value
            Label2.Caption = fnd.Offset(0, 2).Value 'and so on
            End Sub
            Attached Files
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Thanks Roy,

              I tried the attached file you send, but there were error on this part,

              Code:
              Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                   , SearchFormat:=False)

              The error pointed to the last part ", SearchFormat:=False)" which says "named argument not found".

              Im using Excel 97.

              Please advise.

              Thanks.
              Last edited by Barbarr; October 26th, 2005, 00:09. Reason: Add code tags

              Comment


              • #8
                I will check it out. I am using Exel 2002, aand it worked before. Will post back later
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment


                • #9
                  Re: Display data on user form

                  how is it done to display all results if there are more same names for instance and i want to see all of them???

                  Comment


                  • #10
                    Re: Display data on user form

                    Saban

                    Welcome to Ozgrid. Would you please start a new thread for your uestion & be a little more specific on what you want. Place a link to this thread Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser.
                    Hope that Helps

                    Roy

                    New users should read the Forum Rules before posting

                    For free Excel tools & articles visit my web site

                    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                    RoyUK's Web Site

                    royUK's Database Form

                    Where to paste code from the Forum

                    About me.

                    Comment


                    • #11
                      Re: Display data on user form

                      sorr y for that but where can i post a new thread is there any link for posting a new thread I mean it sure is i just cant find it
                      my question was about the same example as u described in this thread except i want names that are repeating also printed on label
                      luka 1234
                      luka 2345 "i want these two to be shown on labels or three or four depends on how many do i have in my excel sheet with same name"
                      dont bother to much just tell me how can i post a new thread
                      and sorry for my stupidity wont happen again

                      Comment


                      • #12
                        Re: Display data on user form

                        Use the New Thread button. Close this Thread & you will see this button owards the top right
                        Hope that Helps

                        Roy

                        New users should read the Forum Rules before posting

                        For free Excel tools & articles visit my web site

                        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                        RoyUK's Web Site

                        royUK's Database Form

                        Where to paste code from the Forum

                        About me.

                        Comment


                        • #13
                          Re: Display data on user form

                          I have added code tags for you in this post.Please read the rules you agreed to particularly concerning Code tags & their use. Thanks
                          Hope that Helps

                          Roy

                          New users should read the Forum Rules before posting

                          For free Excel tools & articles visit my web site

                          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                          RoyUK's Web Site

                          royUK's Database Form

                          Where to paste code from the Forum

                          About me.

                          Comment


                          • #14
                            Display data on user form

                            hi I need help
                            I have excel sheet with 9 columns from a to I
                            column a = supplier
                            column b = items
                            column c = warehouse
                            column d = invoice no
                            column e = receive date
                            column f = due date
                            column g = items cost
                            column h = items value
                            I designed userform to search data
                            as when I write supplier name in combobox
                            it display the search result for other column
                            in textbox
                            combobox named supplier
                            textbox 1 named items to display items of supplier
                            textbox2 named warehouse to display warehouse
                            textbox3 named invoiceno to display invoice no
                            textbox4 named rdate to display receive date
                            textbox 5 named ddate to display due date
                            textbox 6 named cost to display items cost
                            textbox 7 named itemv to display items value
                            I had the following code but I have error 91 object variable when the supplier name not found in the column a
                            Private Sub searchsupp_Click()
                            Dim ws As Worksheet
                            Set ws = Worksheets("sheet1")
                            Dim cell As Range
                            With Range("a2", Range("a1000").End(xlUp))
                            Set cell = .Find(supplier.Value, LookIn:=xlValues)
                            If supplier.Value = "" Then
                            MsgBox "Enter Supplier Name"
                            Unload Me
                            End If

                            items.Text = cell.Offset(0, 1).Value
                            warehouse.Text = cell.Offset(0, 2).Value
                            invoiceno.Text = cell.Offset(0, 3).Value
                            rdate.Text = cell.Offset(0, 4).Value
                            ddate.Text = cell.Offset(0, 5).Value
                            cost.Text = cell.Offset(0, 6).Value
                            itemv.Text = cell.Offset(0, 7).Value


                            If cell Is Nothing Then

                            MsgBox "not found"
                            End If

                            End With

                            End Sub

                            Comment


                            • #15
                              Re: Display data on user form

                              The first thing that you need to do is to actually read the Forum Rules.

                              Do not post your question in another member's thread.

                              Wrap your code with code tags.
                              Hope that Helps

                              Roy

                              New users should read the Forum Rules before posting

                              For free Excel tools & articles visit my web site

                              If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                              RoyUK's Web Site

                              royUK's Database Form

                              Where to paste code from the Forum

                              About me.

                              Comment

                              Working...
                              X