(adsbygoogle = window.adsbygoogle || []).push({});



Si estÃĄs trabajando con listbox quizÃĄs quieras aprender mÃĄs sobre este objeto de VBA para Excel, en el link encontrarÃĄs muchos ejemplos que serÃĄn de utilidad relacionados con listbox de Excel.
Si te estÃĄs iniciando en la operaciÃģn de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre Excel que te ayudarÃĄ operar las planillas u hojas de cÃĄlculo, haz 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.
function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}
Se debe descomprimir en cualquier directorio de la PC con la Única condiciÃģn que se encuentren juntos, para que el ejemplo pueda funcionar perfectamente. En el archivo de Excel se muestra un botÃģn que presionando muestra un formulario de VBA, que nos da la opciÃģn a filtrar la base de datos por Cliente y si es necesario se suma un filtro por fechas o rango de fechas.
La macro en primer lugar crea y elimina una hoja denominada reporte que es donde se va a crear el reporte, con el siguiente cÃģdigo
Posteriormente copias los datos filtrados que se estÃĄn mostrando en el listbox a la Hoja de Excel creaada y denominada Reporte, asÃ
For x = 1 To UserForm1.ListBox1.ListCount – 5
a.Cells(x + 2, ÂŦAÂŧ) = ListBox1.List(x, 0)
a.Cells(x + 2, ÂŦBÂŧ) = CDate(ListBox1.List(x, 1))
a.Cells(x + 2, ÂŦCÂŧ) = ListBox1.List(x, 2)
a.Cells(x + 2, ÂŦDÂŧ) = ListBox1.List(x, 3)
a.Cells(x + 2, ÂŦEÂŧ) = ListBox1.List(x, 4)
a.Cells(x + 2, ÂŦFÂŧ) = ListBox1.List(x, 5)
a.Cells(x + 2, ÂŦGÂŧ) = CDec(ListBox1.List(x, 6))
Next
âââââââââââââââââââââââââââââââââââââââââââââ
QuizÃĄ sea de utilidad tambiÃĐn
Como crear una factura con excel, guardarla y enviarla por mail automÃĄticamente
Como enviar mail con archivo Excel y PDF mediante Outlook con Excel
Como hacer un link o hiperlink a google maps con Excel
Luego da formato y arregla el Reporte, cada uno seguramente le agregarÃĄ lo necesario para que se ajusta a la forma de trabajar de cada uno.
Posteriormente se agrega una imagen se la redimensiona y ubica en el lugar deseado con el siguiente cÃģdigo:
Por Último se guarda la hoja y se obtiene el reporte correspondiente, que son los datos Filtrados por Cliente Rango de Fechas que se Exportan a la Hoja Reporte y que fuera creada al principio de la macro, a continuaciÃģn el cÃģdigo completo y seguidamente se expone el link para que se pueda descargar el ejemplo.
CÃģdigo que se inserta en un Formulario de Excel
‘**************https://macrosenexcel.com **** https://youtube.com/programarexcel*********
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
On Error Resume Next
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
If dato2 = Empty Or dato1 = emtpy Then
MsgBox (ÂŦDebe ingresar datos para consulta entre rango de fechasÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
If dato2 < dato1 Then
MsgBox (ÂŦLa fecha final no puede ser mayor a la fecha inicialÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
‘Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem
If dato1 = Empty Or dato2 = Empty Then
For i = 2 To uf
dato0 = CDate(b.Cells(i, 2).Value)
If dato0 >= dato1 And dato0 <= dato2 Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
Else
If dato2 < dato1 Then
MsgBox (ÂŦLa fecha final no puede ser mayor a la fecha inicialÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
For i = 2 To uf
strg = b.Cells(i, 1).Value
dato0 = CDate(b.Cells(i, 2).Value)
If UCase(strg) Like UCase(TextBox1.Value) & ÂŦ*Âŧ And dato0 >= dato1 And dato0 <= dato2 Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
End If
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(ÂŦHoja1Âŧ).Cells(1, ii + 1)
Next ii
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal ImporteÂŧ
For x = 0 To UserForm1.ListBox1.ListCount – 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = Format(tot, Âŧ ÂŦÂŧU$SÂŧÂŧ #,##0.00 ÂŦ)
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal de registros:Âŧ
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = UserForm1.ListBox1.ListCount – 5
Me.ListBox1.ColumnWidths = ÂŦ170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
End Sub
Private Sub CommandButton3_Click()
Unload UserForm1
End Sub
Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
‘Elimina hoja y crea hoja dando el mismo nombre que la eliminada
Sheets(ÂŦReporteÂŧ).Delete
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ÂŦReporteÂŧ
Set a = Sheets(ÂŦReporteÂŧ)
For x = 1 To UserForm1.ListBox1.ListCount – 5
a.Cells(x + 2, ÂŦAÂŧ) = ListBox1.List(x, 0)
a.Cells(x + 2, ÂŦBÂŧ) = CDate(ListBox1.List(x, 1))
a.Cells(x + 2, ÂŦCÂŧ) = ListBox1.List(x, 2)
a.Cells(x + 2, ÂŦDÂŧ) = ListBox1.List(x, 3)
a.Cells(x + 2, ÂŦEÂŧ) = ListBox1.List(x, 4)
a.Cells(x + 2, ÂŦFÂŧ) = ListBox1.List(x, 5)
a.Cells(x + 2, ÂŦGÂŧ) = CDec(ListBox1.List(x, 6))
Next
a.Cells(x + 4, ÂŦAÂŧ) = ListBox1.List(x + 2, 0)
a.Cells(x + 5, ÂŦAÂŧ) = ListBox1.List(x + 3, 0)
a.Cells(x + 4, ÂŦBÂŧ) = ListBox1.List(x + 2, 1)
a.Cells(x + 5, ÂŦBÂŧ) = ListBox1.List(x + 3, 1)
a.Activate
a.Range(ÂŦA1Âŧ) = ÂŦREPORTE DE VENTASÂŧ
a.Range(ÂŦA2Âŧ) = ÂŦCLIENTEÂŧ
a.Range(ÂŦB2Âŧ) = ÂŦFECHAÂŧ
a.Range(ÂŦC2Âŧ) = ÂŦCOMPROBANTEÂŧ
a.Range(ÂŦD2Âŧ) = ÂŦTIPOÂŧ
a.Range(ÂŦE2Âŧ) = ÂŦSUCÂŧ
a.Range(ÂŦF2Âŧ) = ÂŦN COMPÂŧ
a.Range(ÂŦG2Âŧ) = ÂŦIMPORTEÂŧ
uf = a.Range(ÂŦGÂŧ & Rows.Count).End(xlUp).Row
a.Range(ÂŦG2:GÂŧ & uf).NumberFormat = ÂŦ#.#,0Âŧ
a.Range(ÂŦB2:BÂŧ & uf).NumberFormat = ÂŦdd/mm/yyyyÂŧ
a.Range(ÂŦA:GÂŧ).Columns.AutoFit
a.Range(ÂŦA:AÂŧ).ColumnWidth = 31
With a.Range(ÂŦA2:GÂŧ & uf)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
End With
With a.Range(ÂŦA2:GÂŧ & uf)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
End With
With a.Range(ÂŦAÂŧ & uf + 3 & ÂŦ:GÂŧ & uf + 4)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
With a.Range(ÂŦA1:G1Âŧ)
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.RowHeight = 75
.Font.Size = 16
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
path1 = ActiveWorkbook.Path & ÂŦclientes4.jpgÂŧ
Set ran = a.Cells(1, 1)
Set imag = a.Pictures.Insert(path1)
With imag
.Top = ran.Top
‘reducimos el alto y ancho de la foto
.Width = 20
.Height = ActiveCell.RowHeight – 2
.Left = ran.Left
End With
ActiveWorkbook.Save
Unload UserForm1
Sheets(ÂŦReporteÂŧ).Select
MsgBox ÂŦEl reporte se creo con ÃĐxitoÂŧ, vbCritical, ÂŦAVISOÂŧ
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = ÂŦÂŧ Then
Me.ListBox1.RowSource = ÂŦHoja1!A1:GÂŧ & uf
Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
‘Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem
For i = 2 To uf
strg = b.Cells(i, 1).Value
If UCase(strg) Like UCase(TextBox1.Value) & ÂŦ*Âŧ Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(ÂŦHoja1Âŧ).Cells(1, ii + 1)
Next ii
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal ImporteÂŧ
For x = 0 To UserForm1.ListBox1.ListCount – 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = Format(tot, Âŧ ÂŦÂŧU$SÂŧÂŧ #,##0.00 ÂŦ)
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal de registros:Âŧ
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = UserForm1.ListBox1.ListCount – 5
UserForm1.TextBox2 = Clear
UserForm1.TextBox3 = Clear
Me.ListBox1.ColumnWidths = ÂŦ170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
End Sub
Private Sub TextBox2_Change()
If Len(UserForm1.TextBox2) = 10 Then UserForm1.TextBox3.SetFocus
End Sub
Private Sub TextBox3_Change()
If Len(UserForm1.TextBox3) = 10 Then UserForm1.CommandButton2.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, ÂŦ$Âŧ) + 1, InStr(2, uc, ÂŦ$Âŧ) – 2)
With Me.ListBox1
.ColumnCount = 7
.ColumnWidths = ÂŦ170 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
.RowSource = ÂŦHoja1!A1:Âŧ & wc & uf
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
End Sub
CÃģdigo que se inserta en un mÃģdulo
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
On Error Resume Next
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
If dato2 = Empty Or dato1 = emtpy Then
MsgBox (ÂŦDebe ingresar datos para consulta entre rango de fechasÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
If dato2 < dato1 Then
MsgBox (ÂŦLa fecha final no puede ser mayor a la fecha inicialÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
‘Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem
If dato1 = Empty Or dato2 = Empty Then
For i = 2 To uf
dato0 = CDate(b.Cells(i, 2).Value)
If dato0 >= dato1 And dato0 <= dato2 Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
Else
If dato2 < dato1 Then
MsgBox (ÂŦLa fecha final no puede ser mayor a la fecha inicialÂŧ), vbCritical, ÂŦAVISOÂŧ
Exit Sub
End If
For i = 2 To uf
strg = b.Cells(i, 1).Value
dato0 = CDate(b.Cells(i, 2).Value)
If UCase(strg) Like UCase(TextBox1.Value) & ÂŦ*Âŧ And dato0 >= dato1 And dato0 <= dato2 Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
End If
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(ÂŦHoja1Âŧ).Cells(1, ii + 1)
Next ii
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal ImporteÂŧ
For x = 0 To UserForm1.ListBox1.ListCount – 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = Format(tot, Âŧ ÂŦÂŧU$SÂŧÂŧ #,##0.00 ÂŦ)
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal de registros:Âŧ
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = UserForm1.ListBox1.ListCount – 5
Me.ListBox1.ColumnWidths = ÂŦ170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
End Sub
Private Sub CommandButton3_Click()
Unload UserForm1
End Sub
Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
‘Elimina hoja y crea hoja dando el mismo nombre que la eliminada
Sheets(ÂŦReporteÂŧ).Delete
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ÂŦReporteÂŧ
Set a = Sheets(ÂŦReporteÂŧ)
For x = 1 To UserForm1.ListBox1.ListCount – 5
a.Cells(x + 2, ÂŦAÂŧ) = ListBox1.List(x, 0)
a.Cells(x + 2, ÂŦBÂŧ) = CDate(ListBox1.List(x, 1))
a.Cells(x + 2, ÂŦCÂŧ) = ListBox1.List(x, 2)
a.Cells(x + 2, ÂŦDÂŧ) = ListBox1.List(x, 3)
a.Cells(x + 2, ÂŦEÂŧ) = ListBox1.List(x, 4)
a.Cells(x + 2, ÂŦFÂŧ) = ListBox1.List(x, 5)
a.Cells(x + 2, ÂŦGÂŧ) = CDec(ListBox1.List(x, 6))
Next
a.Cells(x + 4, ÂŦAÂŧ) = ListBox1.List(x + 2, 0)
a.Cells(x + 5, ÂŦAÂŧ) = ListBox1.List(x + 3, 0)
a.Cells(x + 4, ÂŦBÂŧ) = ListBox1.List(x + 2, 1)
a.Cells(x + 5, ÂŦBÂŧ) = ListBox1.List(x + 3, 1)
a.Activate
a.Range(ÂŦA1Âŧ) = ÂŦREPORTE DE VENTASÂŧ
a.Range(ÂŦA2Âŧ) = ÂŦCLIENTEÂŧ
a.Range(ÂŦB2Âŧ) = ÂŦFECHAÂŧ
a.Range(ÂŦC2Âŧ) = ÂŦCOMPROBANTEÂŧ
a.Range(ÂŦD2Âŧ) = ÂŦTIPOÂŧ
a.Range(ÂŦE2Âŧ) = ÂŦSUCÂŧ
a.Range(ÂŦF2Âŧ) = ÂŦN COMPÂŧ
a.Range(ÂŦG2Âŧ) = ÂŦIMPORTEÂŧ
uf = a.Range(ÂŦGÂŧ & Rows.Count).End(xlUp).Row
a.Range(ÂŦG2:GÂŧ & uf).NumberFormat = ÂŦ#.#,0Âŧ
a.Range(ÂŦB2:BÂŧ & uf).NumberFormat = ÂŦdd/mm/yyyyÂŧ
a.Range(ÂŦA:GÂŧ).Columns.AutoFit
a.Range(ÂŦA:AÂŧ).ColumnWidth = 31
With a.Range(ÂŦA2:GÂŧ & uf)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
End With
With a.Range(ÂŦA2:GÂŧ & uf)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
End With
With a.Range(ÂŦAÂŧ & uf + 3 & ÂŦ:GÂŧ & uf + 4)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
With a.Range(ÂŦA1:G1Âŧ)
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.RowHeight = 75
.Font.Size = 16
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
path1 = ActiveWorkbook.Path & ÂŦclientes4.jpgÂŧ
Set ran = a.Cells(1, 1)
Set imag = a.Pictures.Insert(path1)
With imag
.Top = ran.Top
‘reducimos el alto y ancho de la foto
.Width = 20
.Height = ActiveCell.RowHeight – 2
.Left = ran.Left
End With
ActiveWorkbook.Save
Unload UserForm1
Sheets(ÂŦReporteÂŧ).Select
MsgBox ÂŦEl reporte se creo con ÃĐxitoÂŧ, vbCritical, ÂŦAVISOÂŧ
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = ÂŦÂŧ Then
Me.ListBox1.RowSource = ÂŦHoja1!A1:GÂŧ & uf
Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
‘Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem
For i = 2 To uf
strg = b.Cells(i, 1).Value
If UCase(strg) Like UCase(TextBox1.Value) & ÂŦ*Âŧ Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
End If
Next i
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(ÂŦHoja1Âŧ).Cells(1, ii + 1)
Next ii
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal ImporteÂŧ
For x = 0 To UserForm1.ListBox1.ListCount – 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = Format(tot, Âŧ ÂŦÂŧU$SÂŧÂŧ #,##0.00 ÂŦ)
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 0) = ÂŦTotal de registros:Âŧ
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount – 1, 1) = UserForm1.ListBox1.ListCount – 5
UserForm1.TextBox2 = Clear
UserForm1.TextBox3 = Clear
Me.ListBox1.ColumnWidths = ÂŦ170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
End Sub
Private Sub TextBox2_Change()
If Len(UserForm1.TextBox2) = 10 Then UserForm1.TextBox3.SetFocus
End Sub
Private Sub TextBox3_Change()
If Len(UserForm1.TextBox3) = 10 Then UserForm1.CommandButton2.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets(ÂŦHoja1Âŧ)
uf = b.Range(ÂŦAÂŧ & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, ÂŦ$Âŧ) + 1, InStr(2, uc, ÂŦ$Âŧ) – 2)
With Me.ListBox1
.ColumnCount = 7
.ColumnWidths = ÂŦ170 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 ptÂŧ
.RowSource = ÂŦHoja1!A1:Âŧ & wc & uf
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
End Sub
CÃģdigo que se inserta en un mÃģdulo
‘**************https://macrosenexcel.com **** https://youtube.com/programarexcel*********
#If VBA7 And Win64 Then
‘Si es de 64 bits
Public Declare PtrSafe Function ShellExecute Lib ÂŦshell32.dllÂŧ Alias ÂŦShellExecuteAÂŧ (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
Public Declare PtrSafe Function FindWindow Lib ÂŦUSER32Âŧ Alias ÂŦFindWindowAÂŧ (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Public Declare PtrSafe Function GetWindowLongPtr Lib ÂŦUSER32Âŧ Alias ÂŦGetWindowLongPtrAÂŧ (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
Public Declare PtrSafe Function SetWindowLongPtr Lib ÂŦUSER32Âŧ Alias ÂŦSetWindowLongPtrAÂŧ (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
Public Declare PtrSafe Function DrawMenuBar Lib ÂŦUSER32Âŧ (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function RegOpenKeyA Lib ÂŦadvapire32.dllÂŧ (ByVal hKey As LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As LongPtr
#Else
‘Si es de 32 bits
Public Declare Function ShellExecute Lib ÂŦshell32.dllÂŧ Alias ÂŦShellExecuteAÂŧ (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Public Declare Function FindWindow Lib ÂŦUSER32Âŧ Alias ÂŦFindWindowAÂŧ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function GetWindowLong Lib ÂŦUSER32Âŧ Alias ÂŦGetWindowLongAÂŧ (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib ÂŦUSER32Âŧ Alias ÂŦSetWindowLongAÂŧ (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar Lib ÂŦUSER32Âŧ (ByVal hwnd As Long) As Long
Public Declare Function RegOpenKeyA Lib ÂŦadvapire32.dllÂŧ (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
#End If
Sub muestra1()
UserForm1.Show
End Sub
(adsbygoogle = window.adsbygoogle || []).push({});
If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.
Si te gustÃģ por favor compÃĄrtelo con tus amigos
If you liked please share it with your friends