Results 1 to 6 of 6

Thread: help with multipage box

  1. #1

    help with multipage box



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey guys,


    I need urgent help with the following multipage box. I downloaded a sample code which does the following:

    When I enter the supplier's name in the first field the rest of the data apears in the other boxes (address etc). I did sth and now it does not appear. Can you help me?

    2) in the first field (supplier's name from the control box) I would like to add a find dialogbox (the one in excel ctrl+f) to be able to search more efficiently, since currently it searches by first letter. It should pop up automatically by clicking in the field.

    3) I want to add a 2th column in the control box where I should be able to write a new vendor's name (and their details) which has to be automatically added in the list (in case the vendor is not in the list).

    Pls help..
    Attached Files Attached Files
    Last edited by dulitul; 2013-01-09 at 02:02 PM.

  2. #2
    Hi I've made some change to your woorbook.
    I'm not that good.
    I don't know if it will help, but I think it could be a start.

    Change 1) = start the macro with a bouton
    Change 2) = On combobox_change Textbox1; 2; 3; = x
    Now if you like to make some change and save Texbox(1;2 or 3)
    On Texbox_change save

    Like I said. I'm not that great but it's a start.
    I hope it can help.
    Attached Files Attached Files

  3. #3
    Hey,


    Thanks for your efforts. But I would like to:


    1) As I said I want to have a genuine search option (ctrl + f in excel), since it searches on first letter currently.

    2) If I want to add a new vendor to the list I need to have a special option where I have to include the new details and they should automatically be added to the excel list.

    Btw I didnt quite get your point about text_box save change? Could you clarify it?


    Thanks

  4. #4
    Plus - I need to be able to copy the data when I click on it. Currently, If I click on textbox 2 or 3 it immediately disappears.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,243
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there,

    Tanjoc, with regards to your comment about not being good... we all get better by helping others. Keep it up and learn from others who chip in too.

    Dulitul,

    Remove everything in the useform code and replace it with this:
    Code:
    Option Explicit
    Private Sub ComboBox1_Change()
        Dim lVendor As Long
        Dim tblLookup As Range
        
        'Find the range that has the supplier names in it
        With Worksheets("List_CWS")
            Set tblLookup = .Range("B1:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
        End With
        
        'Extract the index position of the vendor
        lVendor = Application.WorksheetFunction.Match(ComboBox1.Value, tblLookup, False)
        
        'Fill the supplier number
        UserForm1.TextBox1.Value = Application.WorksheetFunction.Index(tblLookup.Offset(0, -1), lVendor)
        
        'Fill the address
        UserForm1.TextBox2.Value = Application.WorksheetFunction.Index(tblLookup.Offset(0, 1), lVendor) & vbNewLine & _
            Application.WorksheetFunction.Index(tblLookup.Offset(0, 2), lVendor)
        
        'Fill the bank
        UserForm1.TextBox3.Value = Application.WorksheetFunction.Index(tblLookup.Offset(0, 2), lVendor)
        
            
    End Sub
    
    Private Sub UserForm_Initialize()
        Dim rngData As Range
        Dim cl As Range
        
        With Worksheets("List_CWS")
            Set rngData = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
        End With
        
        With ComboBox1
            For Each cl In rngData
                .AddItem cl.Value
            Next cl
        End With
    End Sub
    That should get you started.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Hey Ken,


    When I run this code, it appears - run-time 424 error object required in both subs procedures.


    I think I fixed the old code since now its working but I want to try out yours too.

    I was wondering if its possible at all to create a button in the menu which can run a special "find" function. I mean that when I type for instance "golar" I want to have all matches to appear in a menu within the main menu. It should be the same as the general find function, but I dont wanna click next until I find all of them having the match creteria.

    I would to be able to type in some creteria under the find button and then all matches from the excel list should appear in a new window.

    Is that possible?



    2) I was wondering if I can connect excel to SAP could that be go through ODBC server? I want to transfer the supplier's database on an Excel sheet and when I change certain data in SAP's database this should be reflected in my excel sheet automatically..I know that's possible with SQL but how about sap?

    Please help.

    Thanks

Posting Permissions

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