‘ * codes of the commands that do not obey, if I choose to use shape on sheet4 to open the form.
Private Sub CommandButton1_Click() ' *RE_EDIT
Dim i As Integer
UserForm2.lblIDOrder.Caption = ListBox1.List(ListBox1.ListIndex)
UserForm2.TextBox5.Text = ListBox1.Column(1, 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)
UserForm2.Show
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then Me.ListBox1.Selected(i) = False
Next i
ListBox2.Value = ""
End Sub
Private Sub CommandButton2_Click() ' *DELETE
Dim i As Integer
If ListBox1.Value = "" Then
MsgBox ("Please fill up, the Months or Value commands, and select them")
Else
If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
For i = 1 To Range("a10000").End(xlUp).Row
If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
Rows(i).Select
Selection.Delete
End If
Next i
End If
End If
With ListBox3
.Clear
.AddItem
For i = 0 To 6
.List(.ListCount - 1, i) = ListBox1.List(ListBox1.ListIndex, i)
Next i
Label5.Caption = " Value was Delete"
End With
ComboBox1.Value = ""
ListBox1.RowSource = ""
ListBox2.Clear
End Sub
Private Sub UserForm_Initialize()
On Error Resume Next
Dim Base As Range
Dim Nome As String
Dim Lh As Long
Dim dict, key
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range("C:C"))
Sheets("sheet1").Range ("c2:c" & lastrow)
dict = Sheets("sheet1").Value
With CreateObject("scripting.dictionary")
.comparemode = 1 'vbtextcompare - case words doesn't matter:apple
For Each key In dict
If Not .exists(key) Then .Add key, Nothing
Next
If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
Sheets("sheet1").Range ("h2:h" & lastrow)
dict = Sheets("sheet1").Value
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each key In dict
If Not .exists(key) Then .Add key, Nothing
Next
If .Count Then Me.ComboBox2.List = Application.Transpose(.keys)
End With
Me.ComboBox1.List = Array("JANEIRO", "FEVEREIRO", _
"MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", _
"SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")
Me.ComboBox2.List = Array("SAUDE", _
"TRANSPORTE", "ALIMENTAÇÃO", "DESP.FIXAS", "IMPOSTOS", "DESP.OCASION", "AAA", "BBB", "CCC")
Call TrueFalseBox 'Modulo3 situação inicial
Lh = Sheet1.Range("A1").CurrentRegion.Rows.Count
Set Base = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Lh, 7)) 'Sheet1.Range("A1").CurrentRegion
Nome = "'" & Sheet1.Name & "'!"
ListBox1.ColumnCount = 7
Sheet2.Range("l2:m2").ClearContents
End Sub
Option Explicit
'userform2
Private Sub CommandButton1_Click() 'Save
Dim I As Integer
Application.ScreenUpdating = False
On Error Resume Next
'Record in listbox1
With userform1.ListBox1
For i = 2 To Range("g10000").End(xlUp).Row
If Cells(i, 1) = lblIDOrder.Caption Then
Cells(i, 2) = TextBox5.Text
Cells(i, 4) = TextBox4.Text
Cells(i, 5) = TextBox1.Text
Cells(i, 6) = TextBox2.Value
Cells(i, 7) = TextBox3.Text
End If
Next i
End With
With userform1
.CheckBox1.Value = False
.TextBox1.Value = ""
.ComboBox1.Value = ""
.ListBox1.RowSource = ""
.Label5.Caption = "Rectified Value"
.ListBox3.RowSource = "sheet2!t2:z2"
End With
‘Record in worksheet
Sheets("Sheet2").Range("T2").Value = lblIDOrder.Caption
Sheets("Sheet2").Range("U2").Value = TextBox5.Value
Sheets("Sheet2").Range("V2").Value = lblMonth.Caption
Sheets("Sheet2").Range("W2").Value = TextBox4.Value
Sheets("Sheet2").Range("X2").Value = TextBox1.Value
Sheets("Sheet2").Range("Y2").Value = TextBox2.Value
Sheets("Sheet2").Range("Z2").Value = TextBox3.Value
'Sheets("Sheet1").Select
Unload Me
'UserForm1.ListBox2.Clear
userform1.ListBox3.Value = ""
userform1.ListBox5.Value = ""
Application.ScreenUpdating = True
End Sub
Private Sub ListBox1_Change()
Dim cell As Range
'On Error Resume Next
'Load to ListBox
For Each cell In Worksheets("Sheet2").Range("t2:z2")
userform1.ListBox1.AddItem cell.Value
Next cell
End Sub
Private Sub UserForm_Initialize()
lblMonth.Caption = userform1.ComboBox1.Value
Sheet2.Range("T2:Z2").ClearContents
End Sub
MODULE
Option Explicit
============
Sub FiltroCbo()
Dim Base As Range
Dim Crt As Range
Dim Filtrada As Range
Dim Nome As String
Dim Lh As Long
Set Base = Sheet1.Range("A1").CurrentRegion
Set Crt = Sheet2.Range("L1:L2")
Base.AdvancedFilter xlFilterCopy, Crt, Sheet2.Range("A1:j1")
Lh = Sheet2.Range("A1").CurrentRegion.Rows.Count
Set Filtrada = Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(Lh, 7))
Nome = "'" & Sheet2.Name & "'!"
userform1.ListBox1.RowSource = Nome & Filtrada.Address
userform1.ListBox1.ColumnHeads = True
If Lh = 1 Then
userform1.ListBox1.ColumnHeads = False
End If
End Sub
