Conditional Data Val Input Message

[Kevin.W.S]

I'm trying to find a way to have the Input Message (from the Data Validation tool) of cell A2 display a message depending on the content of the A1. A1, also data validated, has five possible values and I want that to trigger five different input messages for A2.

The purpose is to guide users through the worksheet and make sure multiple users are using the same syntax throughout.

The overall document is meant to keep records of actions performed on scientific samples in my lab, with "A1" corresponding to a procedure type and "A2" being details of the procedure, but whether that is a volume, percentage, outcome or source depends on the procedure type. I hope that makes sense.

I've spent all day reading up on VBA but I get the feeling I'm a mile away from figuring this out so I'd appreciate someone to get me in the ballpark so I can work from there. Thanks in advance.

 

[Kenneth Hobson]

What is your data validation formula? Give an example with data.

 

[Kevin.W.S]

A1 is just doing a data validation against a list of five strings like "Injection" "Sample_Collection" or "Assay"

For A2, some of the procedure types have no true validation (just need it for the input message) while others are purposes for a sample "Histology" "DNA" "RNA" "Assay" or the type of assay to be run "Phe" "PAH" "qPCR"

Since I'm removing the error message I'm leaving it open for users to write other inputs so that it's more likely to catch on, that's why I want an input message to direct the user like "write the purpose of the sample" without forcing the user through a pop-up data input window.

 

[Kevin.W.S]

I just made a macro that can do what I'm looking for, so now my problem should be relatively simple: I have no idea how to make a macro like this a perpetual rule on the spreadsheet.

Since this will be running on hundreds of different cell pairs perhaps there is a way to make it a rule that runs only when the first cell changes in content? Any help would be greatly appreciated.

Sub DataVal2()
'
' DataVal2 Macro
'
    If ActiveSheet.Range("D5").Value = "Test1" Then
        ActiveSheet.Range("E5").Validation.InputMessage = "Test1"
    ElseIf ActiveSheet.Range("D5").Value = "Test2" Then
        ActiveSheet.Range("E5").Validation.InputMessage = "Test2"
    ElseIf ActiveSheet.Range("D5").Value = "Test3" Then
        ActiveSheet.Range("E5").Validation.InputMessage = "Test3"
    Else
        ActiveSheet.Range("E5").Validation.InputMessage = "Error in D5"
    End If
    
        
    
'
End Sub

 

[Kevin.W.S]

I hate to bump, but I feel like I'm super close to the end of this problem and most users know how to do this last part I'm asking. Can someone give me a hand?

 

[mikerickson]

Have you tried putting that code in a Change event?

 

[Kevin.W.S]

Have you tried putting that code in a Change event?

Here comes my ignorance: I just got into VBA about 48 hours ago, I have no idea what a Change event is. I don't need you to explain it fully for me, if you could just point me in the direction of where/what I should read that would be plenty helpful

 

[mikerickson]

If you open a sheet's code module, selection from the dropdowns will give you an empty Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

You can fill it with whatever code you like.
It will run whenever a cell on that worksheet is changed via direct user action. The changing of a cell because of a formula will NOT trigger a change event.

Target is the system variable that is the range of the cells that have been changed.

Try this, put =A1+1 in B1. Then put this code in the sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Address
End Sub

Fiddle about, copy a range of cells and paste them into the sheet, to get a feel for what triggers a Change event. Note, that if you change A1, B1 will change (because the formula will have a new result) but B1 will not be in Target.

For your purpose, you could use this change event

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$D$5" Then
            Rem if D5 is changed do the routine, else do nothing
            
            Select Case CStr(.Value)
                Case "Test1"
                    .Offset(0,1).Validation.InputMessage = "Test1"
                Case "Test2"
                    .Offset(0,1).Validation.InputMessage = "Test2"
            End Select
        End If
    End With
End Sub

 

[Kevin.W.S]

Thanks Mike! That seems to be working great in my sandbox (I had to switch from Selection Change to Change to make it work). Now just to apply it to the real deal.

Thanks again