Solved

VB help

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

910 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

22 Experts available now in Live!

Get 1:1 Help Now