Our training courses

Book any course as classroom, onsite or online training, or as one-to-one consultancy
Excel Training Basic Excel Advanced Excel VBA Macros Office Scripts Excel Introduction Excel Intermediate Excel Advanced Excel Business Modelling Excel VBA macros Advanced VBA Fast track Excel VBA Basic Office Scripts Advanced Office Scripts Power Platform Training Pure Power BI DAX and fast-track Power Automate Power Apps Introduction to Power BI Adv. Power BI Reports Adv. Power BI Data DAX for Power BI Fast track Power BI Fast track Power BI/DAX Power Automate Desktop Basic Power Automate Advanced Power Automate Fast track Power Automate Power Apps Programming and AI SQL training Visual C# training Python training AI training Introduction to SQL Advanced SQL Fast track SQL Introduction to MySQL Introduction to Visual C# Intermediate C# Fast track C# Introduction to Python Advanced Python Fast track Python Using AI tools Using the OpenAI API SQL Server training Reporting Services Report Builder Integration Services Azure / SSAS Reporting Services Advanced SSRS Fast track SSRS Report Builder Introduction to SSIS Advanced SSIS Fast track SSIS SQL in Azure Data Studio SSAS - Tabular Model
Other training resources

Free resources

Read our blogs, tips and tutorials

Try our exercises or test your skills

Watch our tutorial videos or shorts

Take a self-paced course

Read our recent newsletters

Paid services

License our courseware

Book expert consultancy

Buy our publications

Getting help

Get help in using our site
Our training venues

Where we are

London

Manchester

Where you are

Across the UK

At your site

Online

At your computer
Why we are different

Transparent reviews

551 attributed reviews in the last 3 years

Delivery of courses

Refreshingly small course sizes

Outstandingly good courseware

Whizzy online classrooms

Wise Owl trainers only (no freelancers)

Nicer to work with

Almost no cancellations

We have genuine integrity

We invoice after training

Reliable and established

Review 30+ years of Wise Owl

View our top 100 clients

Search our website

GO
+44 (161) 883 3606 sales@wiseowl.co.uk Web enquiry form
We also send out useful tips in a monthly email newsletter ...

Sign up to newsletter

Our training courses

AI - Articial Intelligence

Power BI and DAX

Other training resources

Tips and tricks

Guides and tutorials

YouTube tutorials

Test your skills

Self-paced courses

Our training venues

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

This page has 5 threads | Add post

Video tutorials

Video tutorials

Finding our videos useful? Please consider helping us to share more!

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

Posted by Andrew Gould on 18 April 2016

Excel VBA Forms Pt 17 - Editing Data

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.

See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.

This video has the following accompanying files:

File name	Type	Description
Profitable Films 2.xlsm	Excel workbook with macros

Click to download a zipped copy of the above files.

There are no exercises for this video.

Making a video bigger

You can increase the size of your video to make it fill the screen like this:

Full screen view

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown above.

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure yoiu're playing your video so that the icons shown above appear, then click on this gear icon.

Choose to change the video quality:

Changing video quality

Click as above to change your video quality.

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Different speeds

Don't choose the HD option shown unless your connection speed is fast enough to support it!

Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.

This page has 5 threads

From:	Avdou
When:	27 Aug 21 at 19:12

Hi Andrew,

Thank you so much for this amazing Userform Tutorials, I just need to ask you, from your experience, what is the recommended number of Controls shall be used in a Single Userform?

From:	Andrew G
When:	30 Aug 21 at 07:53

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!

Reply

Reply

From:	Avdou
When:	24 Aug 21 at 17:24

Hi Andrew,

1. I have alot of controls in the user form which makes it very large and messy, can I split them into related multi page control? or multi user forms?

2. Can a mulitpage control shows or hide another multipage control? is this a recommended approach?

3. If the user is required to enter almost 22 data entry for just one record? shall I split them into related multi page control? or not to use a user form at all and use direct excel sheet in VBA?

From:	Andrew G
When:	25 Aug 21 at 07:44

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!

Reply

Reply

From:	Avdou
When:	27 Dec 20 at 13:43

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

From:	Andrew G
When:	28 Dec 20 at 06:33

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!

Reply

Reply

From:	ColinM
When:	01 Oct 18 at 16:23

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

From:	Andrew G
When:	02 Oct 18 at 07:42

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

Reply

Reply

From:	ColinM
When:	26 Sep 18 at 13:27

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.

From:	Andrew G
When:	01 Oct 18 at 10:37

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!

Reply

Reply

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

Terms & Conditions

End of small page here

Please be aware that our website uses cookies!

I'm OK with this Tell me more ...