martes, febrero 21, 2012

Validación de datos en varias hojas – código mejorado

Gracias al comentario del amigo Juan Munevar en mi nota anterior, vimos que el código para evitar duplicados a través de varias hojas tiene un inconveniente: si la propiedad "mover después de presionar Entrar" está activada el código puede borrar el contenido de la celda equivocada.



Para que el código de la anterior funcione, la propiedad "mover después de presionar Entrar" debe estar desactivada



Como no podemos saber de antemano cuál es la definición del cuaderno debemos escribir un código para cubra todas las posibilidades. Lo que debemos tomar en cuenta es el orden de los eventos cuando apretamos Entrar.
El código evalúa el valor de la celda que era la celda activa al disparar el evento (Target). Es decir, si la opción de mover la selección después de apretar Entrar no está activada, borramos el contenido de la celda activa que es la misma que Target; si se mueve a la izquierda Target se encuentra a la derecha de la celda activa; si se mueve hacia abajo, Target se encuentra arriba; si se mueve hacia arriba, Target es la celda inmediata inferior.
Un caso particular es si la selección se mueve a la izquierda. En este caso, si la celda evaluada está en la columna A, Target coincide con la celda activa ya que Excel no puede seleccionar una celda que no existe.

En definitiva, el código es el siguiente:

Sub valid_accross_sheets(valValue)
   
    Dim iValCalc As Integer
   
    iValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _
                WorksheetFunction.CountIf(Range("Lista2"), valValue) + _
                 WorksheetFunction.CountIf(Range("Lista3"), valValue)
   
   
    If iValCalc > 1 Then
        MsgBox "El valor " & ActiveCell.Value & " ya existe"
        Select Case Application.MoveAfterReturn
            Case Is = False
                ActiveCell.ClearContents
            Case Else
                Select Case Application.MoveAfterReturnDirection
                    Case Is = xlDown
                        ActiveCell.Offset(-1, 0).ClearContents
                    Case Is = xlUp
                         ActiveCell.Offset(1, 0).ClearContents
                    Case Is = xlToRight
                        ActiveCell.Offset(0, -1).ClearContents
                    Case Is = xlToLeft
                        If ActiveCell.Column = 1 Then
                            ActiveCell.ClearContents
                        Else
                            ActiveCell.Offset(0, 1).ClearContents
                        End If
                    End Select
        End Select
                   
    End If
 
End Sub


El cuaderno puede descargarse aquí.

4 comentarios:

  1. Estimado:

    Era suficiente con modificar el parámetro al tipo Range y modificar el código como sigue:

    Sub valid_accross_sheets(valValue As Range)

    Dim iValCalc As Integer

    iValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue.Value) + _
    WorksheetFunction.CountIf(Range("Lista2"), valValue.Value) + _
    WorksheetFunction.CountIf(Range("Lista3"), valValue.Value)

    If iValCalc > 1 Then
    MsgBox "El valor " & valValue.Value & " ya existe"
    valValue.ClearContents
    valValue.Select
    End If

    End Sub

    Saludos:

    ResponderBorrar
  2. Ciertamente, mucho más elegante. Gracias por el aporte.

    ResponderBorrar
  3. Buen dia Jorge.
    mi consulta puede resultar un poco extraña, especialmente conociendo bastante a fondo vba; pero lo que preciso saber es is existe alguna manera o funcion, que permita, desde excel, conocer el valor de la celda activa, y por ejemplo : mostrarlo en otra celda. De modo que a medida que vaya cambiando mi posicion en la hoja (cambiando la celda activa), el valor en una cierta celda (donde estuviera dicha funcion), mostrara el valor de la celda actual.
    Espero haber sido claro.
    con macros, es muy sencillo de resolver, pero quisiera saber si es posible hacerlo sin recurrir a macros.
    saludos y gracias por tu inmenso aporte.

    ResponderBorrar
  4. Espero haber entendido. No, sólo podrías hacerlo programando un evento.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.