Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel userform vba code to edit and delete

Posted on 2016-09-03
20
Medium Priority
?
860 Views
Last Modified: 2016-09-04
I have a table of absences which is populated through a userform, I would like to be able to add command buttons or toggles to the userform to be able to search the records and edit or delete them. I have attached the file, the form I am working on is called frmabsences which adds records to absence table.

I am new to vb coding. any help appreciated. All details in the attached spreadsheet are fictitious for the purpose of developing the spreadsheet
TestMAMF2.xlsm
0
Comment
Question by:rowlandwiliams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783176
I assume you want to edit and delete items on the Mamf sheet. Which columns on that sheet do you want to be able to edit?
0
 

Author Comment

by:rowlandwiliams
ID: 41783179
Hi Martin,

I want to able to edit columns C, D & E on the Absences sheet. I would like to be able to delete individual records on the same sheet/
0
 

Author Comment

by:rowlandwiliams
ID: 41783181
Just to clarify the delete bit, I would delete an entire row
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783185
OK, here's the delete code. Add a button to frmAbsences named cmdDelete and add this code. I'll be back in a few minutes with the edit code.

Private Sub cmdDelete_Click()

    Dim lngLastRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("B1048576").End(xlUp).Row
    
    If cmbStaffName = "" Then
        MsgBox "Please select a Staff Name to be deleted"
        Exit Sub
    End If
    
    If vbYes = MsgBox("Are you sure you want to delete staff member " & cmbStaffName.Text & "?", vbYesNo) Then
        For lngRow = 4 To lngLastRow
            If Cells(lngRow, "B") = cmbStaffName Then
                Cells(lngRow, "B").EntireRow.Delete
            End If
        Next
    End If
    
End Sub

Open in new window

0
 

Author Comment

by:rowlandwiliams
ID: 41783191
Hi Martin,

The delete code is great thank you, however it deletes any random record with that staff name. There are several records for some staff names, I would like to be able to choose which record to delete.
I have put a list box on the userform,  could I choose which record from the list box? Or scroll through the records in the form to choose the one to delete?
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783194
OK, I thought you meant that you wanted to delete, say, row 12 from the Mamf sheet. Where do the records reside that you want to delete?
0
 

Author Comment

by:rowlandwiliams
ID: 41783197
The records are on the absences worksheet. There is a table on there called absences which the frmabsences adds records to.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783203
OK, so let me double-check. You want to select an entry in Listbox1 on frmAbsence and then either delete that entry from both the list and the Absences sheet or edit that Absences entry on the sheet. Correct?
0
 

Author Comment

by:rowlandwiliams
ID: 41783215
That would be perfect
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783250
One more question. You say you want column "C" to be editable. Are there rules to what it can be changed to?
0
 

Author Comment

by:rowlandwiliams
ID: 41783261
Hi Martin,

Column C is a combobox of Names selected from a Staff List Table -  StaffList[Name] It should be a name in that list.

Columns D & E are dates, I use the frmabsences to enter the dates using a text box and the cdate vba

I hope this all makes sense
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783283
OK, cooking dinner. Will try to finish this later tonight.
1
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41783385
Take a look at my DataBaseForm available free here. It has been downloaded 1000s of times and has many features for working with Excel Databases.

Free DataBaseForm

Take a look and post back if you want help adapting it.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783674
Here's a workbook that allows you to edit and delete entries from the Absences sheet. There is a problem however and that is that deleting records causes problems with your existing formulas.
28967563.xlsm
0
 

Author Comment

by:rowlandwiliams
ID: 41783771
Hi Martin,

Thank you for all the work you've put into this. It is exactly what I'm looking for. When I edit records it doesn't appear to change the right record.

There is an entry for example 5 rows down called Ann Green, I've tried changing the end date and it changes a different record leaving Ann Green record unchanged.

I have a look through the code but I cant really establish what is causing this.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783772
I'll be back in a couple of hours and I'll look at it then.
1
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41783853
Replace the cmdOK_Click sub on frmEdit with this.

Private Sub cmdOK_Click()
Dim lngRowToUpdate As Long

With Sheets("Absences")
    lngRowToUpdate = frmAbsences.ListBox1.ListIndex + 4
    If vbYes = MsgBox("Are you sure you want to update the absence for " _
          & .Cells(lngRowToUpdate, "C") _
          & " for the period " & .Cells(lngRowToUpdate, "D") & " to " _
          & .Cells(lngRowToUpdate, "E"), vbYesNo) Then
        .Cells(lngRowToUpdate, "C") = cboName.Text
        .Cells(lngRowToUpdate, "D") = txtFirst.Text
        .Cells(lngRowToUpdate, "E") = txtLast.Text
    End If
End With

Unload Me

End Sub

Open in new window

BTW I noticed that the code for adding an absence allows the end date to be before the start date. Also if it were my workbook I wouldn't blank out the date when an invalid date is added. IMO it's better to just highlight it. That way the user can see whet he typed in and not think "dumb program, I know I entered a good date".
0
 

Author Comment

by:rowlandwiliams
ID: 41783885
Thanks Martin,

That is absolutely perfect. Thank you so much for all your the time and quick responses :-)
0
 

Author Closing Comment

by:rowlandwiliams
ID: 41783887
Thank you Martin, that has solved all of the problems and the form is working perfectly - Thank you again.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41783889
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question