Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Punto de VENTA con EXCEL FORMATO MONEDA y NUMERO a Listbox #1013

Punto de Venta con Excel - Formato a Listbox Moneda y Numero

Punto de Venta en Excel – Dar Formato Moneda y Numero a Listbox 

En está saga de vídeos se está creando un Punto de Venta en Excel, pero puntualmente en este ejemplo se muestra como dar formato a los datos de los productos facturados y que fueron agregados al Listbox, se dará formato de número con dos decimales a toda la columna del Listbox donde se encuentra el importe del producto, como así también formato moneda a Textbox que muestra el total de la factura.

Aprende Excel rápido, 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 FORMATO MONEDA Y NUMERO A LISTBOX

En este caso la macro da formato al Número al Listbox una vez ingresado el producto facturado, es decir que en el Listbox 14, que es de donde se selecciona el producto a facturar, se encuentra la macro que da formato al Listbox.

Se da formato número con dos decimales a la columna del Listbox que contiene el importe unitario y el total del producto facturado.

También la macro da formato Moneda al Textbox donde se calcula el total de la factura, el formato se puede observa al ingresar un dato nuevo, se verá como queda formateado el Listbox y el Textbox que calcula totales. 

Sugiero descargar el archivo lo que permitirá entender de forma más fácil el ejemplo que se muestra en este post.

 

Explicación del código que otorga formato número y moneda a Listbox y Textbox VBA

Formato Número a Elementos Listbox 

Si bien el código que existe en el Textbox 14 del ejemplo Punto de Venta en Excel es mucho más largo, el código que otorga el formato al Listbox es el siguiente:

UserForm1.ListBox2.AddItem rs.Fields(0).Value
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 1) = rs.Fields(1).Value
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 2) = rs.Fields(2).Value

Como pueden observar en el código anterior la macro carga los datos extraídos de la base de datos en Access, en este caso todos los registros relacionados con el código de barra ingresado en el Textbox 14, en ellos no se otorga ningún formato, ya que son la descripción y la marca del producto.

En los códigos siguientes se da formato número con dos decimales al precio unitario, a la cantidad facturada y al total que es el resultado del cálculo precio por cantidad, se usa el siguiente código:

UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 3) = Format(QV, «#,##0.00;-#.##0,00»)
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 4) = Format((rs.Fields(3).Value), «#,##0.00;-#.##0,00»)
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 5) = Format((rs.Fields(3).Value * QV), «#,##0.00;-#.##0,00»)
mypat = rs.Fields(4).Value

Como se puede observar con valor obtenido de la base de datos de Access, se utiliza el comando Format para dar formato, así:

Format((rs.Fields(3).Value * QV), «#,##0.00;-#.##0,00»)

En este caso (rs.Fields(3).Value * QV) a este importe se le da el formato, recuerden que esta columna del Listbox contiene el precio total facturado que surge del precio unitario del producto seleccionado por la cantidad vendida., luego a ello se le aplica el formato número con dos decimales así:

Format(MiNumero, «#,##0.00;-#.##0,00»)

 

Formato a Textbox

Luego para dar formato a los Textbox que se encuentran al lado del formulario y donde se agrega el subtotal, descuento, impuesto y total de la factura-

En primer lugar se recorre la columna total precio unitario por cantidad de cada producto para obtener el subtotal de la factura, se hace con el siguiente código

For x = 1 To UserForm1.ListBox2.ListCount – 1
t = CDec(UserForm1.ListBox2.List(x, 5))
tot = tot + t
t = 0
Next x

Luego se carga los datos en los respectivos Textbox, dando el formato de número con dos decimales, de la siguiente forma:

UserForm1.TextBox27 = Format(subtototal, «#,##0.00;-#.##0,00»)
UserForm1.TextBox28 = Format(Desc, «#,##0.00;-#.##0,00»)
UserForm1.TextBox29 = Format(Impu, «#,##0.00;-#.##0,00»)

En el último Textbox donde se muestra el total de la factura, se otorga al Textbox el formato moneda con el símbolo Euros, se usa el siguiente código:

UserForm1.TextBox30 = Format(Total, » «»€»» #,##0.00 «)

En este último textbox a través del código siguiente se ajusta el tamaño de la fuente en base al espacio que ocupa el número, que en este caso es el total de la factura.

Si el número es mayor a 100.000, otorga un tamaño de fuente 11 ahora si el el número o importe de la factura es mayor a 10.000 y menor a 100.0000, como hay más espacio en el Textbox se otorga un número de fuente de 16, si el importe de la factura es menor a 10.000, existe mayor espacio aún en el Textbox, por ende el tamaño de la fuente es 16, todo esto se hacer a través del comando Select Case, de la siguiente forma:

Select Case Total
Case Is > 1000000
UserForm1.TextBox30.Font.Size = 11
Case Is > 100000
UserForm1.TextBox30.Font.Size = 16
Case Is < 10000
UserForm1.TextBox30.Font.Size = 22
End Select

Descarga el Libro PUNTO DE VENTA EN EXCEL – FORMATO MONEDA Y NÚMERO A LISTBOX

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 del Listbox del Formulario para Facturar en Post de Venta en Excel

Código que se ingresa en un Userform1 Textbox14

Private Sub TextBox14_AfterUpdate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim cn As ADODB.Connection, rs As ADODB.Recordset, QV As Long
On Error Resume Next

If UserForm1.TextBox14 = Empty Then
UserForm1.Label8.Visible = True ‘hace visible el label
Else
UserForm1.Label8.Visible = False
End If

If Len(UserForm1.TextBox14) > 2 Then
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 Cod_Arti,Detalle_Arti,Marca_Arti,PUV_Arti,Imagen_Arti FROM DB_Articulos WHERE Cod_Arti = ‘» & UserForm1.TextBox14 & «‘»
Set rs = cn.Execute(sql)

‘UserForm1.ListBox2.Clear
UserForm1.ListBox2.ColumnCount = 6
UserForm1.ListBox2.ColumnWidths = «70 pt;280 pt;110 pt;70 pt;70 pt; 110 pt «

‘Adiciona un item al listbox reservado para la cabecera
If UserForm1.ListBox2.ListCount <> 0 Then GoTo salta:
catireg = 0
UserForm1.ListBox2.AddItem
UserForm1.ListBox2.List(0, 0) = «Código»
UserForm1.ListBox2.List(0, 1) = «Articulo»
UserForm1.ListBox2.List(0, 2) = «Marca»
UserForm1.ListBox2.List(0, 3) = «Cantidad»
UserForm1.ListBox2.List(0, 4) = «PU»
UserForm1.ListBox2.List(0, 5) = «Total»
catireg = 1
salta:

‘Set rs = cn.Execute(sql)
If UserForm1.Label21.Caption = Empty Then
QV = 1
Else
QV = UserForm1.Label21.Caption
UserForm1.Label21.Caption = Empty
End If

Msj = «GRACIAS POR SU COMPRA, LO ESPERAMOS DE NUEVO»
If rs.EOF = True Then
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
‘UserForm1.ListBox2.Visible = False
Exit Sub
Else

rs.MoveFirst
Do While Not rs.EOF
UserForm1.ListBox2.AddItem rs.Fields(0).Value
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 1) = rs.Fields(1).Value
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 2) = rs.Fields(2).Value
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 3) = Format(QV, «#,##0.00;-#.##0,00»)
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 4) = Format((rs.Fields(3).Value), «#,##0.00;-#.##0,00»)
UserForm1.ListBox2.List(UserForm1.ListBox2.ListCount – 1, 5) = Format((rs.Fields(3).Value * QV), «#,##0.00;-#.##0,00»)
mypat = rs.Fields(4).Value
Image1.Picture = LoadPicture(mypat)
rs.MoveNext
Loop
End If

Set rs = Nothing
cn.Close
Set cn = Nothing
End If

For x = 1 To UserForm1.ListBox2.ListCount – 1
t = CDec(UserForm1.ListBox2.List(x, 5))
tot = tot + t
t = 0
Next x

subtototal = tot
Desc = CDec(UserForm1.TextBox28)
If IsNull(Desc) Or Desc = Empty Then Desc = 0
Impu = (subtototal – Desc) * 0.16
Total = subtototal – Desc + Impu
UserForm1.TextBox27 = Format(subtototal, «#,##0.00;-#.##0,00»)
UserForm1.TextBox28 = Format(Desc, «#,##0.00;-#.##0,00»)
UserForm1.TextBox29 = Format(Impu, «#,##0.00;-#.##0,00»)
UserForm1.TextBox30 = Format(Total, » «»€»» #,##0.00 «)
Select Case Total
Case Is > 1000000
UserForm1.TextBox30.Font.Size = 11
Case Is > 100000
UserForm1.TextBox30.Font.Size = 16
Case Is < 10000
UserForm1.TextBox30.Font.Size = 22
End Select

UserForm1.TextBox14 = Clear
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.TextBox14.SetFocus

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
5 based on 1 votes

Entradas relacionadas

Deja un comentario

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

Comentarios (33)

I am the proprietor of JustCBD Store brand (justcbdstore.com) and am seeking to grow my wholesale side of company. I am hoping anybody at targetdomain can help me ! I thought that the most ideal way to do this would be to talk to vape shops and cbd retail stores. I was hoping if anyone could suggest a trustworthy web-site where I can purchase CBD Shops Business Data I am already looking at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not exactly sure which one would be the most ideal choice and would appreciate any guidance on this. Or would it be simpler for me to scrape my own leads? Suggestions?

Responder

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

Responder

I’m excited to uncover this web site. I wanted to thank you for your time for this particularly fantastic read!! I definitely appreciated every little bit of it and i also have you book-marked to check out new things on your website.

Responder

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

Responder

Your style is unique in comparison to other people I’ve read stuff from. Many thanks for posting when you have the opportunity, Guess I’ll just book mark this blog.

Responder

Right here is the perfect blog for anyone who really wants to understand this topic. You realize a whole lot its almost hard to argue with you (not that I really would want to…HaHa). You definitely put a new spin on a subject that’s been discussed for decades. Wonderful stuff, just excellent!

Responder

Your style is really unique in comparison to other people I have read stuff from. Many thanks for posting when you’ve got the opportunity, Guess I will just book mark this page.

Responder

It’s hard to come by educated people in this particular topic, however, you sound like you know what you’re talking about! Thanks

Responder

It’s nearly impossible to find knowledgeable people for this topic, however, you seem like you know what you’re talking about! Thanks

Responder

Everything is very open with a very clear explanation of the challenges. It was definitely informative. Your site is very helpful. Thanks for sharing!

Responder

Very nice post. I certainly appreciate this website. Continue the good work!

Responder

Good post. I absolutely love this website. Thanks!

Responder

I was very pleased to discover this great site. I want to to thank you for your time for this wonderful read!! I definitely loved every little bit of it and i also have you bookmarked to look at new information in your site.

Responder

An impressive share! I have just forwarded this onto a friend who had been doing a little research on this. And he actually ordered me dinner due to the fact that I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanx for spending some time to discuss this subject here on your website.

Responder

Howdy! I just would like to give you a big thumbs up for the great information you’ve got right here on this post. I am returning to your blog for more soon.

Responder

This web site certainly has all the information I needed concerning this subject and didn’t know who to ask.

Responder

Spot on with this write-up, I seriously believe this website needs much more attention. I’ll probably be returning to read more, thanks for the info!

Responder

I could not resist commenting. Very well written!

Responder

Right here is the right blog for anyone who wishes to find out about this topic. You realize so much its almost hard to argue with you (not that I really would want to…HaHa). You definitely put a fresh spin on a topic that has been discussed for a long time. Wonderful stuff, just wonderful!

Responder

You’re so cool! I don’t suppose I’ve read something like that before. So wonderful to find somebody with a few original thoughts on this subject matter. Really.. thank you for starting this up. This website is something that’s needed on the web, someone with a little originality!

Responder

I couldn’t refrain from commenting. Perfectly written!

Responder

Good article. I definitely appreciate this website. Thanks!

Responder

Spot on with this write-up, I truly feel this website needs far more attention. I’ll probably be back again to read through more, thanks for the advice!

Responder

After I originally commented I appear to have clicked on the -Notify me when new comments are added- checkbox and from now on each time a comment is added I receive 4 emails with the exact same comment. Is there an easy method you can remove me from that service? Kudos!

Responder

Oh my goodness! Amazing article dude! Thanks, However I am experiencing troubles with your RSS. I don’t know the reason why I am unable to join it. Is there anyone else having the same RSS issues? Anyone that knows the solution can you kindly respond? Thanx!!

Responder

Your style is unique in comparison to other people I’ve read stuff from. I appreciate you for posting when you have the opportunity, Guess I’ll just bookmark this blog.

Responder

I need to to thank you for this fantastic read!! I definitely loved every little bit of it. I have you bookmarked to look at new things you post…

Responder

When I originally left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and from now on every time a comment is added I receive 4 emails with the exact same comment. There has to be a means you are able to remove me from that service? Many thanks!

Responder

The next time I read a blog, I hope that it won’t fail me just as much as this one. After all, Yes, it was my choice to read through, however I genuinely believed you’d have something useful to say. All I hear is a bunch of whining about something you could fix if you weren’t too busy looking for attention.

Responder

It’s remarkable to visit this website and reading the views of
all mates concerning this paragraph, while I am also eager
of getting know-how. adreamoftrains best website hosting

Responder

Hello there I am so thrilled I found your blog, I really found you by accident, while I was researching on Google for
something else, Nonetheless I am here now and would just like to say thanks
a lot for a remarkable post and a all round exciting
blog (I also love the theme/design), I don’t have time to go through
it all at the moment but I have saved it and also added in your RSS feeds,
so when I have time I will be back to read a lot more, Please do
keep up the superb b.

Responder

Hello! This post couldn’t be written any better!

Reading through this post reminds me of my old room mate! He always kept chatting about this.

I will forward this page to him. Pretty sure he will have a good read.
Many thanks for sharing!

Responder

A fascinating discussion is worth comment. I do think that you should publish more about this subject, it may not be a taboo matter but usually people do not discuss such topics. To the next! Many thanks!!

Responder