PDA

View Full Version : It is possible deleting rows in lisboxt datagrid with active sheets displayed vba exc



Aihmar
02-03-2018, 04:03 AM
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
02-03-2018, 04:57 AM
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
02-03-2018, 05:36 AM
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
02-03-2018, 05:37 AM
https://www.dropbox.com/s/pdxd9im22qw7u6b/Screenshot%202018-02-03%2014.14.41.png?dl=0

Dave
02-03-2018, 06:36 AM
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
02-03-2018, 07:03 AM
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
02-03-2018, 09:38 AM
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
02-03-2018, 10:48 AM
21527
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
02-03-2018, 10:50 AM
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
02-03-2018, 11:14 AM
21528

Dave
02-04-2018, 09:24 AM
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
02-05-2018, 01:56 AM
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 :friends::bow::yes:beerchug:

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
02-05-2018, 04:12 AM
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
02-05-2018, 04:40 AM
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