Open a userform in diferent sheet where data stay

inactiveUser214710

Board Regular
Joined
Apr 27, 2012
Messages
171
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:
VBA Code:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Looking quickly at your code ..
Rich (BB 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
 
Upvote 0
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
 
Upvote 0
Did you insert reference to sheet2 in your code?
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top