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 una respuesta

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

Comentarios (45)

Having read this I thought it was rather enlightening.
I appreciate you spending some time and energy to put this
information together. I once again find myself personally spending a significant amount of time both reading and
posting comments. But so what, it was still worthwhile!

Responder

Hi there to all, how is the whole thing, I think every
one is getting more from this website, and your views are
fastidious for new people.

Responder

Thanks very interesting blog!

Responder

I like what you guys are up too. This type of clever work and reporting!
Keep up the wonderful works guys I’ve incorporated you guys to our blogroll.

Responder

Does your website have a contact page? I’m having trouble locating it but, I’d like to
send you an e-mail. I’ve got some suggestions for
your blog you might be interested in hearing. Either way, great site
and I look forward to seeing it improve over time.

Responder

What’s up, its pleasant paragraph regarding media print,
we all know media is a impressive source of data.

Responder

I am regular reader, how are you everybody?

This piece of writing posted at this web page is
really good.

Responder

A motivating discussion is definitely worth comment.
I do think that you need to write more about this subject
matter, it may not be a taboo matter but usually people don’t speak about these subjects.

To the next! Cheers!!

Responder

Hi there, I discovered your website via
Google while searching for a related subject, your web site came up,
it looks great. I’ve bookmarked it in my google bookmarks.

Hello there, simply became alert to your weblog thru Google, and found that it’s truly informative.
I’m gonna be careful for brussels. I’ll appreciate if you happen to continue this in future.
Numerous other people will probably be benefited from your writing.

Cheers!

Responder

An intriguing discussion is worth comment. I do think that you should publish more on this subject matter,
it may not be a taboo matter but typically people do not talk
about these topics. To the next! All the best!!

Responder

I’m not sure why but this web site is loading very slow
for me. Is anyone else having this problem or is it a
problem on my end? I’ll check back later and see if the problem still exists.

Responder

whoah this blog is magnificent i like reading your posts.
Stay up the great work! You know, lots of persons are searching round for this info, you
could aid them greatly.

Responder

you are really a excellent webmaster. The website loading pace is incredible.

It seems that you are doing any unique trick.
Furthermore, The contents are masterpiece. you have
performed a fantastic activity in this matter!

Responder

A motivating discussion is definitely worth comment.
There’s no doubt that that you need to write more about this subject matter, it might not
be a taboo matter but usually people don’t talk about such issues.
To the next! Best wishes!!

Responder

I always emailed this weblog post page to all my contacts, because if like to read it then my links
will too.

Responder

I really like what you guys are usually up too. This type of clever work
and exposure! Keep up the terrific works guys I’ve added
you guys to blogroll.

Responder

What’s up everyone, it’s my first go to see at this site, and post
is in fact fruitful in support of me, keep up posting these types of
content.

Responder

Write more, thats all I have to say. Literally, it seems as though you
relied on the video to make your point. You obviously know what youre talking about,
why waste your intelligence on just posting videos to your site when you could be giving us something informative to
read?

Responder

Hello! This is kind of off topic but I need some help from an established
blog. Is it hard to set up your own blog? I’m not very techincal but I can figure things out pretty fast.

I’m thinking about creating my own but I’m not sure where to start.
Do you have any points or suggestions? With thanks

Responder

After I initially commented I appear to
have clicked the -Notify me when new comments
are added- checkbox and now whenever a comment is added I recieve 4 emails with the
same comment. Perhaps there is an easy method you can remove me
from that service? Appreciate it!

Responder

Great goods from you, man. I’ve understand your stuff previous to and you’re
just too great. I really like what you have acquired here, really
like what you’re stating and the way in which you say
it. You make it enjoyable and you still care for to keep it smart.
I cant wait to read much more from you. This is actually a great web site.

Responder

Great article, totally what I needed.

Responder

Somebody essentially assist to make seriously posts I would state.
This is the very first time I frequented your web
page and to this point? I amazed with the analysis you made to create this particular submit amazing.
Excellent activity!

Responder

You could certainly see your expertise within the work you write.
The sector hopes for more passionate writers like
you who are not afraid to mention how they believe.
Always follow your heart.

Responder

What’s up, I want to subscribe for this web site to take
latest updates, so where can i do it please help.

Responder

Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to far added agreeable from you!
By the way, how can we communicate?

Responder

If some one wants expert view on the topic of blogging and site-building then i propose him/her to pay a visit this web site,
Keep up the good work.

Responder

Hi, I check your blogs like every week. Your story-telling style is
witty, keep up the good work!

Responder

Great goods from you, man. I have take into
account your stuff previous to and you’re just
extremely excellent. I actually like what you’ve obtained right here, really like what you’re stating and
the best way in which you say it. You make it enjoyable and you still
care for to stay it wise. I can’t wait to read much
more from you. This is really a terrific site.

Responder

If some one needs expert view concerning blogging afterward
i recommend him/her to visit this website, Keep up the fastidious job.

Responder

I always used to read article in news papers but now
as I am a user of internet thus from now I am using
net for articles or reviews, thanks to web.

Responder

Hi there, just became alert to your blog through Google, and found that it’s really informative.
I’m going to watch out for brussels. I will be grateful if you continue this in future.

Numerous people will be benefited from your writing.
Cheers!

Responder

Hola! I’ve been following your site for a while now and finally got the bravery to go ahead and give you a
shout out from Huffman Texas! Just wanted to tell
you keep up the good job!

Responder

Have you ever thought about publishing an e-book or guest
authoring on other blogs? I have a blog based on the same topics you discuss and would really like to have you share some stories/information. I know my readers would value your work.
If you’re even remotely interested, feel free
to send me an email.

Responder

Hmm is anyone else encountering problems with the pictures on this blog loading?
I’m trying to find out if its a problem on my end or if
it’s the blog. Any suggestions would be greatly appreciated.

Responder

Thanks , I have just been looking for information approximately
this topic for a long time and yours is the greatest I have came upon till now.
But, what in regards to the conclusion? Are you certain in regards to the supply?

Responder

What’s up mates, good piece of writing and pleasant arguments commented
here, I am truly enjoying by these.

Responder

An intriguing discussion is definitely worth comment.
I do think that you ought to publish more about this
subject matter, it might not be a taboo subject
but usually people don’t talk about these issues.
To the next! Many thanks!!

Responder

Hey There. I found your blog using msn. This is an extremely well written article.
I’ll be sure to bookmark it and come back to read more of your useful information. Thanks for the post.
I will definitely comeback.

Responder

Do you mind if I quote a few of your posts as long as I provide credit and sources back to your website?
My website is in the very same niche as yours and my users would truly benefit from some of the
information you present here. Please let me know if this okay with you.
Thanks a lot!

Responder

Thanks for sharing your thoughts on jackfruit nutrition. Regards

Responder

Howdy! I simply would like to offer you a huge thumbs up for
the excellent information you have got here on this post.

I’ll be returning to your blog for more soon.

Responder

I am curious to find out what blog platform
you are utilizing? I’m experiencing some minor security problems with my latest site and I’d
like to find something more risk-free. Do you have
any recommendations?

Responder

A person essentially help to make seriously
articles I would state. This is the very first time I frequented your web page
and to this point? I amazed with the research you made to
create this particular publish extraordinary. Great activity!

Responder

Pretty nice post. I just stumbled upon your weblog and wanted
to say that I’ve really enjoyed surfing around your weblog posts.

In any case I will be subscribing for your feed and
I hope you write once more soon!

Responder