CODEWITHSUNDEEP
vbaexcel
Sevpoint
Sevpoint

Reputation: 213

Excel VBA: Delete row if match with value in combobox

I have a list of projects on a combobox in a sheet which is Filled by a range from another sheet. I can already add a project. Now I would like to delete a project name if the combobox value is equal to the value in a range on another sheet. I have the following code but it doesn't work: 

Dim ProjNameWS As Worksheet
Set ProjNameWS = Sheets("Project Names")
Dim MainWS As Worksheet
Set MainWS = Sheets("Main")


Application.ScreenUpdating = False


ProjValue = Me.CmbProjName.Value
LR = ProjNameWS.Cells(Rows.Count, 1).End(xlUp).row

For y = 1 To LR



If ProjValue = ProjNameWS.Cells(y, 1) Then

    ProjNameWS.Rows(y).Delete

End If

Next y

Application.ScreenUpdating = True

End Sub

By the way, I am using activeX Combobox in a sheet

enter image description here

This is what the error looks when I removed the On Error Resume Next:

enter image description here enter image description here

Upvotes: 0

Views: 777

Answers (1)

Moacir
Moacir

Reputation: 627

You have to treat it as an object

Replace ProjValue = Me.CmbProjName.Value with ProjValue = ProjNameWS.OLEObjects("CmbProjName").Object.Value and it will work.

Upvotes: 1

Related Questions