Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup.... in textbox (2000 sr 1)

    i have a useform with textbox25 and textbox4.
    Is possible to make a vlooupk and return value from textbox25 result into textbox4?
    Example:
    The table of vlookup are into sheet TABELLA with this structure:
    Col Q from Q2 to Q9 are present
    1
    2
    3
    4
    5
    6
    7
    8

    near column Q into col R from R2 to R9 are present

    ACCERTAMENTI IN CORSO
    DA SISTEMARE A CURA INPS
    DEFINITA CON ACCREDITO
    DEFINITA CON VAR. ANAGR.
    NON RECUPERABILE
    PRATICA A LEGALE
    RECUPERO RATEALE
    SISTEMATA A CURA INPS

    If i insert into textbox25 "1" insert into textbox4 ACCERTAMENTI IN CORSO, If i insert into textbox25 "3" insert into textbox4 DEFINITA CON ACCREDITO, ecc...

    or inested to use a textbox4 for entry use a combobox with a list.....?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    You can use code like this:

    Private Sub TextBox25_Change()
    Dim CODICE As Integer
    Select Case Me.TextBox25
    Case ""
    Me.TextBox4 = ""
    Case 1 To 8
    CODICE = Val(Me.TextBox25)
    Me.TextBox4 = Application.WorksheetFunction.VLookup _
    (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False)
    Case Else
    Call MULTI_LINE_BOX
    End Select
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    GOOD!
    To understand...
    Why you have used Me.textboxyy inested MODIFICHE.textboxyy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    In the module belonging to a userform, you can refer to the userform as Me. This is independent of the name of the userform, so it will still work if you rename the userform.

    Similarly, in the code belonging to a worksheet, you can refer to the worksheet as Me, and in the ThisWorkbook module, you can refer to the workbook as Me.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    TKS...

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    My friend...
    How to make this vlooukp if the sheet is into a wbook on a dir server similar?
    my servernamemydirmywbook.xlsWorksheets("TABELLA")... .

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    You will have to open the other workbook, and change the line with VLOOKUP to

    Me.TextBox4 = Application.WorksheetFunction.VLookup _
    (CODICE, Workbooks("Mywbook.xls").Worksheets("TABELLA").Ran ge("Q2:R9"), 2, False)

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    hummmm.. but i dont want to open the wbook, i consider that similar a db table...
    Not i ossible to read the value without to open it?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    VBA cannot read cells in closed workbooks, but formulas can. You could put a (hidden) worksheet TABELLA in your workbook with formulas that link to the TABELLA sheet in the workbook on the network, for example in cell A1:
    <code>
    ='my servernamemydir[mywbook.xls]TABELLA'!A1
    </code>
    The code can then remain the same as it was originally.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    GOOD! i not have think on this solution, tks!

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    ... but new scenario:
    Inested column Q and R in excel i have created a mdb into:

    my servermyserverdirUSER.MDB

    and into this mdb have inserte a table USER_NAME with:

    Field1(with the same data of column R)
    Field2(with the same data of column Q)

    is possible now to make a vlookup?

    EXAMPLE:
    Filed1 Field2
    OI14006 PPPPPPPPPP
    OI15535 GGGGGGGGGGGG
    OI15795 HHHHHHHHHHHHH
    OI16135 HDSFDDFDFD
    OI16696 DFFDFDFDSFD
    OI16780 EREREWERERWER
    OI16821 AAAADSDASDAD

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    Why make it so complicated?

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    ... i distibut the same wboook to many user

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    You don't need a database table for that.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup.... in textbox (2000 sr 1)

    hi Hans...
    In other case i have (as usual cut and paste a milino of line of code), and now have this:
    Sub LOOKUP_ACCESS()

    Dim objDBEngine As Object
    Dim DB As Database
    Dim objRecordset As Recordset
    Dim strYourDB As String, strQuery As String
    Dim intNrRecords As Integer, intNrCurRecords
    Dim l As Long, l2 As Long
    Dim rTarget As Range


    strYourDB = "C:user.mdb"


    Set DB = Workspaces(0).OpenDatabase(strYourDB)

    strQuery = "select field2" & _
    "from user_name " & _
    "where field2 = 'OI14006'"


    Set objRecordset = DB.OpenRecordset = "select field1" & _
    "from field1where Lastname = 'oi14006'"

    intNrRecords = objRecordset.RecordCount


    Set rTarget = ThisWorkbook.Worksheets(1).Range("A1")
    If Not objRecordset.EOF Then
    objRecordset.MoveFirst
    For l = 1 To objRecordset.RecordCount
    For l2 = 1 To objRecordset.Fields.Count - 1
    rTarget.Offset(l - 1, l2).Value = objRecordset.Fields(l2).Value
    Next l2
    objRecordset.MoveNext
    Next l
    End If


    Set objRecordset = Nothing
    Set DB = Nothing
    Set objDBEngine = Nothing
    End Sub

    for test use the sheet attached and the new mdb

Page 1 of 2 12 LastLast

Posting Permissions

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