Hojas de cálculo en Excel - página principal

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.



64 comentarios:

AAAGROUP dijo...

HOLA. SALUDOS, soy alfred de aaawebsitesolution.com
estoy empezando a aprender todo sobre excel - macros, por fin baje la cartera de recibos pero no abre
previsión de cobros por errores en code - mi email es aaainternationalgroup@yahoo.com

Javier Marco dijo...

Pues lo he probado en las versiones de Excel 2003 y Excel 2007, y en ambas funciona perfectamente.

¿Alguien en la misma situación, al que no le funcione el apartado correspondiente a la previsión de cobros?.

Un saludo.

diego dijo...

Hola Javier, Tambien me salió error en prevision de cobros y lo solucioné quitando de la hoja de clientes los dias fijos de pago.
Gracias por tu dedicacion en este blog, es muy util. Dios te bendiga

Javier Marco dijo...

Gracias por tu comentario, Diego. ¿Es posible que utilices una versión en inglés de Excel?.

Creo que todo viene por la función FIN.MES que requiere tner cargadas las "Herramientas para Análisis" (en el macro se cargan automáticamente). Si tuvieras una versión en inglés de Excel, es posible que se solucione cambiando en el macro todas las referencias a FIN.MES por EOMONTH (el equivalente en inglés).

Un saludo.

MiCajitaRedonda... dijo...

Hola Javier! Buenas Tardes!! Te Comento que me funciona excelente! Pero no entiendo bien las formas esas de pago jejejejeje.... Me gustaría que me dieras tu correo, para enviarte un par de archivos con los que trabajo a Diario, para ver que opinabas jejeje!!! eso de los vencimientos, tenia tiempo intentandolo hacer, gracias al cielo que lo hiciste tu jajaja!! Aunque yo lo manejo como proveedores y no como clientes! Bueno, espero tu pronta respuesta con tu correo! Gracias! Mi Correo es gabrielaromeroinc@hotmail.com Saludos!!

Anónimo dijo...

Buenas: Necesito una macro que copie la celda de un rango, y luego minimice el libro... no una hoja.

Gracias, esta chever su blog

mercadeo dijo...

Muy buenas tus enseñamzas, gracias por dedicar tu tiempo a compartir tus conocimientos.

carlos ladino dijo...

Viejo de antemano felicitaciones por tu página. Necesito un favor, quiero saber como conecto una pagina de excel a acces 2007, es decir, que pueda hacer todas las macros en excel para manipular el programa que haya creado, pero que los datos que introduzca los almacene en una base de datos de acces preestablecida, es decir que los datos que haya introducido en un formulario al oprimir un commandbutton me los envie a una base de datos de acces y que igual desde excel los pueda yo consultar.
Gracias.
Gracias.

Javier Marco dijo...

Algunas de las cosas que preguntas, están explicadas en las entradas donde hablo de Excel y Access.

Saludos.

Leandro dijo...

Hola! Estoy buscando una plantilla para controlar el tiempo cronometrado de un recorrido. Digamos que quiero poner el tiempo parcial según el lugar, qué tiempo hice, divididos por: día 1, día 2, 3, etc. y al final el tiempo total por día, que al final me marque la diferencia que tuve respecto uno de otro día, y el promedio general. Gracias master!!

leansa1578@hotmail.com

astuaei dijo...

Estimado gracias, una consulta yo utilizo tu archivo de control de vencimientos y es muy bueno gracias, pero tengo una necesidad en el reporte de "Previcion de Cobros", despues de la columna Cliente, le podrias agregar la I de la "web" hoja alta de clientes y la columna L "Dia de pago fijo"
tiene que quedar asi
Cliente Web Dia de pago fijo, fechas... mi correo electronico es astuaei@gmail.com
gracias

Anónimo dijo...

hola: quisiera que me ayudes en esto de los macros, la verdad es que soy recontra nuevo en esto: lo que pasa es que tengo un libro que contiene dos macros asociadas a dos botones: uno para crear un documento en blanco y otro para ordenar alfabéticamente los nombres que vaya colocando en esta hoja, el problema es que cuando se crea una nueva hoja en blanco y coloco los nombres estos no se ordenan alfabéticamente, y lo que hace es ordenar los nombres de la primera hoja...me podrías ayudar..espero me hallas entendido

Anónimo dijo...

hola te felicito por tu trabajo, y estoy con un pequeño problema con la prevision de cobros y no consegui solucionar al cambiar fin.mes por EOMONTH si me podrias ayudar gracias este es mi correo cri_guer@hotmail.com

Javier Marco dijo...

En el macro Auto_open() hay una línea llamada así:

AddIns("Herramientas para análisis").Installed = True

Tendrás que cambiarla a su equivalente en inglés. Puedes utilizar la grabadora de macros para hacerlo.

Un saludo.

Felipe Prado dijo...

Hola Javier: No puedo descargar el archivo, el link no funciona, podrias volverlo a subir porfavor..
Mil gracias

Javier Marco dijo...

Hola Felipe. Verás un contador de marcha atrás es esa página, debajo del botón "Descarga premium". Cuando ese contador llegue a cero, se convertirá en un botón llamado "Descarga normal". En ese momento, cliquea sobre ese botón, y te descargará el fichero.

Un saludo.

Anónimo dijo...

muchas gracias he encontrado tu pagina muy util e interesante, te felicito.

Anónimo dijo...

Saludos, escribo para felicitarte por la hoja esta muy completa, y por el tiempo que te debio llevar hacerla. Aun mas te felicito por compartir no solo la hoja sino mas por explicar como esta hecha y lo que me ayuda a estudiarla y aprender mas del tema.

Soy Eduardo Rodríguez Pacheco de Alajuela, Costa Rica y estoy aprendiendo sobre macros.

Javier Marco dijo...

Muchas gracias por tu comentario, Eduardo :-)

HECTOR dijo...

Javier, felicidades por tu pagina, excelente, la prevision de cobros podria hacerla que me la de por dia y no por mes...saludos.-

Anónimo dijo...

Hola Javier,

Es fantástico que haya personas cómo tú, que comparten su sabiduría de forma altruista.
Muchísimas gracias por tus aportes y viva la cultura libre!

Marc

Anónimo dijo...

Llevaba tiempo buscando una plantilla para el control de los cobros y creo que esta me irá genial!! El problema es que mi empresa lleva los pagarés de los clientes al descuento y eso no sé como controlarlo...

Javier Marco dijo...

En principio la aplicación está pensada para presentar recibos al cobro (al vencimiento de las facturas, o tres o cuatro días antes, para que el banco haga las gestiones oportunas de cobro), o bien para llamar al cliente si es que paga con transferencia, cheque, o cualquier otro medio de pago que no sea recibo domiciliado, en caso de no haber recibido nada al vencimiento de la factura.

Si negocias todos los efectos en tu empresa, en principio el control de vencimientos no te debería preocupar demasiado, pues una vez descontado, tu lo has cobrado y te despreocupas de controlar nada más. El problema surgiría en caso de impago por parte del cliente, pues te llegaría cargado el pagaré (más los gastos de devolución), pero a nivel de vencimientos poco podrías hacer, salvo reclamar la devolución al cliente el mismo día en que te llega devuelto el pagaré.

Porque entiendo que negocias los pagarés de aquellos clientes que sabes casi con toda seguridad, que no te van a llegar devueltos. En caso contrario, sí que sería interesante que llevases el control, para saber que tal o cual día te puede llegar devuelto un pagaré, y que tienes que tener saldo suficiente en el banco para atender el impagado.

Saludos.

Anónimo dijo...

Hola Javier, gracias por tu rápida respuesta! Nosotros descontamos casi todos los pagarés de los clientes. El caso es que como las líneas de descuento siempre están limitadas, a veces no podemos llevar más pagarés hasta que no han vencido otros y por eso me sería útil poder controlar lo que llevo al banco teniendo en cuenta siempre el límite de cada entidad. Casi nunca nos llega devuelto un efecto, eso no me preocupa, aunque siempre es bueno saber cuando vence y controlar que la deuda del cliente queda saldada.

De todas formas, muchas gracias por compartir tus conocimientos! Me ha sido de gran ayuda!

Javier Marco dijo...

Hola que tal. Completamente de acuerdo con tu exposición.

En una segunda versión de esta aplicación (dentro de poco), incluiré esto que comentas, porque creo que puede ser de mucha utilidad.

Saludos.

Anónimo dijo...

Hola de nuevo Javier,

Genial! Espero impaciente esa nueva versión!!! De momento me las voy apañando como puedo... Muchas gracias de nuevo! Ojalá dominara el Excel como tu!

Un saludo.

Anónimo dijo...

Hola, Mi nombre es oscar, me ha sido de bastante utilidad esta hoja de control de Vencimientos de facturas y pagos, pero quisiera preguntarles Si la captura de las facturas no es consecutivo como puedo ordenarlas posteriormente, ya que si elimino una factura pagada al quitarla automaticamente le cambia el numero a la siguiente por un consecutivo?
como puedo evitar eso? gracias por su ayuda de antemano.

Javier Marco dijo...

Gracias por el aviso. Ven más cuatro ojos, que dos.

Efectivamente había un error, por el que al eliminar una factura, a la factura de la fila inferior se le cambiaba el nº (se autonumeraba). Está solucionado este error, y algún otro de carácter menor, que he observado al chequear la aplicación más a fondo.

Lo he corregido, y he resubido el fichero nuevamente. Puedes descargártelo, y si no quieres traspasar los datos de tus facturas al nuevo fichero, puedes optar por algo más sencillo. Copia y pega el código fuente que hay en la hoja "Facturas emitidas" (hoja4), de este nuevo fichero, al tuyo, pues los únicos cambios realizados, afectan al código que hay en esta hoja.

Espero que no haya más errores, aunque si localizáis alguno (he intentado testear la aplicación a fondo antes de lanzarla definitivamente, pero se me puede haber escapado algo), comunicádmelo, para solucionarlo.

Saludos.

Anónimo dijo...

Estoy utilizando el archivo y es genial. Me gustaria saber si se puede tambien contralar la cantidad pendiente que tengo de un cliente.
Ej. Me dejó pendiente la cantidad de 4.000€ y me los paga con recibos mensuales. Me gustaria contralar el pago mensual y la cantidad que que queda pendiente para saber hasta cuando pasar el recibo al cobro.

Gracias.

Javier Marco dijo...

Controlar la cantidad pendiente de cobro de un cliente, es precisamente lo que hace el programa :-) a través de la tabla que hay en la última hoja (previsión de cobros).

Respecto a lo que comentas, de fraccionar el cobro de 1 factura en n recibos, pues no está contemplado. Lo que yo haría, sería dividir la factura en esos n recibos e introducirlos en la aplicación como si fueran facturas distintas.

Me explico: Si tienes la fra. nº 21 que sube 1.000 €, y el cliente paga en 3 partes (333 €, 333 € y 334 €), en la aplicación lo entraría cambiando el número de factura (ojo, el nº de la factura solo se cambia aquí, para controla los vencimientos), para que nos de de la siguiente forma:

Fra.nº 21#1 ---> 333,00
Fra.nº 21#2 ---> 333,00
Fra.nº 21#3 ---> 334,00

Cada "factura" (fracción de factura), tendría un vencimiento distinto.

Le he puesto como separador "#" para que sepas que sería la 1ª parte de la fra. 21, la 2ª parte de la fra. 21, y la 3ª parte de la fra. 21. Si le pones una barra (21/1, 21/2, y 21/3) te lo cambiará, así que introduce un separador que no sea la barra o el guión (si quieres, puedes usar barra invertida: 21\1, 21\2, y 21\3).

Yo lo haría de esta forma, pues así tienes el control de cada recibo que componen el total de esa factura.

Un saludo.

Anónimo dijo...

Gracias por tu respuesta. probaré de esta forma que me indicas.

Saludos

Anónimo dijo...

Me gustaria saber si es posible añadir hojas al archivo y como se pueden ordenar e interactuar con ellas.

Gracias

Javier Marco dijo...

Sí, claro, se pueden añadir hojas al libro, como en cualquier otro libro de excel.

Para ordenarlas, simplemente hay que arrastrarlas allí donde quieras colocarlas.

Anónimo dijo...

Estoy trabajando con la pagina y me gustaria que me ayudases en estas dudas que tengo.

- Como puedo poner las paginas que inserte en la 1º pag. (como puedo ver las paginas que tengo) no aparecen las pestañas.
- Insertar columnas en la hoja de prev.de pagos y que las guarde, quiero poner despues de cada mes una columna.
- Como se pueden ordenar las facturas por fecha? por si anoto el dia 10 una factura y cuando llega el mes hay facturas del dia 5.

Gracias por tu ayuda.

Javier Marco dijo...

Respecto a la primera cuestión, para ver las pestañas, en Excel 2003, selecciona Herramientas --> Opciones --> y en la pestaña Ver, ponle una muesca en el apartado de opciones de ventana, a Etiquetas de hojas.

En Excel 2010 (y creo que también en Excel 2007, aunque no tengo esta versión delante), selecciona Archivo --> Opciones --> Avanzadas --> Muesca en Mostrar fichas de hojas.

Respecto a lo de inseratr columnas, no te lo recomendaría, pues la aplicación está pensada para actuar tal y como está diseñada. Si insertas columnas, en cuanto vuelvas a darle al botón de "Previsión de pagos", te desaparecerán. Lo mejor es no tocar esa hoja.

Para la tercera cuestión, simplemente deshabilita las macros cuando abras el fichero, así podrás ordenar las facturas como desees (googlea un poco para saber como habilitar/deshabilitar las macros, y para ordenar datos).

Un saludo.

Anónimo dijo...

Gracias por tu ayuda.
Estoy intentando ampliar el archivo para poder controlar tambien la cantidad pendiente que queda de un cliente en alq-compra.
Quiero hacer una hoja por cliente y que vaya descontando de una cantidad inicial en base a los pagos mensuales que realice.
Me podrias decir si es posible y como?
Saludos

Javier Marco dijo...

El fichero puedes adaptarlo a tus necesidades. Eso sí, si no eres un usuario avanzado de Excel, yo te recomendaría no tocar las hojas ya existentes, y montar la información que requieras, en hojas anexas (añadiendo tantas hojas como necesites).

Ayudarte a hacer esto, no puedo, porque excedería lo que es la finalidad de estos bloques de comentarios. Creo que lo que pretendes hacer, requeriría de un artículo completo.

Un saludo.

Anónimo dijo...

Hola saludos, Soy angel.Utilizo el programa "Controlar vencimientos de facturas y recibos" con proveedores y me va bien controlarlos, para saber lo que hay que pagar por mes.

Tengo un problema,ya no me marca la linea azul en las facturas ya vencidas. No se que ha salido mal, no entiendo nada de visual basic
ni de macros. Gracias de antemano.

Javier Marco dijo...

Comprueba que la fecha de tu ordenador esté bien y sea la correcta, pues efectúa los avisos de facturas vencidas, partiendo de esa fecha (la de tu ordenador).

En la barra de inicioa, abajo, a la derecha del todo, te aparece la hora. Haz doble clic sobre ella, y comprueba que la fecha del calendario que te aparecerá, es la correcta.

Saludos.

Nicolás dijo...

Hola Javier, gracias a tu sabiduría compartida estoy aprendiendo mucho sobre excel, tanto que me he atrevido a realizar un Libro para controlar las calificaciones y actas de una prueba de acceso. En el ejemplo de esta entrada he visto tu macro para ordenar alfabéticamente una lista de nombres y esta es mi humilde consulta: ¿es posible realizar una macro que ordene alfabéticamente una lista de nombres (apellidos,nombre) pero que en lugar de empezar por la letra A, comience a ordenar por una letra concreta, manteniendo a partir de dicha letra igualmente el orden alfabetico normal? Mi necesidad viene dada porque el orden de actuación de los aspirantes en la prueba queda fijado por un sorteo inicial para la letra de comienzo.

De antemano muchas gracias por tu tiempo y dedicación, es difícil encontrar hoy día personas tan altruistas como tu.

Saludos cordiales.
Nicolás, sonata1812@hotmail.com

Nicolás dijo...

Hola de nuevo Javier, al leer mi consulta me dió la sensación de que no estaba muy clara mi explicación, te pongo un ejemplo para que veas lo que pretendo.

lista 1
ALCAINA ORTEGA, JOSE
BELDA GARCIA, PATRICIO
CANOVAS NOGUERA, LAURA
FLORES PEREZ, ISABEL
GIL GARCIA, JAVIER
LOPEZ RAYA, SILVIA
MARTINEZ LOPEZ, DANIEL
RODRIGUEZ RODRIGUEZ, TERESA
SANCHEZ SOLANO, MARINA
VIVANCOS BELMONTE, PABLO

lista 2
FLORES PEREZ, ISABEL
GIL GARCIA, JAVIER
LOPEZ RAYA, SILVIA
MARTINEZ LOPEZ, DANIEL
RODRIGUEZ RODRIGUEZ, TERESA
SANCHEZ SOLANO, MARINA
VIVANCOS BELMONTE, PABLO
ALCAINA ORTEGA, JOSE
BELDA GARCIA, PATRICIO
CANOVAS NOGUERA, LAURA

Como ves, en lista 1 tengo los nombres ordenados alfabéticamente y en lista 2 los dispongo igualmente en orden alfabético pero comenzando por la letra F, si ésta hubiera sido la letra resultante del sorteo.
Hasta ahora lo vengo haciendo manualmente arreglando la lista según sea la letra de inicio, por eso hice la consulta para saber si se puede realizar una macro que me ordene el listado de nombres con ese criterio.

Gracias de nuevo por tu tiempo y paciencia.
Un saludo.

Javier Marco dijo...

Pues creo que lo más sencillo sería:

1. Ordenar de la forma tradicional (de la A a la Z).

2. Seleccionar todas las filas que comiencen por una letra anterior a la que hayas seleccionado como "primera" a la hora de ordenar. Por ejemplo, si quieres comenzar a ordenar por la F, cortarías todas las filas desde la A hasta la E.

3. Pegar esas filas cortadas, a partir del último registro (los que empiezan por Z).

Creo que esto sería lo más fácil. Prueba con la grabadora de macros, a ver que obtienes.

Saludos.

Nicolás dijo...

Gracias Javier por tu respuesta.

Así es como vengo haciéndolo, el problema es que tengo que preparar 23 libros anualmente, uno para cada asignatura donde los aspirantes son distintos en cada uno de ellos.

Ya tengo programado un formulario, entre otras cosas, donde pretendía asignar la letra del orden inicial a una variable para posteriormente trabajarla en una macro. Por eso solicitaba algún ejemplo en el que apoyarme para automatizar dicho proceso.

Dado que mis conocimientos de VBA son limitados para crearla desde cero, he buscado en la red alguna consulta similar y no he encontrado nada.

Gracias de nuevo por tu interés.
Un saludo

Anónimo dijo...

necesito hacer una planilla de exel con una columna con fecha de emision y otra con los dias q pasaron a la fecha

Marco Chuica dijo...

Javier: Valoro mucho tu aporte pues considero que no solamente es útil como producto final sino que en mi caso, y gracias a tus detalladas explicaciones, me ha motivado a investigar sobre programación en VBA. Te agradezco por compartir tus conocimientos. Saludos desde Perú.
Marco Antonio.

Byron dijo...

Gracias por compartir tu sabiduria

Anónimo dijo...

Hola queria saber si tenes alguna formula para que en una columna se sumen los totales de facturas que vencen detrminado mes si corresponden a determinado proveedor:
Ejemplo en la columna
A estan cargadas las facturasen la B sus proveedores y el monto de cada una de las facturas en C. ahora bien en otra hoja esta en la coluna A2 en adelante el proveedor y las columnas B-C-D-E-Etc en adelante están los meses de cada año, como puedo hacer que en B2 aparescan las facturas de ese mes correspondientes a ese proveedor.
Muchas gracias!!

Carlos

Javier Marco dijo...

Puedes utilizar la función SUMAPRODUCTO.

Aquí tienes un ejemplo para sumar por cliente y fecha:

http://www.2shared.com/document/mMwuKA_O/Suma_de_facturas_por_cliente_y.html

Fíjate que hay 2 hojas, una con los datos, y otra con el resumen de vencimientos e importes por cliente y mes.

Quizás parezca algo complicado, pero analiza bien las fórmulas y verás que no es tan difícil de entender.

Anónimo dijo...

hola quisiera saber como hacer que el vencimiento solo sea de 30 dias y no de 60..gracias! buenisimo aporte

Javier Marco dijo...

En esta aplicación, puedes informar si el vto. es a 30, a 31, a 42, o el que sea. Debes informarlo en las condiciones de cada cliente (hay una tabla para introducir los datos de cada cliente, y ahí debes especificar el vencimiento).

Saludos.

Jona dijo...

Buenas tardes Javier,

He estado probando tu excel y en las formas de pago que no sean "Contado" no calcula los días que faltan exactamente. He estado trasteando un poco y he logrado conseguirlo. Lo que he hecho ha sido añadir al código un simple if diciendo que compare la forma de pago con la palabra "contado", si es igual, que haga tu formula, sino, que haga tu formula invirtiendo el if principal.

El codigo quedaria algo asi... lo que no consigo invertir la parte entre asteriscos porque no logro ver donde se separa la parte true de la false

"=IF(VLOOKUP(RC[-4],TCLI,11,0)" & _
"=""Contado"",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))," & _
"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))****"

Javier Marco dijo...

Efectivamente es un poco complicado trastear con fórmulas de ese calibre, directamente en VBA. Para solucionarlo, "imprímelas" en una celda, como si estuvieras ejecutando esa parte del código.

Por otra parte no veo ningún cálculo erróneo (puede que lo haya, pero no lo veo, y eres el primero que me lo dice) en aquellas formas de pago que no sean al contado. Fíjate que hay algunas en el ejemplo, que son condiciones a X días, pero con fecha fija de pago el día Y. Quizás eso te haya despistado.

Estoy mirando un cliente cualquiera del ejemplo: Vinícola del Noroeste s.l., que tiene forma de pago, pagaré a 60 días, con fecha fija el día 15. Veo correcto el vencimiento de las facturas, tanto si dejo como fecha de pago fija el 15, como si se la quito, para que calcule los 60 días a secas. He probado a jugar cambiadno la fecha de emisión de la factura, y veo todo correcto.

Un saludo.

Jona dijo...

Gracias por tu respuesta tan rápida Javier.

Te comento, lo que yo veo en la gente que tienen una forma de pago que no sea "Contado" es "#NAME?"

Intentaré cambiar el código VBA "imprimiendolo" a ver si tengo suerte!

Una pregunta, se puede hacer que en el apartado de clientes y/o facturas se puedan agregar más de una a la vez? cuando trato de pegar varias celdas se me centra solo en una.

Gracias y saludos!

Javier Marco dijo...

El problema probablemente lo tengas con la función FIN.MES que no la tienes habilitada. Mírate por ejemplo, este artículo, que allí se explica como hacer que funcione: calcular vencimientos.

Respecto a la otra pregunta, pues no recuerdo, pero tendrás que probar, ya que hay algunas funciones que se habilitan con determinados eventos, como la selección de celdas, y puede que esto limite ciertas entradas en bloque, pero la verdad es que no recuerdo. Prueba a ver.

Saludos.

Jona dijo...

Hola Javier,

He seguido los pasos y sigue sin funcionar, es probable que sea porque tengo excel 2010 y además en inglés... no pasa nada! "imprimiendo" el código al final he conseguido colocar a modo que me funcione :)

Ahora solo me falta averiguar la parte de pegar varios clientes/facturas en el excel!

Gracias de todas formas!

Javier Marco dijo...

Vale, entonces si tu versión es en inglés, la solución es más sencilla, pues pasa simplemente por cambiar todo lo que aparece como FIN.MES en la fórmula (creo que aparece solo en 2 sitios), por OEMONTH que es su equivalente en la versión inglesa.

Si te fijas, en la fórmula introducida directamente en VBA, aparece FIN.MES, pero ncuando vuelca esa fórmula a tu versión inglesa, no sabe interpretarla, ni la traduce, así que tienes que cambiarla tú directamente en VBA, poniendo OEMONTH en lugar de FIN.MES

Saludos.

Jona dijo...

Efectivamente, con el EOMONTH se me ha arreglado auotmáticamente.

Gracias!

Anónimo dijo...

No se manejar mucho esto, pero tengo un problema, resulta que mi empresa funciona de la siguiente manera:
si llega un factura antes del dia 5 se pagará el dia 10 dentro de 60 dias.
Todas las que lleguen despues del dia 5 se pagarán el dia 25 dentro de 60 dias.
No sé si me explico, no llebamos el control de fecha factura sino la fecha en la que nos llega a la oficina, no sé como poner los intervalos citados en el excel para saber el vencimiento exacto.

Espero que me deis alguna solución.

un saludo!!!

Ricardo dijo...

Hola Javier,
primero agradecerte enormemente tu generosidad al compartir con todas esta información.
Este libro de excel me parece genial,es realmente práctico, lo que ocurre es que no me acaba de funcionar bien.

Me ocurre algo parecido a lo que le ocurre a Jona, si pongo día de pago (en la forma de pago) me aparece #¿NOMBRE? en la celda de FECHA DE VENCIMIENTO.

Tengo Excel 2010, el complemento H.para el análisis activado, y sigue sin funcionar.
Alguna idea de como solucionarlo?
Muchas gracias !

Javier Marco dijo...

Pues yo acabo de probarlo en Excel 2010 y me funciona perfectamente. En Excel 2003 idem de lo mismo (ya no tengo Excel 2007 parar probarlo).

Mírate mi respuesta del día 17 de noviembre del 2011. Es probable que sea porque tienes que entrar en modo VBA y cambiar (desde Edición --> reemplazar) esto:

FIN.MES

Por esto otro:

OEMONTH

Seguramente de esa forma, te funcionará correctamente, aunque ya te digo que en mi versión de Excel 2010 me funciona bien.

Saludos.

Ricardo dijo...

Gracias por tu rápida respuesta!

Efectivamente, parece que era lo que has dicho.
He sustituido FIN.MES por OEMONTH y parece que se ha solucionado.

Seguiré haciendo pruebas ya que cuando fallaba había filas que funcionaban y otras no, y no he conseguido averiguar por que.

Aunque de momento, FUNCIONA.

Mil gracias!

Hace falta mas gente como tu en este mundo !!!

anaf dijo...

Hoja Javier, impresionante este artículo, y muy práctico!! He querido extraer algo que necesito para una hoja de cálculo que tengo pero como no tengo ni idea de programación en VBA no consigo (a pesar de que he impreso el código completo)desfrifrar, en que parte del código indicas que si la fra está vencida, la línea completa se ponga de color azul. Lo he mirado unas tres veces y no lo veo... me rindo. Si me contestas sería fantástico y si no, al menos decirte que ojeo a menudo tus artículos y que te tengo una envidia horrorosa...yo también quiero saber hacer todas esas cosas!! bueno, más que envidia es admiración... Un saludo. Ana

Javier Marco dijo...

Bueno, no es ninguna fórmula, ni programación en VBA. Se trata de darle formato condicional a la celda o al grupo de celdas, que forman la fila.

Si desproteges la hoja (no lleva password), y te sitúas por ejemplo en la celda A5, en el formato condicional, verás que la primera condición es esta (y le damos a la celda color azul celeste):

=SI($G5>0;VERDADERO;FALSO)

Luego, la segunda condición es esta otra (para poner una línea debajo de la celda):

=SI($A5<>"";VERDADERO;FALSO)

Si quieres saber más sobre el formato condicional, mírate este ejeplo en Excel 2003 (en Excel 2007 y 2012 es similar): simultanear filas de colores.

Saludos.

Anónimo dijo...

Hola Javier, te agradezco me ayudes para desproteger las hojas y trabajar en la inclusión de la información contable.
gracias,