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.
