Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como CREAR una FACTURA CANCELAR FACTURA DEVOLVER al STOCK Productos NO Facturados EXCEL#12 #526

Factura en Excel - Devolver al stock productos no facturados

Devolver a Stock Productos no Facturados

En este post como facturar y trabajar descontando del stock o devolviendo productos al stock de artículos no facturados, sugiero previamente mirar los vídeos anteriores de esta saga de vídeos denominada como crear facturas con Excel paso a paso.

Aprende a manejar Excel, 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.

  
 

https://youtu.be/NKIY1LbigNM

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.

Como Facturar con Excel y Descontar de Stock

En este post se muestra una mejora solicitada por un suscriptor de nuestro canal de You Tube el cual solicitaba que se pueda devolver al stock en caso de haber en un primer momento haber empezado una factura por error y no requerirse facturar dicho articulo, ya que al seleccionar la cantidad a facturar descuenta del stock y luego al no facturarlo, queda descontado del stock ni haber sido facturado.

Para evitar este problema que se puede suceder en stock con este caso, se procede a ejecutar una macro en el evento «query close» del formulario de Excel VBA, es decir antes de cerrar el formulario verificará si existe algún registro en el Listbox donde se muestran los artículos a facturar.

En caso que exista un articulo, procederá a preguntar si se desea seguir realizando la factura o no, en caso que no deseemos hacer la factura automáticamente vuelve la cantidad afectada, pero no facturada al stock.

Explicación del código para devolver artículos sin facturar al stock

Primero se recorren las filas del Listbox en caso de ser Empty la variable «regi» cierra el formulario, se usan los siguientes códigos:

For x = 0 To UserForm2.ListBox1.ListCount – 1
regi = regi + 1
Next x
If regi = Empty Then Exit Sub

En caso que la variable «regi» posea algún valor, significa que existe un dato en el listbox que modificó el stock y no se facturó aún, no permite cerrar el formulario, sin que el operador del aplicativo tome una decisión.

La macro pregunta si desea cancelar la factura que se estaba generando y devolver artículos al stock, en caso de respuesta negativa, no cierra el formulario permitiendo seguir realizando la factura, se usa el siguientes código:

respuesta = MsgBox(«¿Existe una factura en ejecución, desea cancelarla y volver los articulos al stock?», vbCritical + vbYesNo, «https://macrosenexcel.com»)
If respuesta = 6 Then

Si la respuesta es positiva, es decir no se desea seguir realizando la factura se procede a devolver al stock las cantidades afectadas, pero aún no facturadas.

Por cada uno de los productos que encuentre en el Listbox determina cual es el código y la cantidad, con el objeto de buscarlo en la base de datos y sumar el importe que existe en la base de datos la cantidad que figura en el Listbox, que previamente fue descontado o afectado al seleccionar el registro y cantidad, pero al no se descontó ya que no se realizó la factura: 

Para recorrer el Listbox y determinar el código del producto y cantidad se usan los siguientes códigos:

For x = 0 To UserForm2.ListBox1.ListCount – 1
cod = UserForm2.ListBox1.List(fila, 0)
can = UserForm2.ListBox1.List(fila, 4)

Luego se procede a buscar el código en la base de datos con el siguiente código:

Set codigo = a2.Range(«B2:B» & uf).Find(cod, LookIn:=xlValues, LookAt:=xlWhole)

Una vez encontrado el código se procede  a sumar la cantidad de stock que figura en la base de datos y la cantidad que se afectó con las intensiones de facturar, pero no se terminó de realizar la factura, los códigos usados son:

If Not codigo Is Nothing Then
stodir = codigo.Row
stoold = a2.Cells(stodir, «G»)
a2.Cells(stodir, «G») = stoold + can
End If
Next x

Descarga el Libro usado en el Ejemplo Como Facturar en Excel – Trabajar con Stock

Descarga el libro de Excel usado como ejemplo, desde el final del post, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Código en evento QueryClose (Antes de Cerrar Formulario)

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error Resume Next
For x = 0 To UserForm2.ListBox1.ListCount – 1
regi = regi + 1
Next x
If regi = Empty Then Exit Sub
respuesta = MsgBox(«¿Existe una factura en ejecución, desea cancelarla y volver los articulos al stock?», vbCritical + vbYesNo, «https://macrosenexcel.com»)
If respuesta = 6 Then
Set a2 = Sheets(«Articulos»)
uf = a2.Range(«A» & Rows.Count).End(xlUp).Row

For x = 0 To UserForm2.ListBox1.ListCount – 1
cod = UserForm2.ListBox1.List(fila, 0)
can = UserForm2.ListBox1.List(fila, 4)

Set codigo = a2.Range(«B2:B» & uf).Find(cod, LookIn:=xlValues, LookAt:=xlWhole)
If Not codigo Is Nothing Then
stodir = codigo.Row
stoold = a2.Cells(stodir, «G»)
a2.Cells(stodir, «G») = stoold + can
End If
Next x
End If
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

Entradas relacionadas

Deja un comentario

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

Comentarios (31)

I’m the co-founder of JustCBD Store company (justcbdstore.com) and I’m presently planning to grow my wholesale side of business. I am hoping someone at targetdomain is able to provide some guidance 🙂 I considered that the best way to accomplish this would be to reach out to vape stores and cbd stores. I was really hoping if anyone could suggest a qualified web site where I can get CBD Shops B2B Database I am currently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence which one would be the very best choice and would appreciate any guidance on this. Or would it be easier for me to scrape my own leads? Ideas?

Responder

ликвидация ООО для учредителя нынче актуальная тема

Responder

This is a topic that’s near to my heart… Many thanks! Where are your contact details though?

Responder

I was more than happy to uncover this website. I want to to thank you for your time just for this wonderful read!! I definitely liked every bit of it and I have you bookmarked to check out new information in your site.

Responder

Good web site you have got here.. It’s difficult to find high quality writing like yours these days. I seriously appreciate people like you! Take care!!

Responder

This site was… how do I say it? Relevant!! Finally I’ve found something which helped me. Cheers!

Responder

Oh my goodness! Impressive article dude! Many thanks, However I am going through troubles with your RSS. I don’t know the reason why I am unable to join it. Is there anybody having identical RSS issues? Anyone who knows the solution will you kindly respond? Thanks!!

Responder

Way cool! Some extremely valid points! I appreciate you penning this post and the rest of the site is really good.

Responder

I blog often and I seriously thank you for your information. The article has truly peaked my interest. I am going to take a note of your site and keep checking for new details about once a week. I subscribed to your RSS feed too.

Responder

Great web site you have here.. It’s difficult to find excellent writing like yours nowadays. I truly appreciate people like you! Take care!!

Responder

Hey there! I just want to give you a big thumbs up for the great info you have here on this post. I will be coming back to your website for more soon.

Responder

After looking into a handful of the blog articles on your website, I truly appreciate your way of blogging. I bookmarked it to my bookmark webpage list and will be checking back soon. Take a look at my website as well and tell me what you think.

Responder

This blog was… how do I say it? Relevant!! Finally I have found something which helped me. Appreciate it!

Responder

Very good article. I certainly appreciate this site. Stick with it!

Responder

Hi! I could have sworn I’ve been to your blog before but after browsing through many of the posts I realized it’s new to me. Anyhow, I’m certainly happy I stumbled upon it and I’ll be bookmarking it and checking back frequently!

Responder

I really like looking through a post that will make people think. Also, thank you for allowing for me to comment!

Responder

An outstanding share! I have just forwarded this onto a coworker who had been doing a little research on this. And he in fact bought me breakfast because I discovered it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanx for spending time to talk about this subject here on your web site.

Responder

Pretty! This has been an extremely wonderful post. Thank you for supplying these details.

Responder

When I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now every 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? Thanks!

Responder

Good info. Lucky me I ran across your blog by chance (stumbleupon). I’ve saved it for later!

Responder

Great blog you have here.. It’s hard to find good quality writing like yours nowadays. I seriously appreciate people like you! Take care!!

Responder

I blog often and I seriously thank you for your content. The article has really peaked my interest. I’m going to book mark your site and keep checking for new information about once per week. I subscribed to your RSS feed too.

Responder

An intriguing discussion is definitely worth comment. I think that you need to publish more on this topic, it may not be a taboo subject but generally people don’t discuss such issues. To the next! Cheers!!

Responder

An impressive share! I’ve just forwarded this onto a friend who has been doing a little research on this. And he in fact ordered me lunch due to the fact that I stumbled upon it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanks for spending the time to discuss this matter here on your blog.

Responder

Way cool! Some very valid points! I appreciate you penning this write-up plus the rest of the website is also very good.

Responder

Hi there! I simply want to offer you a huge thumbs up for the excellent info you have got right here on this post. I am coming back to your website for more soon.

Responder

I was able to find good info from your articles.

Responder

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

Responder

After going over a few of the blog articles on your web site, I truly appreciate your way of writing a blog. I saved as a favorite it to my bookmark site list and will be checking back soon. Please check out my web site as well and let me know your opinion.

Responder

Having read this I thought it was really informative. I appreciate you finding the time and energy
to put this short article together. I once again find myself spending a
significant amount of time both reading and leaving comments.
But so what, it was still worth it! adreamoftrains website hosting

Responder

I have to thank you for the efforts you’ve put in writing this website. I really hope to check out the same high-grade blog posts from you in the future as well. In truth, your creative writing abilities has motivated me to get my own, personal blog now 😉

Responder