help with multipage box

dulitul

New member
Joined
Dec 13, 2012
Messages
16
Reaction score
0
Points
0
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..
 

Attachments

  • data123.xls
    334.5 KB · Views: 24
Last edited:

Tangjoc

New member
Joined
Jan 9, 2013
Messages
11
Reaction score
0
Points
0
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.
 

Attachments

  • data123.xls
    338.5 KB · Views: 15

dulitul

New member
Joined
Dec 13, 2012
Messages
16
Reaction score
0
Points
0
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
 

dulitul

New member
Joined
Dec 13, 2012
Messages
16
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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. :)
 

dulitul

New member
Joined
Dec 13, 2012
Messages
16
Reaction score
0
Points
0
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
 
Top