Excel and VBA help please

[paul_bam]

Hello everyone,

First things first. This is my first post, so a big hello to everyone.

Now on to my problem. I have developed an Excel spreadsheet that has a form. The form was developed using VBA . The form is very simple and has one combo box and two command buttons on it. The combo box get populated from column A of sheet 1 and I achieved this by using the Rowsource from within the properties of the combo box. One of the command buttons is named cancel and I have got this to work fine. However, and this is where I require some help, the other command button is named "Delete" and what I would like it to do is this, whichever value that is selected from the combo box list, then when the user clicks on delete then it would find that value in column A, then select the entire row and finally delete the row.

Any help will be very much appreciated.

Thanks in advance

Paul

 

[VoG]

Right click the 'Delete' button > View Code. The code window should show something like

Private Sub CommandButton1_Click()

End Sub

In between the Private Sub and End Sub insert

Dim MyText As String, Found As Range
MyText = UserForm1.ComboBox1.Text
Set Found = Sheets("Sheet1").Columns(1).Find(what:=MyText)
If Not Found Is Nothing Then Found.EntireRow.Delete

You may need to change the numbers of the UserForm, ComboBox and Sheet to suit.

 

[sykes]

Hi Paul, and welcome to mr Excel!

This code will do what you wish. You'll need to copy it from here, and paste it into your "Delete" button's on_click event.
To get to this event, right-click the button (whilst in the VB windows) and select "View Code".
It should open up a code pane for the button thus:

Private Sub CommandButton1_Click()

End Sub

Paste this code in so that the whole lot looks thus:

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("sheet1").Rows(Application.WorksheetFunction.Match(Me.ComboBox1.Value, Sheets("sheet1").Range("A1:A4"), 0)).EntireRow.Delete
End Sub

My code is for a button called "CommandButton1". However, your's will refer to the name of your button, but this will happen automatically when you right-click and select "View Code"

You'll also need to change the name of the combobox to suit your own, and do the same with the range containing your list.
Finally, unless your list range starts at A1, you'll also need some simple maths to ensure the correct row gets deleted.
All my code does is to find the relative position of the combobox value in the list.

All the best, and hope you'll enjoy Mr Excel!

 

[paul_bam]

Hello,

Thanks for the welcome and thanks for the replies. I have now got the "Delete" button to work fine.

However, what I would like to do now, if possible, is this: When the user clicks on the "Delete" button a message box appears asking the user if they are sure that they would like to delete this record? If the user clicks "Yes" then it completes the delete and closes the form, and if the user clicks "No" then the form just closes.

Thanks for your help, it is very much appreciated.

Paul

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim MyText As String, Found As Range, Response As Integer
MyText = UserForm1.ComboBox1.Text
Response = MsgBox(prompt:="Are you sure you want to delete the row containing " & MyText, Buttons:=vbYesNo)
If Response = vbYes Then
    Set Found = Sheets("Sheet1").Columns(1).Find(what:=MyText)
    If Not Found Is Nothing Then Found.EntireRow.Delete
End If
Unload UserForm1
End Sub

 

[sykes]

Private Sub CommandButton1_Click()

If MsgBox("Are you sure you want to delete this entire row?", 52, "Caution - Deleting an entire row from sheet1!") = vbNo Then
      Me.Hide
    Else
        ThisWorkbook.Sheets("sheet1").Rows(Application.WorksheetFunction.Match(Me.ComboBox1.Value, Sheets("sheet1").Range("A1:A4"), 0)).EntireRow.Delete
        Me.Hide
    End If
End Sub

 

[paul_bam]

Hello,

Thanks for the replies. How does the saying go? "We are cooking on gas now!"

OK, now for the big challenge. I also want to add another button on the form. This maybe a little bit more complex, but I will endeavour to explain the purpose of this new button. Lets say, for example, that there are 20 rows in my spreadsheet and with the help of you kind people we can now delete a row by simply running a form, selecting the record from the combobox and clicking on "Delete". However, what I would like to do is to be able to update a record. Each record (or row) has 4 columns (namely) column A to D inclusive. What I would like to happen is that when the user clicks the "Update" button a combobox appears asking the user to select the record they wish to update (similar to how my delete form works now) and then when the user selects the record and click "OK" another form appears with 4 text boxes that contain the cell values relevant to the user selection, then when the user has ammended the details of one or more of these values they can click "OK" and the new values are written into the relevant cells.

I hope that it makes sense.

Thanks in advance

Paul

 

[VoG]

I'm happy to help but I'm not going to write your entire project

Have a look at this http://support.microsoft.com/kb/213749

 

[sykes]

Ditto!!

We'll always help folk out for free, in our spare time, but there's a limit...........

 

[paul_bam]

Hello,

Not quite the entire project . In actual fact, this particular part of the project takes up about 10% of the entire project.

I don't have a problem with creating the forms and adding the buttons, list boxes or combo boxes, it's just the VB code that I struggle with.

Anyway, thanks for your help, it very much appreciated.

Paul