Controlar vencimientos de facturas y recibos

Si tienes cosas que hacer, mejor deja la lectura de este artículo para cuando dispongas de tiempo, porque una vez redactado todo, acabo de darme cuenta que ocupa la friolera de 16 páginas en DIN-A4. En cualquier caso, si prefieres descargarte la aplicación ahora, y leerte el resto más tarde, puedes hacerlo desde aquí: descargar la aplicación para el control de vencimientos de facturas y recibos. Eso sí, al menos léete los párrafos iniciales para saber de qué va esta aplicación en Excel, y los párrafos finales de este artículo, para saber cual es el procedimiento que debes seguir como usuario de la aplicación, para su correcto funcionamiento, y saber como debes introducir los datos, para no obtener errores inesperados.

Son muchas las pequeñas empresas, ya sean talleres, asociaciones, fundaciones, cooperativas, microempresas, e inclusos profesionales o empresarios individuales, que por su volumen de facturación y por su carga de trabajo administrativo, no requieren del uso de complejos programas para llevar el control de sus facturas, y saber cuando tienen que presentar los recibos al cobro, o cuando tienen que llamar al cliente para reclamar el pago de las facturas.

Normalmente estas aplicaciones de control de recibos, forman parte de los propios programas de contabilidad, pero como muchas de esas empresas, probablemente tengan externalizada su contabilidad, delegando este trabajo en una gestoría o asesoría fiscal y contable, he pensado que podría ser de utilidad a más de uno, esta sencilla pero útil aplicación.

Como siempre, se trata de una aplicación en Excel para el control de vencimientos de facturas y recibos, y es de libre distribución, como todo lo que puedes encontrar en este blog de Excel. Es una aplicación que no está protegida, por lo que podéis copiarla, enviársela a un amigo, o simplemente cambiar lo que os apetezca para adaptarla a vuestras necesidades. Respecto a esto último, solo quiero comentar que la aplicación va a servirle al 99,99% de los usuarios (por no decir al 100%), pues no está diseñada para un sector de actividad en concreto, de tal forma que servirá tanto para una empresa industrial como de servicios, ejerciten la actividad que ejerciten.

Voy a explicar un poco por encima, cómo debe utilizarse esta aplicación, para un correcto funcionamiento. No es nada complicado su uso, pues incluso los más vagos pueden saltarse este pequeño tutorial, que no va a tener problemas para hacerse con él, en menos de un minuto :-)

La aplicación consta de 5 hojas. Estas hojas no están ocultas, pero lo que sí que hemos hecho es ocultar las etiquetas de las hojas para que el usuario interactúe con los botones, y no a través de clics en las pestañas. Las hojas en cuestión, son las siguientes:

- Una hoja para el menú principal.

- Otra hoja para dar de alta, modificar, y ver los datos de nuestros clientes.

- Otra hoja para dar de alta, modificar, y ver las condiciones de pago (condiciones de cobro de nuestras facturas).

- Otra hoja para dar de alta, modificar, y ver las facturas que hemos emitido. Las facturas se tienen que generar con otra aplicación informática (programa de facturación), pues esta aplicación en Excel no es un programa de facturación, sino de control de vencimientos de facturas.

- Otra hoja para obtener una previsión de cobros, en función de los datos de las facturas que hemos introducido. Esta previsión de cobros, nos muestra los cobros previstos mes a mes, así como un total general por cliente.

Vamos a explicar para qué sirve cada hoja, y que es lo que encontraremos en cada una de ellas:

Menú principal:

Pocas cosas podemos decir sobre la funcionalidad de esta hoja, porque es más que evidente. Un pantallazo nos sacará de dudas:



El macro que tenemos en esta hoja (la hoja1), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de clientes:

Desde esta hoja, daremos de alta nuestros clientes, con todos los datos fiscales, y de contacto, así como su forma de pago, seleccionándola del desplegable que se genera automáticamente al dar de alta el nombre del cliente. También se generará una validación de datos automática, por lo que si el cliente tiene fecha de pago fija, solo podremos introducir un número entre el 1 y el 31.

Este sería un pantallazo con un ejemplo donde salen nuestros clientes (clic sobre la imagen, para ampliarla):



En esta hoja tenemos dos macros. Uno que se ejecuta al activarse la hoja (la hoja2), y nos sirve para proteger la hoja:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Este otro macro se ejecutará cuando cambiemos un dato de la hoja en cuestión. Si el cambio afecta a la columna A, entonces ocurrirá lo que comentaba anterior mente, es decir, se generará un desplegable de forma automática, y se generará también una validación de datos:

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'ocultamos el procedimiento Application.ScreenUpdating = False 'fichamos la celda donde estamos celda = ActiveCell.Address 'si introducimos datos en la columna A, 'entonces añadimos la validación de datos en 'las dos columnas finales If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'añadimos la lista de validación de las formas de pago Cells(Target.Row, 11).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=FPA" End With 'añadimos la lista de validación del día de pago fijo Cells(ActiveCell.Row, 12).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, Operator:=xlBetween, _ Formula1:="1", Formula2:="31" .ErrorMessage = "El día de pago debe estar entre el 1 y el 31." End With End If 'volvemos donde estábamos Range(celda).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de formas de pago:

Esta hoja nos sirve para dar de alta las diferentes formas de pago, que luego serán las que se muestren en el desplegable al dar de alta los clientes. Un ejemplo de ello es el pantallazo que os presento a continuación (clic sobre la imagen, para ampliarla):



El macro que tenemos en esta hoja con las formas de pago (la hoja3), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Facturas emitidas:

Aquí es donde iremos introduciendo las facturas de nuestra empresa. Solo tendremos que seleccionar el cliente desde el desplegable que nos aparecerá en la columna A. Este desplegable se genera automáticamente al colocarnos en una celda de esa columna, y toma los datos de la hoja de clientes (evidentemente de los clientes que hayamos introducido). Si generamos una nueva factura, antes deberemos dar de alta al cliente, y su forma de pago si es que no la tenemos ya en nuestra hoja de forma de pago.

El aspecto que presenta esta hoja de facturas es similar a la de este ejemplo (clic sobre la imagen, para ampliarla):



En el caso de haber facturas vencidas, la fila nos saldrá de color azul celeste, para tenerlas más a la vista. Solo nos quedará llamar a los clientes que no han pagado todavía, para recordarles que su factura ha vencido, y no hemos recibido el cobro. Una vez las hayamos cobrado, eliminaremos las facturas, situándonos en cualquier celda de la fila, y pulsando el botón "Eliminar fila".

Lo primero que tenemos que hacer para dar de alta un cliente, es seleccionarlo del desplegable, tal y como se muestra en este ejemplo (clic sobre la imagen, para ampliarla):



Una vez seleccionado el cliente, automáticamente nos aparecerán a la derecha una serie de datos, algunos de ellos son propuestas que evidentemente podemos modificar, como el número de factura (nos genera el siguiente nº de factura presuponiendo que el de la fila anterior es el último número de factura), y la fecha de la factura (presuponiendo que es la misma que la fecha de la fila anterior). El vencimiento evidentemente no lo debemos introducir, pues lo obtenemos automáticamente a partir de la fecha de emisión de la factura, y de las condiciones de pago (forma de pago).

Con todo ello, y siguiendo con nuestro ejemplo, al seleccionar en el desplegable, nos aparecerá algo como lo de este ejemplo (clic sobre la imagen, para ampliarla):



Los macros que nos encontraremos en esta hoja (la hoja4), son los siguientes. Uno de ellos, para proteger la hoja, en el momento de activarse:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Otro macro que se ejecuta al seleccionar una celda de la columna A, y que nos sirve para generar el desplegable con los clientes a seleccionar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si seleccionamos una celda de la columna A, 'entonces añadimos la validación de datos en 'la celda en cuestión If Not Application.Intersect(Target, Range("A:A")) _ Is Nothing And ActiveCell.Row >= 5 Then 'añadimos la lista de validación de los clientes Cells(Target.Row, 1).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=CLI" End With End If 'protegemos la hoja ActiveSheet.Protect End Sub

Y este otro macro que se ejecutará cuando cambiemos un dato de la columna A, y que nos generará todos los datos que comentábamos anteriormente (número de factura, fecha de factura, vencimiento, días hasta el vencimiento, y días de exceso sobre el vencimiento):

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si modificamos una celda de la columna A, añadimos 'las fórmulas en las columnas correspondientes If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'si no tenemos cliente o fecha de fra., borramos el vto. si 'lo hay así como los días de exceso, y los días hasta el vto. If ActiveCell = "" Then ActiveCell.Offset(0, 1) = "" ActiveCell.Offset(0, 2) = "" ActiveCell.Offset(0, 4) = "" ActiveCell.Offset(0, 5) = "" ActiveCell.Offset(0, 6) = "" End If 'si seleccionamos un cliente en el desplegable 'añadimos la fra. previsible, la fecha de fra. previsible, 'el vto., los días de exceso, y los días hasta el vto. If ActiveCell <> "" Then 'añadimos el nº de fra. previsible If ActiveCell.Offset(-1, 1) <> "" And ActiveCell.Offset(0, 1) = "" Then ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1 End If 'añadimos la fecha de fra. previsible If ActiveCell.Offset(-1, 2) <> "" And ActiveCell.Offset(0, 2) = "" Then ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) End If 'añadimos el vencimiento If ActiveCell.Offset(-1, 4) <> "" And ActiveCell.Offset(0, 4) = "" Then ActiveCell.Offset(0, 4).FormulaR1C1 = "=IF(VLOOKUP(RC[-4]," & _ "TCLI,12,0)<>"""",IF(DAY(RC[-2]+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0)" & _ ",TFPA,2,0))>VLOOKUP(RC[-4],TCLI,12,0),MIN(DATE(YEAR(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0))+1,DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP(RC[-4]," & _ "TCLI,11,0),TFPA,2,0),1)),MIN(DATE(YEAR(RC[-2]+" & _ "VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0),0))),RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0))" End If 'añadimos los días hasta el vencimiento If ActiveCell.Offset(-1, 5) <> "" And ActiveCell.Offset(0, 5) = "" Then ActiveCell.Offset(0, 5) = "=IF(TODAY()-RC[-1]<0,RC[-1]-TODAY(),0)" End If 'añadimos los días de exceso sobre el vencimiento If ActiveCell.Offset(-1, 6) <> "" And ActiveCell.Offset(0, 6) = "" Then ActiveCell.Offset(0, 6) = "=IF(TODAY()-RC[-2]>=0,TODAY()-RC[-2],0)" End If End If End If 'protegemos la hoja ActiveSheet.Protect End Sub

Previsión de cobros:

Una vez tengamos dadas de alta todas las facturas, simplemente deberemos acceder a la hoja donde se nos genera de forma automática la previsión de cobros. Para ello, pulsaremos el botón "Previsión de cobros".

Lo que obtendremos será algo similar a lo que se muestra en el siguiente ejemplo:



En esta hoja no tenemos ningún macro, pues todo el código que genera la previsión de cobros, lo tenemos en un macro dentro del Módulo1.

Vamos a entrar ahora a comentar los macros que tenemos en el Módulo1. Lo primero que hay es el macro Auto_open(), que como sabéis es el macro que se ejecuta al abrir el fichero. El código de nuestro macro Auto_open() es el siguiente (lo he modificado una vez publicado el artículo, para que se carguen automáticamente las herramientas para análisis, y no tener problemas con la función FIN.MES):

Sub Auto_open() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'activamos las herramientas para análisis, 'para no tener problemas con la función FIN.MES AddIns("Herramientas para análisis").Installed = True 'no mostramos las pestañas (las hojas) ActiveWindow.DisplayWorkbookTabs = False 'buscamos si hay alguna factura vencida, y también 'si hay facturas para vencer en los próximos 7 días Hoja4.Select Range("G5").Select 'si no hay facturas, saltamos a la línea correspondiente If ActiveCell.Offset(0, -6) = "" Then GoTo sinfacturas 'para todo el rango de datos de la columna J For i = 5 To Selection.End(xlDown).Row 'miramos si hay facturas vencidas If ActiveCell > 0 Then vencidas = vencidas + 1 'miramos si hay facturas que 'vencen en los próximos 7 días If ActiveCell.Offset(0, -1) <= 7 And _ ActiveCell.Offset(0, -1) > 0 Then proximas = proximas + 1 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'nos situamos para escribir la siguiente factura ActiveCell.Offset(0, -6).Select 'mostramos un mensaje si hay facturas vencidas If vencidas = 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " factura vencida.") _ , , "Facturas vencidas" If vencidas > 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " facturas vencidas.") _ , , "Facturas vencidas" 'mostramos un mensaje si hay facturas próximas a vencer If proximas = 1 Then MsgBox ("Hay " & proximas & " factura que vence " _ + "en los próximos 7 días."), , "Facturas para vencer" If proximas > 1 Then MsgBox ("Hay " & proximas & " facturas que vencen " _ + "en los próximos 7 días."), , "Facturas para vencer" 'nos situamos en el menú principal sinfacturas: Hoja1.Select Range("E12").Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

No comentaré mucho sobre lo que hace el macro Auto_open(), porque con leer los comentarios del propio macro, lo tenemos todo chupado. Dentro de ese código, hay dos partes importantes que sí que me gustaría recalcar. Una de ellas, es que se nos mostrará un aviso informándonos de las facturas que tenemos vencidas. Este aviso se nos mostrará con tan solo abrir el fichero, siempre y cuando tengamos facturas vencidas, claro está. En el caso de haber facturas vencidas, se nos mostrará un mensaje similar a este que os muestro a continuación, donde nos avisa que hay 9 facturas vencidas:



En el caso de tener facturas que venzan en los próximos 7 días, la aplicación también nos mostrará un aviso como el que se nos muestra a continuación, donde nos avisa que tenemos 1 factura que nos vence en los próximos 7 días:



Aparte del macro Auto_open(), tenemos estos otros macros cuyo código os incluyo a continuación. Un macro para imprimir:

Sub Imprimir() 'Imprimimos la hoja ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Otro macro para guardar el fichero pero sin cerrar la aplicación, es decir, para guardar los datos, y continuar trabajando:

Sub Guardar() 'Guardamos el libro ActiveWorkbook.Save End Sub

Otro macro para ir a la hoja de clientes:

Sub Clientes() 'Vamos a la hoja2 Hoja2.Select Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja con las formas de pago:

Sub Formas_de_pago() 'Vamos a la hoja3 Hoja3.Select Range("B5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja de facturas:

Sub Facturas() 'Vamos a la hoja4 Hoja4.Select Range("A5").Select 'nos situamos en la primera fila libre If ActiveCell.Offset(0, 1) <> "" Then Selection.End(xlDown).Offset(1, 0).Select End If End Sub

Otro macro para volver al menú principal:

Sub Volver_al_menu() 'volvemos al menú, pero dependiendo 'de la hoja donde estemos, ordenaremos 'también los datos If ActiveSheet.CodeName = "Hoja2" Then Ordenar_clientes If ActiveSheet.CodeName = "Hoja3" Then Ordenar_formas_de_pago 'volvemos al menú Hoja1.Select Range("E12").Select End Sub

Otro macro para ordenar alfabéticamente los clientes:

Sub Ordenar_clientes() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("A5:K5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A5") 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="CLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'ahora le ponemos un nombre a toda la tabla Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TCLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C12" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente los clientes.") _ , , "Clientes ordenados" End Sub

Otro macro para ordenar alfabéticamente las formas de pago:

Sub Ordenar_formas_de_pago() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("B5:C5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B5") 'seleccionamos todo el rango continuo 'desde B5 hasta abajo del todo Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C2" 'ahora le ponemos un nombre a toda la tabla Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TFPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C3" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente las formas de pago.") _ , , "Formas de pago ordenadas" End Sub

Tomad un poco de aire, que aún quedan unos cuantos macros más. El siguiente que nos encontraremos es para eliminar clientes:

Sub Eliminar_cliente() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre del cliente a buscar 'y la celda donde estamos cliente = Cells(ActiveCell.Row, 1) celda = ActiveCell.Address 'antes de borrar un cliente, buscaremos que no tengamos 'facturas emitidas a nombre de ese cliente a eliminar Hoja4.Select 'buscamos el cliente, y si existe en las facturas 'creamos una variable If Not Cells.Find(cliente) Is Nothing And cliente <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja2 Hoja2.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar este cliente, debes borrar sus facturas." _ + Chr(10) + "Por favor, accede a la hoja de Faturas, para eliminarlas.") _ , , "Imposible borrar este cliente" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Los datos de este cliente, han sido eliminados.") _ , , "Cliente eliminado" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar formas de pago:

Sub Eliminar_forma_de_pago() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre de la forma de pago a buscar 'y la celda donde estamos formadepago = Cells(ActiveCell.Row, 2) celda = ActiveCell.Address 'antes de borrar una forma de pago, buscaremos 'que no tengamos facturas emitidas a clientes 'con esa forma de pago a eliminar Hoja2.Select 'buscamos la forma de pago, y si existe en 'algún cliente, creamos una variable If Not Cells.Find(formadepago) Is Nothing And formadepago <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja3 Hoja3.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar esta forma de pago, debes borrar o cambiar la forma" _ + Chr(10) + "de pago de los clientes que utilizan esta modalidad de pago a borrar." _ + Chr(10) + Chr(10) + "Por favor, accede a la hoja de Clientes, para editarlos.") _ , , "Imposible borrar esta forma de pago" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Esta forma de pago, ha sido eliminada.") _ , , "Forma de pago eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar facturas:

Sub Eliminar_factura() 'Si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("La factura seleccionada, ha sido eliminada.") _ , , "Factura eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Y el último macro que nos encontraremos, y también el más largo, es este, que nos sirve para generar nuestra previsión de cobros, detallando los vencimientos por meses y por clientes:

Sub Prevision_de_cobros() 'Si hay errores, que continúe On Error Resume Next 'cambiamos el texto del botón de la hoja1 y hoja4 If ActiveSheet.CodeName = "Hoja1" Or _ ActiveSheet.CodeName = "Hoja4" Then 'creamos una variable If ActiveSheet.CodeName = "Hoja1" Then hoja = "menu" If ActiveSheet.CodeName = "Hoja4" Then hoja = "facturas" 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre, y lo ponemos en rojo Selection.Characters.Text = "Procesando..." With Selection.Font .ColorIndex = 3 End With End If 'ocultamos el procedimiento Application.ScreenUpdating = False 'comprobamos que tengamos facturas If Hoja4.Range("A5") = "" Then 'si no hay facturas, mostramos un mensaje MsgBox ("Por favor, revisa todo, para poder continuar.") _ + Chr(10) + Chr(10) + "Al parecer no hay facturas, y por tanto no se" _ + Chr(10) + "puede generar la previsión de cobros." _ , , "Hay errores" 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If Hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf Hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro Exit Sub End If 'fichamos la celda donde estamos celda = ActiveCell.Address 'eliminamos todo lo que haya en la hoja5 Hoja5.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'borramos las filas (los clientes) Selection.EntireRow.Delete 'borramos ahora las fechas Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select 'borramos las filas (los clientes) Selection.EntireColumn.Delete 'seleccionamos la hoja2 Hoja2.Select 'nos situamos en la primera celda con datos Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'copiamos los datos Selection.Copy 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos la hoja5 Hoja5.Select 'seleccionamos la celda A5 Range("A5").Select 'pegamos los datos Selection.PasteSpecial Paste:=xlPasteValues 'fichamos la fila máxima fila_maxima = Selection.End(xlDown).Row 'vamos a la hoja4, donde tenemos las facturas 'y seleccionamos la fecha menor y mayor Hoja4.Select 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FRAS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'seleccionamos todo el rango continuo 'desde D5 hasta abajo del todo Range("D5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="IMPORTES", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C4:R" & ActiveCell.Row & "C4" 'seleccionamos todo el rango continuo 'desde E5 hasta abajo del todo Range("E5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="VTOS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C5:R" & ActiveCell.Row & "C5" 'volvemos a E5 Range("E5").Select 'definimos dos variables (fecha mínima y máxima) minimo = ActiveCell maximo = ActiveCell Do While Not IsEmpty(ActiveCell) 'seleccionamos la fecha mínima y máxima If ActiveCell < minimo Then minimo = ActiveCell If ActiveCell > maximo Then maximo = ActiveCell 'controlamos que no falten datos: nombre del cliente, 'fecha de factura, vencimiento e importe. 'Comenzamos controlando el vencimiento If IsDate(ActiveCell) <> True Then incorrecto = True 'controlamos la fecha de la factura If IsDate(ActiveCell.Offset(0, -2)) <> True Then incorrecto = True 'controlamos el importe If Not IsNumeric(ActiveCell.Offset(0, -1)) Or _ ActiveCell.Offset(0, -1) = "" Then incorrecto = True 'controlamos que exista el cliente If ActiveCell.Offset(0, -4) = "" Then incorrecto = True 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'si hay errores, mostramos un mensaje If Err.Number <> 0 Then 'mostramos un mensaje MsgBox ("Existen errores." _ + Chr(10) + Chr(10) + "Por favor, revisa todo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'si hay errores, mostramos un mensaje If incorrecto = True Then 'mostramos un mensaje MsgBox ("Existen errores en algunas de estas columnas:" _ + Chr(10) + Chr(10) + "- Nombre de los clientes." _ + Chr(10) + "- Fecha de las facturas." _ + Chr(10) + "- Importe de las facturas." _ + Chr(10) + "- Vencimiento de las facturas." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'ahora seleccionamos el primer día del mes del mínimo y máximo minimo = "01" & "/" & Month(minimo) & "/" & Year(minimo) maximo = "01" & "/" & Month(maximo) & "/" & Year(maximo) 'recuerda que esta aplicación ha salido de 'http://hojas-de-calculo-en-excel.blogspot.com 'calculamos la diferencia en meses entre el mínimo y el máximo meses = DateDiff("m", minimo, maximo) 'si la diferencia de meses es superior a 120 'mostramos un mensaje de error If meses > 120 Then 'mostramos un mensaje MsgBox ("Hay más de 10 años de diferencia entre el " _ + Chr(10) + "primer vencimiento, y el último vencimiento." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Vencimientos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'volvemos a la hoja5 Hoja5.Select 'escribimos los meses para planificar los cobros, 'primero escribiendo los meses Range("A4").Select For i = 0 To meses 'seleccionamos la columna de la derecha ActiveCell.Offset(0, 1).Select 'escribimos el mes ActiveCell = DateAdd("m", i, minimo) Next 'escribimos el total ActiveCell.Offset(0, 1) = "TOTAL" 'fichamos la columna máxima columna_maxima = Selection.End(xlToRight).Column 'copiamos el formato de la celda A4 Range("A4").Select ActiveCell.Copy 'seleccionamos todo el rango continuo por 'la derecha, desde la segunda columna Range(Selection.Offset(0, 1), Selection.End(xlToRight)).Select 'pegamos los formatos Selection.PasteSpecial Paste:=xlPasteFormats 'ponemos los formatos de fecha (mes y año) Selection.NumberFormat = "[$-340A]mmm yyyy" 'alineamos esos encabezados a la derecha Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlRight End With 'nos situamos en la celda B5 Range("B5").Select 'escribimos la fórmula ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT((FRAS=RC1)*(MONTH(VTOS)=MONTH(R4C))*(YEAR(VTOS)=YEAR(R4C))*IMPORTES)" 'copiamos y pegamos la fórmula en toda la tabla Selection.Copy Range(Cells(5, 2), Cells(fila_maxima, columna_maxima - 1)).Select ActiveSheet.Paste 'ponemos los totales Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select ActiveCell = "TOTALES" 'pasamos a la siguiente columna ActiveCell.Offset(0, 1).Select 'escribimos las sumas totales de cada mes For i = 2 To columna_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(R[-" & fila_maxima - 4 & _ "]C:R[-1]C)" 'nos movemos a la derecha ActiveCell.Offset(0, 1).Select Next 'volvemos a la última columna, y 'seleccionamos toda la fila ActiveCell.Offset(0, -1).Select Range(Selection, Selection.End(xlToLeft)).Select 'ponemos la fila en negrita, con 'bordes, y con la trama de color amarillo Selection.Font.Bold = True With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With With Selection.Interior .ColorIndex = 36 End With 'nos vamos a la última columna, 'es decir, la de los totales Cells(5, columna_maxima).Select 'escribimos las sumas totales de cada cliente For i = 5 To fila_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(RC[-" & columna_maxima - 2 & _ "]:RC[-1])" 'nos movemos hacia abajo ActiveCell.Offset(1, 0).Select Next 'ponemos todas esas sumas de cada cliente, en negrita Range(Selection, Selection.End(xlUp)).Select Selection.Font.Bold = True 'le volvemos a poner el nombre correcto al botón boton: If hoja = "menu" Or hoja = "facturas" Then 'seleccionamos la hoja1 o la hoja4 If hoja = "menu" Then Hoja1.Select If hoja = "facturas" Then Hoja4.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro, si hay errores If Err.Number <> 0 Or incorrecto = True Or meses > 120 Then Exit Sub End If 'nos situamos en la fila con el rótulo de los 'totales, de la hoja5 Hoja5.Select Range("A5").Select Selection.End(xlDown).Select 'protegemos la hoja ActiveSheet.Protect 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Finalmente comentar que el procedimiento correcto para inicializar la aplicación sería este, y necesariamente en este orden que os incluyo a continuación, pues en caso de no seguir este procedimiento, la aplicación puede responder con errores o de forma inesperada:

1. Dar de alta las formas de pago.
2. Dar de alta los clientes.
3. Dar de alta las facturas.

A partir de ese momento, y si nuestros clientes son los que son, y no generamos nuevos clientes, tan solo tendremos que preocuparnos por introducir las facturas. Si tenemos un cliente nuevo, deberemos darlo de alta previamente, y si su forma de pago es nueva, deberemos dar de alta ésta lo primero de todo, siguiendo el esquema explicado en el párrafo anterior.

Espero que os sea de utilidad esta aplicación. Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011).

Si hacéis algún cambio a la aplicación, os ruego que no me pidáis modificaciones personalizadas para vuestro caso en concreto o el de vuestra empresa, porque mi tiempo es muy limitado, y prefiero utilizarlo en resolver cuestiones de uso común. Si por el contrario, crees que sería interesante introducir alguna mejora en la aplicación, para que sea de uso público y los demás puedan aprovechar esa utilidad, entonces gustoso intentaré darle solución, si mis limitados conocimientos me lo permiten.

Si en vuestra empresa trabajáis con descuento comercial (descuento de efectos), en este otro artículo podréis descargar la aplicación para controlar vencimientos de facturas y recibos, con descuento comercial.

Por cierto, al hilo de esta aplicación para el control de vencimientos de facturas y recibos, solo quiero recordaros a los que realizáis actividades empresariales o profesionales en territorio español, que la reciente publicación de la ley de lucha contra la morosidad comercial, solo permite un máximo de 60 días de crédito para aquellas operaciones que se realicen a partir del 1 de enero de 2013. Es decir, a partir de esa fecha, no podréis darle a vuestros clientes un plazo de pago superior a los 60 días (si los clientes tienen fecha fija de pago, deberéis tener en cuenta esta circunstancia, para adaptar las condiciones de pago, y que no exceda del límite marcado en la ley). No obstante, hasta esa fecha (1 de enero de 2013) uno no puede hacer lo que desee, ya que desde la entrada en vigor de la ley (7 de julio de 2010), existe un periodo transitorio para adaptarnos a ese máximo de 60 días, y no tener que bajar de golpe de los 180, 150, 120, 90 días, o los que vuestra empresa de a sus clientes. De esta forma, todas las empresas competirán en las mismas condiciones crediticias. Algo muy importante que como novedad incorpora la ley (en sustitución de la anterior ley antimorosidad), es que no admite el acuerdo entre las partes, para alargar los plazos de crédito a los clientes, es decir, bajo ningún concepto se podrá superar el límite de los 60 días de crédito comercial, pues no se permite que pactes alargar ese límite con tus clientes.