It is possible deleting rows in lisboxt datagrid with active sheets displayed vba exc

[Aihmar]

Below is my code for delete button

to delete data in data grid list box


thank you very much in advanced

Private Sub cmdbDLT_Click()
Dim i As Integer
Dim wss As Worksheet

For Each wss In ActiveWorkbook.Sheets

 For i = 1 To Range("A600").End(xlUp).Row
    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Select
        Selection.Delete
        
    End If
 Next i
Next wss

End Sub

[Dave]

Yes it is possible. Your query isn't very clear. I assume your code fails on this line..

If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then

Listindex values are like an array and start at 0. Therefore...

If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex + 1) Then

would give U the value of the listbox selection.
U can also do this...

 Rows(i).Delete

HTH. Dave

[Aihmar]

Yes it is possible. Your query isn't very clear. I assume your code fails on this line..

If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then

Listindex values are like an array and start at 0. Therefore...

If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex + 1) Then

would give U the value of the listbox selection.
U can also do this...

 Rows(i).Delete

HTH. Dave

it only delete the active sheet
when I press the button to display another sheet in data grid it will not delete

[Aihmar]

https://www.dropbox.com/s/pdxd9im22q...14.41.png?dl=0

[Dave]

It depends what this is...

Cells(i, 1)

U need to be more specific. Either...

Sheets("yoursheetname").Cells(i, 1)

or....

wss.Cells(i, 1)

Depends what U want to do. Further...

Private Sub cmdbDLT_Click() 
    Dim i As Integer 
    Dim wss As Worksheet 
     
    For Each wss In ActiveWorkbook.Sheets 
         
        For i = 1 To wss.Range("A600").End(xlUp).Row 
            If wss.Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then 
                wss.Rows(i).Delete 
         End If 
        Next i 
    Next wss 
     
End Sub

Again, it depends what your trying to do. Dave

[Aihmar]

It depends what this is...

Cells(i, 1)

U need to be more specific. Either...

Sheets("yoursheetname").Cells(i, 1)

or....

wss.Cells(i, 1)

Depends what U want to do. Further...

Private Sub cmdbDLT_Click() 
    Dim i As Integer 
    Dim wss As Worksheet 
     
    For Each wss In ActiveWorkbook.Sheets 
         
        For i = 1 To wss.Range("A600").End(xlUp).Row 
            If wss.Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then 
                wss.Rows(i).Delete 
         End If 
        Next i 
    Next wss 
     
End Sub

Again, it depends what your trying to do. Dave

i want if click on command button it will show specific sheet/s on the data grid list box. and the delete button we delete specific data on that displayed sheet in data grid list box.

sorry for inconvenience still newbie in vb excel. thank god because here in the forum vba express i am progressively learning.


thank you very much

[Dave]

Ok but where do U get the specific data to be deleted? Is it on each sheet or in 1 location? Have U worked out the listbox loading? The drop box wanted me to sign up... didn't do it so I haven't seen your file. Dave

[Aihmar]

jouna payroll (3).xlsm

Ok but where do U get the specific data to be deleted? Is it on each sheet or in 1 location? Have U worked out the listbox loading? The drop box wanted me to sign up... didn't do it so I haven't seen your file. Dave

here is the file

[Aihmar]

Ok but where do U get the specific data to be deleted? Is it on each sheet or in 1 location? Have U worked out the listbox loading? The drop box wanted me to sign up... didn't do it so I haven't seen your file. Dave

if possible each sheet displayed when the button is press. specific data inside the sheet

[Aihmar]

prog codes.jpg

[Dave]

This code will remove the selected item in the listbox. Note that when U have multiple forms, U should clearly specify all controls etc. with what userform U are referring to ie. instead of Me.Listbox1 it should be Userform1.Listbox1. HTH. Dave
This is code for the jounadata form..

Option Explicit
Dim LastRow As Double
Private Sub cmdbDLT_Click()
'DELETE
If jounadata.ListBox1.ListIndex = -1 Then
MsgBox "Select item to be deleted"
Exit Sub
End If
With jounadata.ListBox1
If UCase(ComboBox1.Value) = UCase("sheet3") Then
If jounadata.ListBox1.ListIndex = 0 Then
MsgBox "You can't remove the header"
Exit Sub
End If
Else
If jounadata.ListBox1.ListIndex < 2 Then
MsgBox "2You can't remove the header"
Exit Sub
End If
End If
End With
Sheets(ComboBox1.Value).Rows(jounadata.ListBox1.ListIndex + 1).Delete
With jounadata.ListBox1
If UCase(ComboBox1.Value) = UCase("sheet3") Then
.ColumnCount = 26
Else
.ColumnCount = 20
End If
.RowSource = Sheets(ComboBox1.Value).Range("A1:S" & LastRow).Address
End With
End Sub
Private Sub ComboBox1_Change()
Dim rng3 As Range
Sheets(ComboBox1.Value).Activate
If jounadata.ComboBox1.Value <> "Sheet3" Then
With Sheets(ComboBox1.Value)
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With jounadata.ListBox1
      .ColumnCount = 20
      .RowSource = Sheets(ComboBox1.Value).Range("A1:S" & LastRow).Address
    End With
Else
LastRow = Worksheets("Sheet3").Range("A1").CurrentRegion.Rows.Count
Set rng3 = Worksheets("Sheet3").Range("A1:X" & LastRow)
With jounadata.ListBox1
      .RowSource = ""
      .ColumnCount = 26
      .List = rng3.Cells.Value
    End With
End If
End Sub
Private Sub CommandButton1_Click()
'MASTER LIST
jounadata.ComboBox1.Value = "Sheet3"
End Sub

[Aihmar]

This code will remove the selected item in the listbox. Note that when U have multiple forms, U should clearly specify all controls etc. with what userform U are referring to ie. instead of Me.Listbox1 it should be Userform1.Listbox1. HTH. Dave
This is code for the jounadata form..

Option Explicit
Dim LastRow As Double
Private Sub cmdbDLT_Click()
'DELETE
If jounadata.ListBox1.ListIndex = -1 Then
MsgBox "Select item to be deleted"
Exit Sub
End If
With jounadata.ListBox1
If UCase(ComboBox1.Value) = UCase("sheet3") Then
If jounadata.ListBox1.ListIndex = 0 Then
MsgBox "You can't remove the header"
Exit Sub
End If
Else
If jounadata.ListBox1.ListIndex < 2 Then
MsgBox "2You can't remove the header"
Exit Sub
End If
End If
End With
Sheets(ComboBox1.Value).Rows(jounadata.ListBox1.ListIndex + 1).Delete
With jounadata.ListBox1
If UCase(ComboBox1.Value) = UCase("sheet3") Then
.ColumnCount = 26
Else
.ColumnCount = 20
End If
.RowSource = Sheets(ComboBox1.Value).Range("A1:S" & LastRow).Address
End With
End Sub
Private Sub ComboBox1_Change()
Dim rng3 As Range
Sheets(ComboBox1.Value).Activate
If jounadata.ComboBox1.Value <> "Sheet3" Then
With Sheets(ComboBox1.Value)
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With jounadata.ListBox1
      .ColumnCount = 20
      .RowSource = Sheets(ComboBox1.Value).Range("A1:S" & LastRow).Address
    End With
Else
LastRow = Worksheets("Sheet3").Range("A1").CurrentRegion.Rows.Count
Set rng3 = Worksheets("Sheet3").Range("A1:X" & LastRow)
With jounadata.ListBox1
      .RowSource = ""
      .ColumnCount = 26
      .List = rng3.Cells.Value
    End With
End If
End Sub
Private Sub CommandButton1_Click()
'MASTER LIST
jounadata.ComboBox1.Value = "Sheet3"
End Sub

thank you very very much!!! no words can express

IT IS POSSIBLE:

1. TO Resize the columns?
2. To print through data grid?
3. To save as different file with filename "DEPARTMENTS" JANUARY 2018, FEBUARY2018 AND SO ON AND SO FORTH?

[Dave]

You are welcome. I had this feeling that your request may evolve. It would likely be better to start a new thread if U have different topic questions. As a suggestion for further posts, please be clear with your requests. As is, I don't understand what U mean resize columns... add/remove columns to what... the listbox, the sheet(s), or..? and it likely would have been handy to know that U needed variable columns before I spent time coding the last part. "Print through data grid" I really can't speculate what U mean by that and I have seen the file? There is lot's of code available on the internet to save files with different names. Thanks for posting your outcome. Dave
https://www.mrexcel.com/forum/excel-questions/1041843-automatically-generating-duplicate-workbooks-renamed-incremental-dates.html

[Aihmar]

You are welcome. I had this feeling that your request may evolve. It would likely be better to start a new thread if U have different topic questions. As a suggestion for further posts, please be clear with your requests. As is, I don't understand what U mean resize columns... add/remove columns to what... the listbox, the sheet(s), or..? and it likely would have been handy to know that U needed variable columns before I spent time coding the last part. "Print through data grid" I really can't speculate what U mean by that and I have seen the file? There is lot's of code available on the internet to save files with different names. Thanks for posting your outcome. Dave
https://www.mrexcel.com/forum/excel-questions/1041843-automatically-generating-duplicate-workbooks-renamed-incremental-dates.html

thank you very much dave. I don't know how to thank you dave

thank you for reminding and sorry I am new to the forum. it's my first time to enter a forum.

ah. what I mean is to save each of my sheets as a separate file. then my original work book clear that data enter in each sheet. for next months data entry