X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Data Validation In Next Cell Only If First Cell Has Specific Text, Office 2010    
post May 5 2019, 01:26 PM

Posts: 1,300
Joined: 17-November 13
From: Orewa New Zealand

I am trying to use a named range for data validation only if the previous cell contains the word "Kiwi Saver"
So it would start off like this
If Range("F" & Target.Row).Value = "Kiwi Saver" then
I am not sure what to put next the named range for the list is called "KiwiSaver"
Go to the top of the page
post May 6 2019, 09:40 AM

Posts: 2,402
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. This one was a little tricky and I am still a little confused on exactly what you are looking for, but I'll give it a try.
  2. The way I interpret your Request is as follows:
    Find a Value within a specified Named Range (KiwiSaver) only if the Value in the Previous Cell is 'Kiwi Saver'.
  3. That being said, make the following Declaration in the General Declarations Event of your Worksheet:
    Dim rngPreviousCell As Excel.Range
  4. Place the following Code in the Selection_Change() Event of your Worksheet:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Does the Text conTEXT_To_FIND exist within the Range KiwiSaver?
    'Valid only if the entry in the Previous Cell is Kiwi Saver
    On Error Resume Next
    Dim rng As Excel.Range
    Dim blnFound As Boolean

    '********** SIMULATE TEXT TO FIND **********
    Const conTEXT_To_FIND = "Help me please!"

    If rngPreviousCell Is Nothing Then
      Set rngPreviousCell = Target
        Exit Sub
    End If

    If UCase(Range(rngPreviousCell.Address).Value) = "KIWI SAVER" Then
      For Each rng In Range("KiwiSaver")
        If UCase(rng.Value) = UCase(conTEXT_To_FIND) Then
          blnFound = True
            Exit For
        End If
    End If

    If blnFound Then MsgBox conTEXT_To_FIND & " was found in Range " & Range("KiwiSaver").Address

    'Debugging only
    'MsgBox "New cell is " & Target.Address & vbCrLf & "Old cell was " & Range(rngPreviousCell.Address).Value

    Set rngPreviousCell = Target
    End Sub
  5. The Code, with MINIMAL TESTING appears to work quite well.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    25th June 2019 - 11:09 PM