Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

PUNTO de VENTA con Excel DETERMINAR NUMERO Factura en EXCEL VBA #1014

Punto de Venta en Excel - determinar número de factura - autonumerico

Punto de Venta en Excel – Autonumerico para Número de Factura

En este vídeo se muestra como determinar en forma automática el número de factura siguiente, es decir se muestra una macro que genera un autonumerico utilizado para establecer el número de factura siguiente de una factura generada con Excel.

Se el mejor manejando Excel, hazlo en forma fácil y sencilla, click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

  
 

Mira la macro en acción, una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

PUNTO DE VENTA en EXCEL – AUTONUMERAR FACTURAS EN EXCEL

En el ejemplo al presionar el botón con el carro de compras, la marco de Excel genera en forma automática el número de factura siguiente, teniendo en cuenta el tipo de factura y número de sucursal, es decir que la macro debe verificar no solo el número de factura, sino también que tipo de factura se seleccionó y la sucursal.

Para ello se conecta a la base de datos de Access, más precisamente con la tabla donde están guardados los comprobantes y cuenta la cantidad de registros, pero que coincidan con el criterio «tipo de factura» y «sucursal», es decir deberá la macro verificar tres campos para determinar el número siguiente de factura, o dicho de otra manera, generar un autonumerico que establezca cual es la factura siguiente según se ingrese tipo de factura y sucursal.

Explicación del código para autonumerar en Excel 

Código del CommandButton

El código que está en el commandbutton si bien es muy parecido al que se encuentre en los Combobox destinados a ingresar el tipo de comprobante o sucursal, es diferente, porque este código agrega en forma predeterminada el tipo de factura y sucursal y luego determina el número de factura con dependiendo del número de sucursal y tipo de factura preestablecida en en este ejemplo es «Sucursal: 1» y «tipo Factura: A»; en los Combobox los datos son variables dependiendo de lo ingresado en ellos.

En primer lugar se dimensionan las variables a usar con los siguientes códigos:
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String, maxCliente As Long

Luego se crea objetos uno con la conexión y el otro que contendrá los datos recuperados de la base de datos que permanecerán en memoria para ser usados, se usa estos códigos:
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Luego se crea el string de conexión a la base de datos, de la siguiente forma:
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «\1000 DBTSPuntoVenta.accdb;»

Luego se crea la sql de consulta, con el código siguiente: el mismo se podría traducir, Cuente del campo ID_Fac de la base de datos DB_Clientes Cuando el Tipo de Factura es A y la Sucursal es igual a 1.

sql = «SELECT COUNT (Id_Fac) FROM DB_Clientes WHERE Tipo_Fac = LIKE ‘A’ AND N_Suc = 1»

Ejecuta la string de consulta con el siguiente código:

Set rs = cn.Execute(sql)

Esta consulta nos dará como resultado un solo dato, que es la cuenta de los registros que coinciden con los criterios nombrados en el párrafo anterior, y se guarda en una variable «maxFac», con el siguiente código:
maxFac = rs.Fields(0).Value

Luego asignamos al Textbox donde se debe mostrar en número de factura el valor de la variable + 1, lo que equivale a decir el número de factura siguiente y se asignan a los Textbox los valores predeterminados de tipo de Factura y Sucursal, en este caso «A» y «1» respectivamente, se usa el código siguiente:

UserForm1.TextBox11 = maxFac + 1

UserForm1.ComboBox1 = «A»
UserForm1.ComboBox2 = 1

En el Combobox el código es prácticamente igual con algunas diferencias:

Primero se dimensionan las variables, crean objetos, así:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String, maxCliente As Long

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Si el Combobox2, que contiene el número de sucursal = Empty o vacío, le asigna automáticamente la sucursal 1, de la siguiente forma:

If UserForm1.ComboBox2 = Empty Then UserForm1.ComboBox2 = 1

Crea la conexión a la base de datos de Access:
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «\1000 DBTSPuntoVenta.accdb;»

Establece la SQL o string de conexión, si se observa está SQL es distinta a la anterior que es la que está en el Commandbutton, no se establece un nombre o sucursal determinada, sino que depende de lo que ingrese el usuario, es decir del tipo de factura y sucursal ingresada por el operador.

Fíjense que el código en la parte de Where (criterios) es distinto en el primer caso (Command Button) los datos son estáticos y en el segundo depende de lo ingresado por el operador (Combobox):

WHERE Tipo_Fac = LIKE ‘A’ AND N_Suc = 1″

WHERE Tipo_Fac = LIKE ‘» & Me.ComboBox1 & » ‘ AND N_Suc = » & Me.ComboBox2 & » «

El código para determinar la SQL es el siguiente:

sql = «SELECT COUNT (Id_Fac) FROM DB_Fac WHERE Tipo_Fac = LIKE ‘» & Me.ComboBox1 & » ‘ AND N_Suc = » & Me.ComboBox2 & » «

Ejecuta la SQL para obtener el dato que se requiere que es la cantidad de facturaras existentes que cumplan con el criterio de Tipo de Factura ingresado y sucursal ingresada, de la siguiente forma:

Set rs = cn.Execute(sql)

Utiliza el valor determinado y se le suma uno para obtener el número de factura siguiente,  insertando dicho valor en el Textbox, con los siguientes códigos:

maxFac = rs.Fields(0).Value
UserForm1.TextBox11 = maxFac + 1

Descarga el Libro PUNTO DE VENTA EN EXCEL – AUTOMUNERICO FACTURA

Descarga el libro usado en este ejemplo desde el final del post, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Código para Generar un Código Consecutivo para una Factura

Código que se ingresa en Combobox en este Ejemplo

Private Sub ComboBox1_Change()
On Error Resume Next
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String, maxCliente As Long
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
UserForm1.ComboBox2 = 1
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «\1000 DBTSPuntoVenta.accdb;»
sql = «SELECT COUNT (Id_Fac) FROM DB_Fac WHERE Tipo_Fac = LIKE ‘» & Me.ComboBox1 & » ‘ AND N_Suc = » & Me.ComboBox2 & » «
Set rs = cn.Execute(sql)
maxFac = rs.Fields(0).Value
UserForm1.TextBox11 = maxFac + 1
UserForm1.TextBox7.SetFocus
UserForm1.ListBox2.Clear
End Sub

Código que se ingresa en un Botón de Comando

Private Sub CommandButton9_Click()
On Error Resume Next
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String, maxCliente As Long
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0; » & «data source=» & ThisWorkbook.Path & «\1000 DBTSPuntoVenta.accdb;»
sql = «SELECT COUNT (Id_Fac) FROM DB_Clientes WHERE Tipo_Fac = LIKE ‘A’ AND N_Suc = 1»
Set rs = cn.Execute(sql)
maxFac = rs.Fields(0).Value
UserForm1.TextBox11 = maxFac + 1
UserForm1.ComboBox1 = «A»
UserForm1.ComboBox2 = 1
UserForm1.TextBox7.SetFocus
UserForm1.ListBox2.Clear
End Sub



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
no rating based on 0 votes

Entradas relacionadas

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (34)

I’m the proprietor of JustCBD Store brand (justcbdstore.com) and I’m presently aiming to grow my wholesale side of company. It would be great if anybody at targetdomain share some guidance . I thought that the most ideal way to do this would be to talk to vape companies and cbd retailers. I was hoping if anybody could recommend a reputable website where I can purchase Vape Shop B2B Direct Mail List I am presently taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not exactly sure which one would be the most ideal selection and would appreciate any guidance on this. Or would it be simpler for me to scrape my own leads? Ideas?

Responder

Этапы процедуры для организаций и услуга банкротство юридических лиц тема знакома каждому учредителю

Responder

Can I simply say what a comfort to uncover a person that truly knows what they’re discussing over the internet. You actually realize how to bring a problem to light and make it important. A lot more people should check this out and understand this side of your story. I was surprised that you aren’t more popular given that you definitely possess the gift.

Responder

Viagra Purchase In Nz Immassymet https://bbuycialisss.com/# – buy real cialis online Telaseangego Viagra Joven emogue buy cialis online woormuriousy magasin levitra

Responder

Having read this I believed it was really informative. I appreciate you taking the time and energy to put this informative article together. I once again find myself spending way too much time both reading and commenting. But so what, it was still worth it!

Responder

I was very happy to find this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every little bit of it and i also have you saved to fav to look at new stuff in your site.

Responder

After looking over a few of the blog articles on your website, I seriously appreciate your technique of blogging. I book-marked it to my bookmark website list and will be checking back soon. Please visit my website as well and let me know what you think.

Responder

Hello there! This blog post could not be written any better! Going through this article reminds me of my previous roommate! He always kept preaching about this. I am going to send this article to him. Pretty sure he’ll have a good read. Many thanks for sharing!

Responder

Howdy! I could have sworn I’ve visited this blog before but after looking at a few of the posts I realized it’s new to me. Regardless, I’m certainly happy I stumbled upon it and I’ll be book-marking it and checking back often!

Responder

I like looking through a post that will make people think. Also, thanks for permitting me to comment!

Responder

After I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added I receive four emails with the same comment. There has to be an easy method you can remove me from that service? Thanks!

Responder

The very next time I read a blog, I hope that it won’t disappoint me just as much as this one. After all, I know it was my choice to read through, but I truly thought you would have something interesting to say. All I hear is a bunch of crying about something you could possibly fix if you were not too busy seeking attention.

Responder

Excellent article! We will be linking to this great content on our site. Keep up the great writing.

Responder

Nice post. I learn something totally new and challenging on websites I stumbleupon on a daily basis. It’s always useful to read through articles from other writers and practice something from their web sites.

Responder

I couldn’t refrain from commenting. Perfectly written!

Responder

I used to be able to find good advice from your blog articles.

Responder

An outstanding share! I’ve just forwarded this onto a friend who had been conducting a little homework on this. And he actually bought me dinner because I found it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending time to talk about this matter here on your web page.

Responder

I used to be able to find good advice from your blog articles.

Responder

I’m very happy to discover this site. I want to to thank you for your time for this particularly wonderful read!! I definitely appreciated every little bit of it and i also have you saved to fav to check out new things on your blog.

Responder

It’s hard to find well-informed people on this subject, however, you seem like you know what you’re talking about! Thanks

Responder

This is a topic which is near to my heart… Take care! Where are your contact details though?

Responder

Greetings! Very helpful advice in this particular post! It’s the little changes that produce the most important changes. Thanks for sharing!

Responder

There’s certainly a great deal to learn about this subject. I like all of the points you made.

Responder

Saved as a favorite, I love your website!

Responder

It’s hard to come by knowledgeable people for this subject, however, you sound like you know what you’re talking about! Thanks

Responder

Your style is so unique compared to other folks I have read stuff from. Thank you for posting when you’ve got the opportunity, Guess I will just book mark this page.

Responder

Howdy, I do think your site could be having internet browser compatibility problems. When I look at your site in Safari, it looks fine however when opening in Internet Explorer, it has some overlapping issues. I just wanted to give you a quick heads up! Apart from that, great website!

Responder

Good site you’ve got here.. It’s difficult to find excellent writing like yours these days. I really appreciate people like you! Take care!!

Responder

Your style is so unique compared to other folks I have read stuff from. Thank you for posting when you have the opportunity, Guess I’ll just book mark this page.

Responder

When I originally left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added I receive four emails with the exact same comment. Is there a means you are able to remove me from that service? Cheers!

Responder

An impressive share! I have just forwarded this onto a colleague who was conducting a little homework on this. And he in fact ordered me breakfast simply because I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanx for spending the time to discuss this issue here on your website.

Responder

Aw, this was a very good post. Taking the time and actual effort to generate a really good article… but what can I say… I procrastinate a whole lot and don’t seem to get nearly anything done.

Responder

Hello! I realize this is sort of off-topic
but I had to ask. Does building a well-established website like yours take a lot of work?

I am brand new to blogging but I do write in my diary every day.
I’d like to start a blog so I will be able to share
my own experience and feelings online. Please let me know if you have any
suggestions or tips for new aspiring blog owners.
Thankyou!

Responder

I was excited to find this page. I wanted to thank you for ones time due to this wonderful read!! I definitely really liked every little bit of it and i also have you book marked to check out new things on your web site.

Responder