Solved

VB help

Posted on 2011-02-21
1
768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: 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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

729 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