Open a userform in diferent sheet where data stay

[inactiveUser214710]

Hi everyone I would like to ask the following question: For example I have a userform that has a combobox (Combobox1) a listbox (listbox1) and two Commandbuttons, these one to edit and the other to delete. (cmb1 and cmb2 respectively). But, if I introduce a Command Button in other sheet used to open the form, the criteria works, but the Cmd1 and Cmd2, (to edit and Delete), don't execute the instructions gave when I select the row. Why? Are there any solution for solve this problem? Thank you The code used is as follows: Option Explicit Private Sub combobox1_Change() 'Choose the month ListBox1.RowSource = "" If ComboBox1.Value <> "" Then Sheet2.Range("L2") = ComboBox1 'modulo2 filter advance Call FiltroCbo End If Worksheets("sheet2").Range("L2") = Me.ComboBox1.Value End Sub Private Sub FilterData() Dim Mês As String Dim Desp2 As String Dim myDB As Range 'range data in sheet1 On Error Resume Next With Me If .ComboBox1.ListIndex < 0 Then Exit Sub 'titulos na sheet1 Mês = .ComboBox1.Value End With With ActiveWorkbook.Sheets("sheet1") Set myDB = .Range("A1:H1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row) End With With myDB .AutoFilter ' remove filters .AutoFilter Field:=3, Criteria1:=Mês 'filter data .AutoFilter End With 'Mes Seleccionado Worksheets("sheet2").Range("L2") = Me.ComboBox1.Value End Sub Sub UpdateListbox(ListBox1 As MSForms.ListBox, myDB As Range, columnToList As Long) Dim cell As Range, dataValues As Range On Error Resume Next If myDB.SpecialCells(xlCellTypeVisible).Count > myDB.Columns.Count Then Set dataValues = myDB.Resize(myDB.Rows.Count + 1) ListBox1.Clear 'we clear the listbox before adding new elements For Each cell In dataValues.Columns(columnToList).SpecialCells(xlCellTypeVisible) With Me.ListBox1 .AddItem cell.Value .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value .List(.ListCount - 1, 3) = cell.Offset(0, 3).Value .List(.ListCount - 1, 4) = cell.Offset(0, 4).Value .List(.ListCount - 1, 5) = cell.Offset(0, 5).Value .List(.ListCount - 1, 6) = cell.Offset(0, 6).Value .List(.ListCount - 1, 7) = cell.Offset(0, 7).Value End With Next cell Else ListBox1.Clear 'if no match then clear listbox End If ListBox1.SetFocus 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 ComboBox1.Value = "" ListBox1.RowSource = "" 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 Dim Sb As Double lastRow = Application.WorksheetFunction.CountA(Sheets("sheet1").Range("C:C")) Worksheets("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 With CreateObject("scripting.dictionary") .comparemode = 1 For Each key In dict If Not .exists(key) Then .Add key, Nothing Next End With Me.ComboBox1.List = Array("JANEIRO", "FEVEREIRO", _ "MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", _ "SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO") 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 End Sub

 

[Yongle]

Looking quickly at your code ..

              If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then   Which SHEET contains Cells? Insert sheet reference
                Rows(i).Select    Selection is not required
                Selection.Delete
                Rows(i).Delete  Which SHEET contains this Row? Insert sheet reference
              End If

 

[inactiveUser214710]

Hi Yongle
I had a little problem, and only now i coud answer you.
I made the changes you've proposed, and it works, but while spreadsheet2 is open.
But, if I open the userform, for example in spreadsheet1, the command delete, no longer executes.
It is extremely important to open the form without having to have the "data" sheet selected (sheet2).
I have tried several unsolved things. I appreciate the help you can give me. Thanks
Jdasp

 

[Yongle]

Did you insert reference to sheet2 in your code?

 

[inactiveUser214710]

hi
Thank you.
Efectivamente tenho essa situação espelhada em macro abaixo referenciada, mas não sei como adulterar esta situação, pois esta macro já fora cópia do que retirei da net.
I actually have this situation mirrored in the macro referenced below, but I don't know how to change this situation, because it is already a copy of what I removed from the net.(

). It is parte of combo boxes criteria

Private Sub FilterData()
Dim Mês As String
Dim Desp2 As String
Dim myDB As Range 'range data in ImputData2
'On Error Resume Next
With Me
     If .ComboBox1.ListIndex < 0 Or .ComboBox2.ListIndex < 0 Then Exit Sub
      'titulos na sheet1
      Mês = .ComboBox1.Value
      Desp2 = .ComboBox2.Value
 End With
With Sheet2 ' or ActiveWorkbook.Sheet2 witout with
       Set myDB = .Range("b1:n1").Resize(.Cells(.Rows.count, 2).End(xlUp).Row)
 End With
With myDB
    .AutoFilter ' remove filters
    .AutoFilter Field:=3, Criteria1:=Mês 'filter data
    .SpecialCells(xlCellTypeVisible).AutoFilter Field:=13, Criteria1:=Desp2 'filter data again
          Call UpdateListbox(Me.ListBox1, myDB, 1)
    .AutoFilter
End With

'' I've copy this macro from: https://www.youtube.com/watch?v=avvTwszFVFo

'month selected
   Sheet24.Range("m2") = Me.ComboBox1.Value
   Sheet24.Range("n2") = Me.ComboBox2.Value
     Label10.Caption = Sheet24.Range("n3")
End Sub

 

[inactiveUser214710]

Hi Yongle
Obrigado pela sua ajuda.
Neste ultimo post fui um pouco obtuso e com alguns ajustes, incluido a sua observação, se não haveria alguma referencia à sheet2, obti com sucesso o meu objectivo
De novo muito obrigado
Solved
Djcar