+ Reply to Thread
Results 1 to 6 of 6

Cell Update on Data Validation Selection

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Cell Update on Data Validation Selection

    I'm using a DV List (in H100) to allow the user to select various options. If one particular option is selected from the DV List, I need to dispaly text in cell H98, which I'm doing using this code:

    If Range("$H$100").Value = "Total" Then
        Range("$H$98").Value = "£'s Thousands"
    Else
        Range("$H$98").ClearContents
    End If
    However, cell H98 won't show the text until cell H100 is not selected, i.e. until another cell is clicked. The same applies to clearing the cell as well.

    Question
    How can I get my text to appear as in H98 as soon as the relevant option is selected from the DV List without the user having to click another cell?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Cell Update on Data Validation Selection

    Maybe...

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$H$100" Then Exit Sub
        If Target.Value = "Total" Then
            Range("$H$98").Value = "£'s Thousands"
        Else
            Range("$H$98").ClearContents
        End If
    End Sub
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Cell Update on Data Validation Selection

    Hi Jeff,

    This works perfectly, though I need to extend the code across ten columns as there is a DV List in each column and the value "Total" can be selected from any of them. I tried to extend your code to allow this to work for each column, what am I doing wrong? (I've only tried to add the second column below).

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$H$100" Then Exit Sub
        If Target.Value = "Total" Then
            Range("$H$98").Value = "£'s Thousands"
        Else
            Range("$H$98").ClearContents
        End If
        
        If Target.Address <> "$I$100" Then Exit Sub
        If Target.Value = "Total" Then
            Range("$I$98").Value = "£'s Thousands"
        Else
            Range("$I$98").ClearContents
        End If
    End Sub

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Cell Update on Data Validation Selection

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rng As Range
        Set Rng = Target.Parent.Range("H100:I100")
        If Target.Count > 1 Then Exit Sub
        If Intersect(Target, Rng) Is Nothing Then Exit Sub
        If Target.Value = "Total" Then
            Target.Offset(-2).Value = "£'s Thousands"
        Else
            Target.Offset(-2).ClearContents
        End If
    End Sub

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Cell Update on Data Validation Selection

    Jeff,

    That works perfectly, many thanks once again for your help...

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Cell Update on Data Validation Selection

    You are most welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1