VBA code to check if any of a range of controls are blank

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
Hi, I have done some sniffing around to see if I can find solution to my problem. I came close but needed a hand to finish it up for me.

Code:
[COLOR=#101094][FONT=monospace]Dim[/FONT][/COLOR][COLOR=#303336][FONT=monospace] allEmpty [/FONT][/COLOR][COLOR=#101094][FONT=monospace]As [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Boolean
[/FONT][/COLOR][COLOR=#303336][FONT=monospace]allEmpty [/FONT][/COLOR][COLOR=#303336][FONT=monospace]=[/FONT][/COLOR][COLOR=#7D2727][FONT=monospace]True
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]For [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Each[/FONT][/COLOR][COLOR=#303336][FONT=monospace] crtl [/FONT][/COLOR][COLOR=#101094][FONT=monospace]In [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Me[/FONT][/COLOR][COLOR=#303336][FONT=monospace].[/FONT][/COLOR][COLOR=#303336][FONT=monospace]Controls    
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]    If[/FONT][/COLOR][COLOR=#303336][FONT=monospace] crtl[/FONT][/COLOR][COLOR=#303336][FONT=monospace].[/FONT][/COLOR][COLOR=#303336][FONT=monospace]Name [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Like [/FONT][/COLOR][COLOR=#7D2727][FONT=monospace]"txtM*" [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Then
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]       If[/FONT][/COLOR][COLOR=#303336][FONT=monospace] Trim[/FONT][/COLOR][COLOR=#303336][FONT=monospace]([/FONT][/COLOR][COLOR=#303336][FONT=monospace]crtl[/FONT][/COLOR][COLOR=#303336][FONT=monospace].[/FONT][/COLOR][COLOR=#303336][FONT=monospace]Value[/FONT][/COLOR][COLOR=#303336][FONT=monospace]) [/FONT][/COLOR][COLOR=#303336][FONT=monospace]<> [/FONT][/COLOR][COLOR=#7D2727][FONT=monospace]"" [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Then[/FONT][/COLOR][COLOR=#303336][FONT=monospace]           
         allEmpty [/FONT][/COLOR][COLOR=#303336][FONT=monospace]=[/FONT][/COLOR][COLOR=#7D2727][FONT=monospace]False
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]       Exit [/FONT][/COLOR][COLOR=#101094][FONT=monospace]For
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]     End [/FONT][/COLOR][COLOR=#101094][FONT=monospace]If
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]     End [/FONT][/COLOR][COLOR=#101094][FONT=monospace]If
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]Next
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]If[/FONT][/COLOR][COLOR=#303336][FONT=monospace] allEmpty [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Then[/FONT][/COLOR][COLOR=#303336][FONT=monospace]    
   MsgBox [/FONT][/COLOR][COLOR=#7D2727][FONT=monospace]"dont forget .... blablabla"
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]   Exit [/FONT][/COLOR][COLOR=#101094][FONT=monospace]Sub
[/FONT][/COLOR][COLOR=#101094][FONT=monospace]End [/FONT][/COLOR][COLOR=#101094][FONT=monospace]If[/FONT][/COLOR]
What I want to do here, is to check a given range of controls.

My controls are named "reg1" to say "reg20".

But I want to see if from reg4 to reg8, has any blank. The code I see so far all check for all controls. I need just a range

Can someone pull that string up for me?
Thanks in advance
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,914
Office Version
365
Platform
Windows
How about
Code:
    Dim i As Long
    For i = 4 To 8
        If Me.Controls("reg" & i) <> "" Then
            allEmpty = False
            Exit Sub
        End If
    Next i
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
How about
Code:
    Dim i As Long
    For i = 4 To 8
        If Me.Controls("reg" & i) <> "" Then
            allEmpty = False
            Exit Sub
        End If
    Next i

Okay. Cooler than what I was thinking.

So what if I want to test if all is blank?

How will that be written?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
When I say all I mean from reg4 to reg8.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,914
Office Version
365
Platform
Windows
That's what it does, just replace your for each loop with my code
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
That's what it does, just replace your for each loop with my code
Oh okay, the Len function was deceiving me. So I have to change it to make it work.

It's working now.

I appreciate that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,914
Office Version
365
Platform
Windows
:confused:
the Len function was deceiving me. So I have to change it to make it work.
There is no LEN function.

But glad you got it working & thanks for the feedback
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
:confused: There is no LEN function.

But glad you got it working & thanks for the feedback
Sure.

I was having another if condition with

If Len (regX)

It failed but when I used

If regX <> ""

This worked. That was the Len I was referring to.
:cool:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,914
Office Version
365
Platform
Windows
I got you, thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,090,049
Messages
5,412,060
Members
403,410
Latest member
tofoo

This Week's Hot Topics

Top