Correction in selection in listbox

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
156
I have the userform with = two Listbox(Listbox1 to show all data ;
listbox2=to apresent the selection made in listbox1);
a combobox(combobox1= to show a especif month) and
five CommandButton=
Cmd1=show all data of all month of the year;
Cmd2=to correct the selection >Re_Edit;
Cmd3=to delete selection>Delete;
Cmd4=Clear all boxes> Clear; and
Cmd5=Exit userform)
And Other Userform (userform2) to correct

The goal is
Whenever I select a row in listbox1, appear in listbox2 the selection.This listbox2 is for just one selection.
When I want to correct an error in a certain selection, I use the command (Cmd2) that opens another userform (Userform2), with the respective boxes and commandButton to save.
>>And here I wanted that the correction appear in listbox2 in userform1. How can I do that?

abaixo segue o codigo do Userform1 e Userform2, agradecendo antecipadamente a ajuda que me possam dar. Obrigado
VBA Code:
Option Explicit

Private Sub CheckBox1_Click() 'open Textbox
With CheckBox1


   If CheckBox1.Value = True Then
     Cbo1.Value = ""
     ListBox1.Clear
     cboTxtBackColorTrue  'modulo to change backcolor True
   Else
     cboTxtBackColorFalse 'modulo to change backcolor False
   End If
End With
TextBox1.Value = ""
End Sub

Private Sub Cmd1_Click() 'show all data of all month

Cbo1.Value = ""
ListBox1.Clear
TextBox1.Value = ""

Application.ScreenUpdating = False
With Sheet2
     ListBox1.ColumnCount = 7
     ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value
End With

Application.ScreenUpdating = True

End Sub

Private Sub cbo1_Click() 'show data of especific month choose
ListBox1.Clear
TextBox1.Value = ""

On Error Resume Next
Application.ScreenUpdating = False

With Sheet2
Sheets("sheet2").Range("k2").Value = Cbo1.Value

ComboChooseMonths   'modulo Advance Data    ' [ Sheets("sheet1").Range("A1:G10000").AdvancedFilter Action:=xlFilterCopy, _
                                            ' CriteriaRange:=Sheets("sheet2").Range("k1:k2"), CopyToRange:=Sheets("sheet2").Range( _
                                            ' "A1:G1"), Unique:=False ]

End With

ListBox1.ColumnCount = 7
ListBox1.List = Sheets("Sheet2").Range("A1:G" & Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row).Value

Application.ScreenUpdating = True
End Sub

Private Sub TextBox1_Change()   'To choose value to be corrected

Application.ScreenUpdating = False

ListBox1.Value = ""
Cbo1.Value = ""

On Error Resume Next

With Sheets("Sheet2")
   Sheets("Sheet2").Range("L2").Value = TextBox1.Value
   ComboChooseValor  ' the same modulo above
   ListBox1.List = Sheets("Sheet2").Range("A1:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With

Application.ScreenUpdating = True

End Sub


Private Sub cmd2_Click() 'Re_Edit
With UserForm2

UserForm2.lblIDorder.Caption = ListBox1.List(ListBox1.ListIndex)
UserForm2.TextBox4.Text = ListBox1.Column(3, ListBox1.ListIndex)
UserForm2.TextBox1.Text = ListBox1.Column(4, ListBox1.ListIndex)
UserForm2.TextBox2.Value = ListBox1.Column(5, ListBox1.ListIndex)
UserForm2.TextBox3.Text = ListBox1.Column(6, ListBox1.ListIndex)
End With

UserForm2.Show

ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value


End Sub

Private Sub cmd3_Click() 'Delete
Dim I As Integer

If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then

      For I = 2 To Range("g10000").End(xlUp).Row
        If Cells(I, 1) = ListBox1.List(ListBox1.ListIndex) Then

            Rows(I).Select
            Selection.Delete
           ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value
        End If
      Next I

End If

End Sub

Private Sub cmd4_Click() 'Clear boxes
      Cbo1.Value = ""
      TextBox1.Value = ""
      CheckBox1.Value = False
      ListBox1.Clear
      ListBox2.Clear
End Sub
Private Sub cmd5_Click() 'Exit from userform
    Unload UserForm1
   
    cmd5.Value = Sheets("sheet2").Range("a2:g10000").ClearContents
    cmd5.Value = Sheets("sheet2").Range("k2").ClearContents
    cmd5.Value = Sheets("sheet2").Range("l2").ClearContents
End Sub


Private Sub ListBox1_Click()  'your code= to show in listbox2 the selection in listbox1

  Dim I As Long
  With ListBox2
  .Clear
    .AddItem
    For I = 0 To 6
      .List(.ListCount - 1, I) = ListBox1.List(ListBox1.ListIndex, I)
    Next I
   
  End With

End Sub

Private Sub UserForm_Initialize()
Me.Cbo1.List = Array("JANEIRO", "FEVEREIRO", "MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", "SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")

End Sub


Userform2 [CODE=vba]
Option Explicit

Private Sub Cmd1_Click() 'SAVE the Re_Edit
Dim I As Integer

With UserForm1.ListBox1
  For I = 2 To Range("g10000").End(xlUp).Row
     If Cells(I, 1) = lblIDorder.Caption Then
        Cells(I, 4) = TextBox4.Text 'tipo Pagtº
        Cells(I, 5) = TextBox1.Text  'tipo Despesa
        Cells(I, 6) = TextBox2.Value  'valores
        Cells(I, 7) = TextBox3.Text   'Obs
     End If
  Next I
End With

MsgBox "saved!", vbInformation
 
Unload Me

With UserForm1
UserForm1.CheckBox1.Value = False
UserForm1.TextBox1.Value = ""
UserForm1.Cbo1.Value = ""
End With
End Sub
[/CODE]
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,119,006
Messages
5,575,523
Members
412,672
Latest member
Tupelo1984
Top