BY CATEGORY▼
VBA CATEGORIES▼
VBA USER FORMS VIDEOS▼
- Excel VBA Forms Pt 1 - Drawing Forms
- Excel VBA Forms Pt 2 - Running and Navigating Forms
- Excel VBA Forms Pt 3 - Adding Code to Forms
- Excel VBA Forms Pt 4 - Validating Controls
- Excel VBA Forms Pt 5 - Validating Forms
- Excel VBA Forms Pt 6 - ComboBox Controls
- Excel VBA Forms Pt 7 - ListBox Controls
- Excel VBA Forms Pt 8 - Option Buttons
- Excel VBA Forms Pt 9 - Spin Buttons
- Excel VBA Forms Pt 10 - Scroll Bar Controls
- Excel VBA Forms Pt 11 - Check Boxes
- Excel VBA Forms Pt 12 - Toggle Buttons
- Excel VBA Forms Pt 13 - Image Controls
- Excel VBA Forms Pt 14 - Multi Page Controls
- Excel VBA Forms Pt 15 - Tab Strip Controls
- Excel VBA Forms Pt 16 - Playing YouTube Videos
- Excel VBA Forms Pt 17 - Editing Data
- Excel VBA Forms Pt 18 - Installing the Date Time Picker and Month View Controls
- Excel VBA Forms Pt 19 - The RefEdit Control
- Excel VBA Forms Pt 20 - Progress Bars
VBA User Forms videos | Excel VBA Forms Pt 17 - Editing Data
Posted by Andrew Gould on 18 April 2016
User forms aren't just for data entry; you can use them to edit and delete data too! This video explains how to create a form which allows the user to browse a set of records held in a worksheet and to edit and delete that data using the form. Along the way, you'll learn about a couple of advanced programming techniques such as declaring Enumerations and Property statements.
You can download any files that you need to follow the video here.
You can increase the size of the video:
You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.
You can also increase the quality of the video:
You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.
Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.
Hi Andrew ... your excel userforms videos are amazing, I have actually learned alot from you.
I just have a small question to you, I need to make a small tool that depends on entering more that 200 records ... is it effecient to let the user to enter the 200 records manually in a userform like the Film Detais Userform or is there any other mechanism.
Another thing if I will do some calculations and generating some reports as per the User Inputs ... shall I need to use userforms or just use the Excel Vba along with ActiveX Controls?
Thank you so much
Hi Avdou,
I think that entering 200 records manually will take a long time whichever method you choose. I would probably not use a user form for this and rely on the user entering data directly into a worksheet. This will allow them to take advantage of copy and paste and the autocomplete feature to speed up data entry. You can use Data Validation to control what values the user enters into specific cells.
I don't think that you need user forms to create reports either - it will almost certainly be easier to provide buttons on the worksheet.
I hope that helps!
Andrew,
Many thanks for the response. On a quick first read it looks as if that is exactly what I need and looks to be simpler and more elegant than the changes I was planning to make.
I am currently sitting on a boat in Croatia so I won’t get a chance to give this a try until I am back home, but I will do so as soon as I can. I hope that revisiting past code did not take up too much of your valuable time.
Once again many thanks,
regards,
Colin Murdoch
You're most welcome Colin! It's nice to know that the videos are still being used years after they were published. Enjoy Croatia!
Andrew,
Many thanks for all the excellent videos. I have been using them to add to my knowledge of VBA.
I have been using the downloaded file accompanying this video as a basis of a project for myself (personal) as a stock system for my model collection. Many of my controls are combo boxes from standard lists. The basic work very well, but I hit on the idea of adding an extra page containing duplicate combo boxes and using these as a basis to set filters. This works OK and a simple subroutine filters the data in the main worksheet. However your "NavigateToRecord" ignores the filters and displays all the hidden rows.
I have started building a single element array to hold the row numbers of the visible rows which works OK, and I am about to write a different sub to replace your navigation routine. However I wondered of there was a slick way your routine could be made to ignore the hidden rows? If so this could save a lot of work.
As this video is now 2 years old, not sure if you will pick this up but thanks in anticipation.
Regards,
Colin Murdoch.
Hi Colin,
If I had to do this I'd probably use a collection to hold the visible ID cells and populate this when the form is loaded. At the top of the form's code page you could add this:
'NEW LINE
Private VisibleIDCells As Collection
'NEW SUBROUTINE
Private Sub PopulateVisibleIDCells()
Set VisibleIDCells = New Collection
Dim r As Range
For Each r In wsFilms.Range("A3", _
wsFilms.Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible)
VisibleIDCells.Add r, r.Address
Next r
End Sub
You can then make a call to the PopulateVisibleIDCells subroutine in the Initialise event of the form.
You'll also need to modify the way the RecordCount property works:
Private Property Get RecordCount() As Long
'ALTERED LINE
RecordCount = wsFilms.Range("A3", _
wsFilms.Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count
End Property
At the beginning of the ReadDetails subroutine, you'll need to change the way you set a reference to the current ID cell:
Private Sub ReadFilmDetails()
'ALTERED LINE
Set CurrentIdCell = VisibleIDCells(CurrentRecordId)
I haven't tested this rigorously (it took long enough to remind myself what I'd done in the first place!) but the basics appear to work when I apply different filters to the list of films.
I hope that points you in the right direction!