inactiveUser214710
Board Regular
- Joined
- Apr 27, 2012
- Messages
- 171
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
[/CODE]
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
Last edited by a moderator: