VBA User Forms videos | Excel VBA Forms Pt 17 - Editing Data

Hi Avdou,

I don't think that there is a definitive answer to that question. Add as many controls as you need to allow your users to provide the inputs they need. There are several ways to separate controls to reduce the number that are visible at the same time, as we've discussed in other comments.

I hope that helps!

Hi Avdou,

You can certainly split controls using a multi page on the form. There is a video in this series on working with multi page controls.

You can embed a multi page control within a multi page control, or show/hide a multi page control programmatically, or divide your form into separate forms, or just use a worksheet with embedded controls instead. I think that you and your users have to decide on the best approach - what makes life easiest for your users?

I hope that helps!

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!

You're most welcome Colin!  It's nice to know that the videos are still being used years after they were published.  Enjoy Croatia!

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!