Solved

VB help

Posted on 2011-02-21
1
762 Views
Last Modified: 2012-05-11
Please can you help with the code for TextBox4 on my worksheet.

When the location has been selected in TextBox1, I would like for Textbox4 to bring back the corresponding 'code' in sheet6("Datasheet") column E

An example would be that Algeria would equal "4", basically I would like textbox4 to work like a V-lookup does when trying to search for the corresponding value.

Many thanks for your help,

Gary

help.JPG
gary-test4.xls
0
Comment
Question by:Gazza83
1 Comment
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 34948082
Gary,

Modified CheckIfComplete sub below

Cheers

Dave
'If they are all non-blank, enable the add button
Private Sub CheckIfComplete()
    Dim boolCheck As Boolean
    Dim rng1 As Range

    boolCheck = False
    ComboBox3.Visible = (ComboBox2.Value = "No")
    Label30.Visible = (ComboBox2.Value = "No")

    If txtcustomersname <> "" And txtCustomerusername <> "" And TextBox1 <> "" _
       And txtrecieveddate <> "" And txtstarteddate <> "" And txtcompleteddate <> "" _
       And ComboBox1 <> "" And ComboBox2 <> "" Then
        For i = 0 To ListBox1.ListCount - 1
            If Trim(TextBox1.Text) = Trim(ListBox1.List(i)) Then
                boolCheck = True
                Exit For
            End If
        Next i

        If boolCheck = False Then
            cmdPopulateRow.Enabled = False
            Exit Sub
        End If

        If ComboBox2.Value = "No" Then
            If Len(ComboBox3.Value) > 0 Then
                cmdPopulateRow.Enabled = True
            Else
                cmdPopulateRow.Enabled = False
            End If
        Else
            cmdPopulateRow.Enabled = True
        End If
    Else
        cmdPopulateRow.Enabled = False
    End If
    Set rng1 = Sheets("DataSheet").Range("D1:D300").Find(TextBox1.Value, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then TextBox4.Value = rng1.Offset(0, 1)

End Sub

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now