Possible BUG, VBA code reference to Sheets ERROR

[Simon Campbell]

I Have a USERFORM with a CommandButton that deletes a row from a worksheet called ANALYSISDB. The WORKBOOK aslo has a sheets called PLOT. When the DELETE command is run it deletes the row from the PLOT sheet and not the ANALYSISDB sheets, below is the Code I have used. For all other Command Button actions (SAVE,UPDATE) they seem to work properly. Any suggestions.

'DELETE BUTTON CODE
Private Sub cmd_Delete_Click()

Dim x As Long
    Dim Y As Long
    x = ThisWorkbook.Worksheets("ANALYSISDB").Range("A" & Rows.Count).End(xlUp).Row
    For Y = 2 To x
    If ThisWorkbook.Worksheets("ANALYSISDB").Cells(Y, 1).Value = txt_Search.Text Then
    Rows(Y).Delete
    End If
    
    Next Y
    
    'Clear Data Entry Form
            Me.txt_Search.Value = "-"
            Me.txt_Name.Value = "-"
            Me.cmb_Phase.Value = "0"
            Me.txt_Crest.Value = "0"
            Me.txt_GOC.Value = "0"
            Me.txt_HWC.Value = "0"
            Me.txt_OP.Value = "0"
            Me.txt_GasGrad.Value = "0"
            Me.txt_OilGrad.Value = "0"
            Me.txt_WaterGrad.Value = "0"
            Me.txt_RefWell.Value = "-"
            Me.chbDisplay.Value = False
            Me.chbLabel.Value = False
            
            
            
            MsgBox "Data has been deleted", vbInformation
            
            txt_Name.SetFocus
End Sub

 

[FormR]

Hi, a couple of things.

1. You need to qualify which sheet you want the row to be deleted from, else the active sheet will be assumed.
2. When deleting rows in a loop you need to start from the bottom and work up, else when you delete a row all the rows below move up before your code moves to the next one.

You could give this a try instead.

Dim x As Long
Dim Y As Long
With ThisWorkbook.Worksheets("ANALYSISDB")
    x = .Range("A" & .Rows.Count).End(xlUp).Row
    For Y = x To 2 Step -1
        If .Cells(Y, 1).Value = txt_Search.Text Then
            .Rows(Y).Delete
        End If
    Next Y
End With

 

[Simon Campbell]

Hi, a couple of things.

1. You need to qualify which sheet you want the row to be deleted from, else the active sheet will be assumed.
2. When deleting rows in a loop you need to start from the bottom and work up, else when you delete a row all the rows below move up before your code moves to the next one.

You could give this a try instead.

Dim x As Long
Dim Y As Long
With ThisWorkbook.Worksheets("ANALYSISDB")
    x = .Range("A" & .Rows.Count).End(xlUp).Row
    For Y = x To 2 Step -1
        If .Cells(Y, 1).Value = txt_Search.Text Then
            .Rows(Y).Delete
        End If
    Next Y
End With

I tried your code and it didn't seem to have any effect (does Delet the correct row of data BUT doesn't delete a row from the PLOT sheet either so half way there, I use a Double Click function to select the desired row in the list box to be deleted.. perhaps this is the problem. The code I'm using is shown below.. My VBA skills are limited at best so any help/support you can provide will be appreciated.

'DOUBLE CLICK WITHIN LISTBOX TO SELECT DATA
Private Sub lst_AnalysisDB_DblClick(ByVal Cancel As MSForms.ReturnBoolean)


txt_Search.Text = lst_AnalysisDB.Column(0)
If txt_Search.Text = lst_AnalysisDB.Column(0) Then
txt_Name.Text = Me.lst_AnalysisDB.Column(0)
cmb_Phase.Text = Me.lst_AnalysisDB.Column(1)
txt_Crest.Text = Me.lst_AnalysisDB.Column(2)
txt_GOC.Text = Me.lst_AnalysisDB.Column(3)
txt_HWC.Text = Me.lst_AnalysisDB.Column(4)
txt_OP.Text = Me.lst_AnalysisDB.Column(5)
txt_GasGrad.Text = Me.lst_AnalysisDB.Column(6)
txt_OilGrad.Text = Me.lst_AnalysisDB.Column(7)
txt_WaterGrad.Text = Me.lst_AnalysisDB.Column(8)
txt_RefWell.Text = Me.lst_AnalysisDB.Column(9)
chbDisplay.Value = Me.lst_AnalysisDB.Column(10)
chbLabel.Value = Me.lst_AnalysisDB.Column(11)

End If
End Sub

 

[RoryA]

The previous code is logically exactly the same as your original code, other than the fact it specifically deletes from the ANALYSISDB sheet. If nothing is being deleted, it means that nothing in column A on that sheet matches (and it has to match exactly) the text in the txt_Search control.

 

[RoryA]

Please note:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: ListBox selection referes to wrong worksheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Simon Campbell]

The previous code is logically exactly the same as your original code, other than the fact it specifically deletes from the ANALYSISDB sheet. If nothing is being deleted, it means that nothing in column A on that sheet matches (and it has to match exactly) the text in the txt_Search control.

mmmmm... food for thought, but seeing as the UPDATE command works then the

txt_Search

control clearly works.. I'm pretty sure the delete worked until I introduced the additional sheet PLOT from where I'm initiating the USERFORM... I've enclosed a screen dump of the ANALYSISDB and UserForm for reference

 

[Simon Campbell]

Please note:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: ListBox selection referes to wrong worksheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I've just tested my theory.. when I delete the PLOT Sheet, initiate the USERFORM from the ANALYSISDP sheet the DELETE command button works correctly.. so where do I ensure the Delete command refers to the correct sheet, as you can see, my code clearly references the ANALYSISDB Sheet

Dim x As Long
Dim Y As Long
    x = ThisWorkbook.Worksheets("ANALYSISDB").Range("A" & Rows.Count).End(xlUp).Row
    For Y = 2 To x
    If ThisWorkbook.Worksheets("ANALYSISDB").Cells(Y, 1).Value = txt_Search.Text Then
    Rows(Y).Delete
    End If
    
    Next Y

 

[RoryA]

You seem to have ignored the corrected code. You should be using:

ThisWorkbook.Worksheets("ANALYSISDB").Rows(Y).Delete

although FormR's version was neater.

 

[FormR]

And you are still looping from top to bottom, rather than bottom up, so you risk not deleting all of the intended rows.

 

[Simon Campbell]

Gents... with a combination of all your suggestions, it now seems to work... as mentioned I'm very new to VBA coding, but getting there .. for your reference here's the final code that seems to work (for now)

Many millions of thanks

              Dim x As Long
               Dim y As Long

With ThisWorkbook.Worksheets("ANALYSISDB")
    x = .Range("A" & .Rows.Count).End(xlUp).Row

    For y = x To 2 Step -1
        If .Cells(y, 1).Value = txt_Search.Text Then
            ThisWorkbook.Worksheets("ANALYSISDB").Rows(y).Delete
        End If
    Next y
End With