Solved

VB help

Posted on 2011-02-21
1
767 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

749 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