Posts by Tyrlaan

On Error Resume Next worked like a charm, thank you!

(did not try SendKeys because this one solved the issue)

Have not tried either route. I'll give both a shot and report back!

Hi folks,
Really struggling with this one and hoping someone has ideas on how to do this or what I'm missing.

I'm trying to code a series of checks when values on a worksheet change, and based on what changed do other things. I've got that part down fine until a delete key is pressed. From research, I understand the problem is that the deletion is a merged cell, which trips up the code.

I've seen some writeups that suggest you need to go through each cell to bypass the delete error, but I am trying to minimize how much code I have to write. For example, I do the same action if any one of four merged cell ranges changes to "Yes":

[VBA]If Not Intersect(Target, Range("RTIndRTO")) Is Nothing Or Not Intersect(Target, Range("RTentRTO")) Is Nothing Or Not Intersect(Target, Range("RTRedRTO")) Is Nothing Or Not Intersect(Target, Range("RTVndRTO")) Is Nothing Then)[/VBA]

This works fine, until the dreaded delete hits. So I've been trying to intercept it with an Application.OnKey "{Delete}", "SubName" line, but results have been inconsistent and certainly not doing what I'm trying to do.

If there's another way to capture the delete key and bypass the rest of my code in the worksheet_change sub that would be ideal, but I'm not aware of a way to do that.

Any advice would be greatly appreciated! I will have several compound IF statements like the one above, so an efficient way to dodge the delete error when applied to a merged cell is pretty necessary. If nothing else, if I can just disable deleting as a last resort, I'd do that.

Thanks in advance!

Hi all,
Since as far as I can tell what I was trying to do is just not possible, I took a different route to solve my problem.

For any folks that are curious, I instead built out a sub that reacts to any change in the range of cells that constitute the first validated entry (ie the driver for this validation) and then adds the appropriate validation based on that through simple if statements.

Hi all,
First time poster, long time lurker! Really struggling with this and can't seem to find an answer anywhere. Hoping someone here has a solution for me.

I'm trying to set Data Validation for a cell via vba. The cell needs to exist on each row of data the rest of my code generates, so I can't hardcode the cell referencing. Also, this needs to be a dynamic validation, so I'm trying to use an indirect formula to drive things. Here's the sub I'm using:

Sub CreateDetailDD(r As Range)
    Dim cTarget As String
    Dim FormStr As String


    cTarget = Cells(r.Row, r.Column + 3).Address(0, 0)
    FormStr = "=INDIRECT(" & cTarget & ")"
    With r.Validation
        .Add Type:=3, AlertStyle:=1, Formula1:=FormStr
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

When I try to run it, I get a run-time 1004 error on the line adding Formula1 etc.

Is it possible to do what I'm trying to do, and if so, how do I do it?

Thanks in advance!