manejo basico de formularios desde vba

Un gran lector de mi blog y de todas las entradas que se reflejan en Facebook, Jose Quetz Ku, me consulta sobre como manejar datos desde un formulario, para colocarlos en una hoja de cálculos. Esto se realiza integramente desde VBA, y nos ofrece una gran solución cuando debemos restringir la cantidad y tipos de datos, por ejemplo, en una tabla. Como veremos a continuación, "poner" datos en Excel desde un formulario es muy sencillo, pero la cuestión se complica un poco al controlar la entrada de dichos datos.

Diseñé un pequeño proyecto para dar de alta y eliminar películas en un videoclub. Arranca de un nivel básico y, seguramente, podríamos ir complicando las cosas, ítem que dejaré pendiente de acuerdo a las consultas y necesidades de mis lectores.

uno de los dos formularios del proyecto: visualizar la lista de películas existentes, junto al agregado de colocar una imagen de la misma en el formulario.

[+/-] Ver el resto / Ocultar

Estamos frente a un proyecto de mediana envergadura. Si bien aquí solo veremos muy pocos aspectos y funcionalidades, esto se puede extender a límites verdaderamente "enormes", incluyendo hasta la facturación del alquiler de cada película. Solo trataré como dar de alta una película y luego mostrarla en el formulario que arriba coloqué, mismo que también nos permitirá eliminar un registro de la tabla. No lo hago mas extenso para no complicarla, aunque como ya mencioné, podemos ir agregando cosas con el tiempo.

Antes de codificar... primero vamos "al papel", armando un bosquejo de lo que deberá hacer nuestro programa:

1) Dar de alta la película: la interfaz gráfica será mediante un formulario. Deberemos controlar que todos los datos estén bien ingresados y, antes de dar de alta, verificar que el código de película no se encuentre duplicado. Si algún dato es erróneo, falta o bien el código que intentamos asignar ya existe, la operación debe ser cancelada.

2) Colocar esos datos en una hoja de cálculos, limpiando luego los controles del formulario.

3) Mostrar la lista de todas las películas. Si tienen una imagen asociada, reflejarla en el formulario. Aquí también se podrá seleccionar un registro y eliminarlo. Si hacemos doble click sobre algún elemento de la lista, un mensaje nos mostrará los detalles faltantes.

al hacer doble click el programa me informa el género y si la película está en HD o no.

Creamos la interfaz, compuesta por un formulario con 5 controles: dos cuadros de texto (código, nombre) un combobox (género), un checkbox (si es o nó HD) y un botón de comando para aceptar y así colocar esos datos en la planilla:

El código deberá ser numérico, el nombre puede ser alfanumérico, para el género damos una ventana desplegable y si es o no HD, un control para tildar o destildar dicha opción.

Si algún dato está mal ingresado, el respectivo mensaje nos alertará de ello:

si doy "Aceptar" y falta algún campo.... el programa no me deja seguir. En el ejemplo de arriba, omití el ingreso del campo "Código"

Si aún continúo equivocándome y en dicho campo pongo datos que no son numéricos, la macro también tendrá que alertarme y cancelar la acción:

hasta aquí todo funciona correctamente.

Una vez que los datos están completos y bien integrados, los coloco en la hoja "peliculas":

ahora sí GRAN TORINO pasó a ser parte de la tabla.

Lo arriba mostrado se logra con el siguiente código:

Private Sub cmdAceptar_Click()
Dim Fila As Long

'realizo los controles de rutina:
If Trim(txtCodigo.Text) = "" Then
    Alerta 1, txtCodigo
ElseIf Trim(txtNombre.Text) = "" Then
    Alerta 1, txtNombre
ElseIf Trim(cboGenero.Text) = "" Then
    Alerta 1, cboGenero
ElseIf Not IsNumeric(txtCodigo.Text) Then
    Alerta 2, cboGenero
ElseIf BuscarDuplicados("peliculas", "A", txtCodigo.Text) = True Then
    'el código de película NO puede estar
    'duplicado. si existe mas de una copia
    'permitiremos que se duplique el nombre
    'de la misma, pero nunca su código:
    Alerta 3, txtCodigo
Else
    'ahora veo en donde está la ultima fila
    'ocupada: paso el nombre de la hoja y
    'la columna:
    Fila = UltimaFila("peliculas", "A")
    'le sumo 1, para poner los datos de la
    'película en la fila siguiente:
    Fila = Fila + 1
    CargarPelicula Fila
    'y borro los datos del formulario:
    BorrarDatosABM
End If
End Sub


Sub Alerta(QueMuestro As Byte, Obj As Object)
'con este sub ahorro bastante código, ya que lo escribo
'una sola vez y luego lo llamo, cada vez que lo necesito.
'paso como segundo argumento un objeto que soporta el
'método SetFocus, para luego devolverle el "foco" (cursor)
'y así indicar donde está el error:
Select Case QueMuestro
    Case 1
        MsgBox "Falta completar informacion", vbExclamation, "Videos"
        Obj.SetFocus
    Case 2
        MsgBox "El dato debe ser numérico", vbCritical, "Videos"
        Obj.SetFocus
    Case 3
        MsgBox "El código de película se encuentra duplicado", vbCritical
        Obj.SetFocus
End Select
Set Obj = Nothing
End Sub

Function UltimaFila(H As String, C As String) As Long
UltimaFila = Sheets(H).Cells(65536, "A").End(xlUp).Row
End Function

Sub CargarPelicula(F As Long)
'coloco los datos del formulario en la hoja
'"películas"
With Sheets("peliculas")
    .Cells(F, "A") = txtCodigo.Text
    .Cells(F, "B") = UCase(txtNombre.Text)
    .Cells(F, "C") = cboGenero.Text
    If chkHD.Value = True Then
        .Cells(F, "D").Value = "SI"
    Else
        .Cells(F, "D").Value = "NO"
    End If
End With
End Sub
Sub BorrarDatosABM()
'aquí elimino de los controles los datos
'cargados previamente:
txtCodigo.Text = ""
txtNombre.Text = ""
cboGenero.Text = ""
chkHD.Value = False
End Sub

Function BuscarDuplicados(H As String, C As String, Valor) As Boolean
BuscarDuplicados = True
'voy a la hoja y selecciono las columnas indicadas:
Sheets(H).Select
Columns(C).Select
'busco el Valor:
On Error Resume Next
BuscarDuplicados = Selection.Find(What:=Valor, After:=ActiveCell, _
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
'si la busqueda NO tira error, es por que ese valor existe:
If Err.Number = 0 Then
    BuscarDuplicados = True
Else
    'si hay error, entonces el valor buscado NO existe:
    BuscarDuplicados = False
End If
End Function
Sub CargarGeneros(Cbo As Object)
'cargo en el combobox los distintos géneros:
Cbo.AddItem "ACCION"
Cbo.AddItem "FICCION"
Cbo.AddItem "TERROR"
Cbo.AddItem "EPICA"
Cbo.AddItem "COMEDIA"
Cbo.AddItem "DRAMA"
Set Cbo = Nothing
End Sub

Private Sub UserForm_Initialize()
'utilizo el evento Initialize del formulario
'para cargar los generos de las películas:
CargarGeneros cboGenero
End Sub
Private Sub cboGenero_KeyDown(ByVal KeyCode As MSForms.ReturnInteger _
, ByVal Shift As Integer)
'con el evento KeyDown controlo que teclas presiona el usuario.
'sobre este combobox solo podrá presionar: cursor arriba,
'cursor abajo, tabulador y enter. cualquier otra tecla será
'convertida a 0 (cero) y no tendrá ningún efecto. de esta
'forma evito que puede modificar los datos cargados en el
'control:
If KeyCode = vbKeyDown Then
    KeyCode = vbKeyDown 'cursor abajo
ElseIf KeyCode = vbKeyUp Then
    KeyCode = vbKeyUp 'cursor arriba
ElseIf KeyCode = vbKeyTab Then
    KeyCode = vbKeyTab 'tabulador
ElseIf KeyCode = vbKeyReturn Then
    KeyCode = vbKeyReturn 'enter
Else
    KeyCode = 0 'cualquier otra tecla la invalido
End If
End Sub

El código está comentado para facilitar su lectura y comprensión. De todas formas, lo mas recomendable es que bajen el ejemplo y coloquen puntos de interrupción y así realizar el "paso a paso" y ver en "cámara lenta" que hace la macro en cada línea. Intenté, en algunos casos, modularizar algunas rutinas, para que el código sea reutilizable y mas fácil de mantener y depurar.

El formulario que sigue crea una lista de todas las películas existentes, permitiendo ordenarlas por código o por nombre, para facilitar la búsqueda. Si esa película tiene una imagen asociada, la muestra en un control Image. También posee un botón que nos permite eliminar la película seleccionada:

obviamente antes de llevar a cabo tal acción... preguntamos.

Por defecto las películas están ordenadas por código. Si presiono el botón "Reordenar", pasa lo siguiente:

ahora se visualizan ordenadas por nombre. Si presionamos nuevamente dicho botón, volverá a ordenarse por código.

Analicemos el código involucrado:
Dim RutaImagen As String

Dim Reordenar As String
Private Sub cmdEliminar_Click()
'doy la posibilidad de borrar una película
Dim P As Long
'si hay algun item seleccionado:
If lstPeliculas.Text <> "" Then
    'primero pregunto
    If MsgBox("Confirma la eliminacion?", vbYesNo + vbQuestion, "Atención") = vbNo Then
        Exit Sub
    End If
    'a la posicion del item le sumo 2, para emparejarlo con la
    'fila de excel en donde está la película
    P = lstPeliculas.ListIndex + 2
    With Sheets("peliculas")
        'si tiene una imagen guardada, la elimino:
        If Dir(RutaImagen & .Cells(P, "A") & ".jpg") <> "" Then
            Kill RutaImagen & .Cells(P, "A") & ".jpg"
        End If
        'y la borro la pelicula de la hoja de cálculos:
        .Range("a" & P).EntireRow.Delete
    End With
End If
End Sub

Private Sub cmdOrdenar_Click()
Dim Fila As Long
'puedo agregar dos métodos de ordenamiento, utilizando
'el mismo boton: ordenar por codigo o nombre de peli
'cula. Cambio el nombre del botón en cada caso y lla
'mo al sub ordenador, pasando los parametros correctos:

Fila = frmABM.UltimaFila("peliculas", "A")
If Reordenar = "nombre" Then
    Reordenar = "codigo"
    OrdenarPeliculas "A2", Fila
    CargarPeliculas Fila
ElseIf Reordenar = "codigo" Then
    Reordenar = "nombre"
    OrdenarPeliculas "B2", Fila
    CargarPeliculas Fila
End If
imgPelicula.Visible = False
End Sub


Private Sub lstPeliculas_Click()
'tomo el codigo de la pelicula y la agrego la
'extención ".jpg", para buscar la imagen
'de la misma y mostrarla en el control Image:
If lstPeliculas.Text <> "" Then
    imgPelicula.Visible = True
    F = lstPeliculas.ListIndex + 2
    'controlo el posible error de que la película
    'no tenga una imagen relacionada:
    On Error Resume Next
    imgPelicula.Picture = LoadPicture(RutaImagen & _
    Sheets("peliculas").Cells(F, 1) & ".jpg")
    If Err.Number <> 0 Then
        'si el archivo jpg no  existe, limpio el
        'error y oculto el control Image
        Err.Clear
        imgPelicula.Visible = False
    End If
End If
End Sub

Private Sub lstPeliculas_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Posi As Long
If lstPeliculas.Text <> "" Then

    Posi = lstPeliculas.ListIndex + 2
    With Sheets("peliculas") 'ahora muestro los detalles:
        MsgBox "Genero: " & .Cells(Posi, "C") & vbCrLf _
        & "HD: " & .Cells(Posi, "D"), vbInformation _
        , "Detalles"
    End With
End If
End Sub

Private Sub UserForm_Initialize()
RutaImagen = ActiveWorkbook.Path & "\"
Dim Fila, X As Long
'aqui llamo a un procedimiento ya creado
'ahorrando código
Fila = frmABM.UltimaFila("peliculas", "A")
'ordeno por codigo:
OrdenarPeliculas "A2", Fila
CargarPeliculas Fila
Reordenar = "nombre"
End Sub
Sub OrdenarPeliculas(QueCampo As String, F)
'recibo dos argumentos: QueCampo que es el rango que tomaré como
'referencia para ordenar y F, que me indica donde está la útlima
'fila ocupada de la tabla
Sheets("peliculas").Select
    Range("A1").Select
    Range("A1:G" & F).Sort Key1:=Range(QueCampo), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub
Sub CargarPeliculas(F)
'primero limpio, por si hay datos en el control:
lstPeliculas.Clear
'y con un bucle recorro la hoja, cargando algunos
'detalles de las películas
With Sheets("peliculas")
    For X = 2 To F
        lstPeliculas.AddItem .Cells(X, "A") _
        & "-" & .Cells(X, "B")
    Next X
End With
End Sub

Como antes expresé, esto se puede ampliar mucho: generar un ABM para clientes o hasta aprovechar la hoja "películas" para marcar si el film está o no alquilado, y por quien. Aquí solo intento reflejar los aspectos fundamentales a la hora de interactuar entre un formulario y una hoja de cálculos.
Vean el código VBA que carga las imágenes y noten que a cada imagen la llamé del mismo nombre que su respectivo código (columna "A"). Por ej: "GLADIADOR" posee el código nro 1 y su imagen se llama "1.jpg". Esto facilita la incorporación de nuevas gráficas, ya que solo guardaremos la foto en el directorio respectivo y la llamaremos del mismo nombre que el código que le dimos a la película al momento de cargarla.

Les dejo un archivo ".rar", con el libro de Excel y 3 imágenes. No olviden guardarlas dentro del mismo directorio que el libro, o (si lo hacen en otro lado) modificar la variable RutaImagen, para que la macro lea correctamente:

RutaImagen = ActiveWorkbook.Path & "\ImagenesDePeliculas\"

Suerte y gracias José por tus lecturas y comentarios. Cualquier duda para ampliar lo expuesto, solo avisen.