VBA to check if certain fields have been completed before proceeding with event

[Maleficent]

I have a user form containing some "mandatory fields", I have some current code which saves the file and attaches it to an email, before doing that it checks to see if the mandatory fields have been completed and if not it turns the questions red and pops up a msgbox advising the user to complete the fields

current code:

Please Login or Register  to view this content.

followed by the rest of the code to save/attach to email etc


To develop this further, I've been asked to add in some additional mandatory fields but these are based on dynamic sections - ie we have a section which asks for the number of people involved, if the user selects 1 person, 1 set of additional fields becomes visible asking for info such as name, address etc, if they select 2 then 2 sections come up etc (up to 3)

There are 4 sections like this, each with the option to add details for up to 3 people.

what I've been asked for is to give each of these sections mandatory fields

so if 1 person is added then they must have a title and surname filled out, if 2 people are added then both additional sections must have title and surname, and so on

I can make this work but not without adding tonnes of lines of code which I fear will impact performance

my thoughts were along the lines of:

Please Login or Register  to view this content.

adding the rules for each statement after the else..

but I'm relatively new to VBA and sure this is long winded

is there an efficient way to make this work?

many thanks

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Kaper]

Edit:
Whoops,
I started writing before Mod request.
So I delete my answer :-(

[Kaper]

I hope the new (edited 4pm) thread title is acceptable, so:


I'd try such construction:

Please Login or Register  to view this content.

Or (if it's always E(n) =1 and D(n-3)<=-1) something like:

Please Login or Register  to view this content.

[Maleficent]

Thanks Kaper, I have updated the title as requested!

[Kaper]

I've noted this (some lag in forum updating :-))
so the code is re-published in: https://www.excelforum.com/excel-pro...ml#post5183411

[Maleficent]

Thanks Kaper

I need to test several conditions so..

section 1 - if 1 person, make 2 fields mandatory, if 2 people make 4 fields mandatory, if 3 people make 6 fields mandatory
section 2 - as above
etc for 4 sections

I don't think I can use offset as the three subsections are checked against the same section heading

so to try and simplify...

if A1 = 1 then A3 & A4 are mandatory
if A1 = 2 then A3, A4, A10 & A11 are mandatory
if A1 = 3 then A3, A4, A10, A11, A17 & A18 are mandatory

If A30 = 1 then A33 & A34 are mandatory
etc etc (these cell refs are made up, just to give an idea)

I hope this makes sense!

[Kaper]

Then the first code is probably better.
Moreover, as you want o check several options, I think SELECT CASE instruction would suit better than simple IF

[Maleficent]

Thanks Kaper, I'm not familiar with Select Case - can you elaborate? thanks again!

[Kaper]

See for instance here: https://trumpexcel.com/vba-select-case/ (or other searches with "select case vba tutorial" keywords) how to use one SELECT CASE instead of a set of IF instructions