Issue with VBA on mandatory fields in worksheet and fields connected to them (also mandatory)

R

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
Hi All,

I have a worksheet which is used as a questionnaire. I have been trying to have an error check on mandatory fields that must be filled out by the user. This part has worked out fine - mostly. But the challenge is with connected fields that are dependent on the response from the user in the first set of mandatory fields. Do note that all the fields in the code are mandatory. I seem to be going in circles trying to find a code that would work in all scenarios.

So to explain this if fields c23 and c24 - (group a) are empty, then data must be pulled from, C25, C27:C29 (group b). Sounds simple, right. when I try to cover all scenarios of whether data in either of these groups is empty or partially empty then the code hits an error. I think a loop to check on which set of fields must be checked depending on either group a or group b have data in all fields, but I am unable to work it out. I seem to also get lost in combining multiple ranges via the For loop. And perhaps there's a smarter way of doing this.

Thank you for your help in advance!

VBA Code: 
Sub startform()
If ThisWorkbook.ConnectionError = False Then

UserForm2.Show

    Dim R1, R2, R3, R4, R5, R6, R7, Multirng, myRange, rng As Range
    Dim cell As Range
    Dim myString As String
    
    Dim ws As Worksheet
    
    Set ws = Sheets("Employee")

        If ws.Range("C3").Value = "" Or _
            ws.Range("C4").Value = "" Or _
            ws.Range("C5").Value = "" Or _
            ws.Range("C6").Value = "Select" Or _
            ws.Range("C8").Value = "Select" Or _
            ws.Range("C11").Value = "Select" Or _
            ws.Range("C13").Value = "" Or _
            ws.Range("C14").Value = "" Or _
            ws.Range("C21").Value = "" Or _
            ws.Range("C22").Value = "" Or _
            ws.Range("C34").Value = "Select" Or _
            ws.Range("C42").Value = "Select" Or _
            ws.Range("C43").Value = "" Or _
            ws.Range("C44").Value = "" Or _
            ws.Range("C45").Value = "" Or _
            ws.Range("C46").Value = "" Then
            
            Set Multirng = Range("C3:C6, C8, C11, C13:C14, C21:C22, C34, C42:C46")
            
            If ws.Range("C23").Value <> "" And ws.Range("C24").Value <> "Select" And ws.Range("C25, C27:C29").Value = "" Then
                
                Set rng = ws.Range("C23:C24")
                
                Set myRange = Union(Multirng, rng)
                
            ElseIf ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25, C27:C29").Value = "" Then

                Set rng = ws.Range("C23:C24")
                
                Set myRange = Union(Multirng, rng)
            
            ElseIf ws.Range("C25, C27:C29").Value = "" Then
                If ws.Range("C23").Value <> "" And ws.Range("C24").Value <> "Select" Or _
                    ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" Or _
                    ws.Range("C23").Value <> "" And ws.Range("C24").Value = "Select" Or _
                    ws.Range("C23").Value = "" And ws.Range("C24").Value <> "Select" Then

                    Set rng = ws.Range("C23:C24")

                    Set myRange = Union(Multirng, rng)
                End If
            
            ElseIf ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25, C27:C29").Value <> "" Then
            
                Set rng = ws.Range("C25, 27:C29")
                
            End If
    
        Else
            
'**Continue to the rest of the code"

        End If
 
'***Loop for listing empty fields in the questionnaire and their values
        
         For Each cell In myRange
         
         If cell = "" Or cell = "Select" Then
             
             myString = myString & cell.Address(0, 0) & ":  " & cell.Offset(0, -1).Value & "," & vbCrLf
             
         End If
         
         Next cell
         
         If Len(myString) = 0 Then
                Call AnsigterSQL
                UserForm1.Show

         Else
            MsgBox "Please complete these empty fields and try again:" & vbCrLf & vbCrLf & Left(myString, Len(myString) - 1), vbInformation, "Mandatory Cells Not Completed"
         End If

End If

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Error number AND description (message text) is always helpful. "I get an error" is of zero help.
Perhaps it is because all of these
R1, R2, R3, R4, R5, R6, R7, Multirng, myRange,
are variants and only rng is declared as a range. Each variable in a multi declaration line must be explicitly typed (declared as a data type) unless you want everything but the last one to be a variant. Then again, maybe that's not the reason...

If you have a lot of scenarios as you say, the only solution I can see is dealing with every possibility by using IF blocks or Select Case blocks.
 
Upvote 0
Micron said:
Error number AND description (message text) is always helpful. "I get an error" is of zero help.
Perhaps it is because all of these
R1, R2, R3, R4, R5, R6, R7, Multirng, myRange,
are variants and only rng is declared as a range. Each variable in a multi declaration line must be explicitly typed (declared as a data type) unless you want everything but the last one to be a variant. Then again, maybe that's not the reason...

If you have a lot of scenarios as you say, the only solution I can see is dealing with every possibility by using IF blocks or Select Case blocks.
Click to expand...
Hi Micron,

First of all thank you for offering help. However, please remember that those like me posting requests for help aren't experts on the subject or at programming. I am a novice trying to learn and improve my knowledge of vba programming. So kindly be patient and constructive in your replies. As negative criticism is of zero help! Secondly, I did read my post again and couldn't really see where I wrote "I get an error" :)

Thank you also for explaining that only rng is declared as a range and the others remain as variants. I had a while ago seen this being done in other codes and thought it was just an easy/convenient way of declaring items. I think this gives you a sense of my novice skills with VBA.


Now coming back to the issues my code was running in to:
  • numerous combinations of nested IF statements and their placement (which comes before and which one after),
  • trouble with assigning ranges and using mixed ranges in if statements resulting in errors - one of them - Runtime error '424' - object required
  • Changes in the For loop to provide different messages based on the empty fields

Nevertheless, for the last 3 hours I tried different combinations with the If statements and also figured I had to build in some sort of error handling for cases where one of the two ranges were equating to Nothing. Finally, after shuffling pieces in the code a bit and more test attempts and combinations the below code works perfectly. Therefore, I am posting it here so it can help anyone else with a similar problem.

VBA Code: 
Sub startform()
If ThisWorkbook.ConnectionError = False Then

UserForm2.Show

    Dim Multirng As Range
    Dim myRange As Range
    Dim rng As Range
    Dim cell As Range
    Dim myString As String
    Dim Err As Boolean
    
    Dim ws As Worksheet
    
    Err = False
    
    Set ws = Sheets("Employee")

        
        If ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25").Value = "" And ws.Range("C27").Value = "" And _
            ws.Range("C28").Value = "" And ws.Range("C29").Value = "" Then

            Set rng = ws.Range("C23:C24")

        ElseIf ws.Range("C23").Value <> "" And ws.Range("C24").Value <> "Select" And ws.Range("C25").Value = "" And ws.Range("C27").Value = "" And _
            ws.Range("C28").Value = "" And ws.Range("C29").Value = "" Then
            
            Set rng = ws.Range("C23:C24")



        ElseIf ws.Range("C23").Value <> "" And ws.Range("C24").Value = "Select" Or _
                ws.Range("C23").Value = "" And ws.Range("C24").Value <> "Select" Then

            If ws.Range("C25").Value = "" And ws.Range("C27").Value = "" And ws.Range("C28").Value = "" And ws.Range("C29").Value = "" Then
                
                    Set rng = ws.Range("C23:C24")

            End If
        
        ElseIf ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25").Value <> "" Or ws.Range("C25").Value = "" And _
                ws.Range("C27").Value <> "" Or ws.Range("C27").Value = "" And ws.Range("C28").Value <> "" Or ws.Range("C28").Value = "" And _
                ws.Range("C29").Value <> "" Or ws.Range("C29").Value = "" Then

            Set rng = ws.Range("C25, C27:C29")
        
        End If
        
        If ws.Range("C23").Value <> "" Or ws.Range("C24").Value <> "Select" Then
            If ws.Range("C25").Value <> "" Or ws.Range("C27").Value <> "" Or ws.Range("C28").Value <> "" Or ws.Range("C29").Value <> "" Then
            

            Set rng = ws.Range("C23:C25, C27:C29")
            
            Err = True
            End If
        
        End If
        
        If ws.Range("C3").Value = "" Or _
                ws.Range("C4").Value = "" Or _
                ws.Range("C5").Value = "" Or _
                ws.Range("C6").Value = "Select" Or _
                ws.Range("C8").Value = "Select" Or _
                ws.Range("C11").Value = "Select" Or _
                ws.Range("C13").Value = "" Or _
                ws.Range("C14").Value = "" Or _
                ws.Range("C21").Value = "" Or _
                ws.Range("C22").Value = "" Or _
                ws.Range("C34").Value = "Select" Or _
                ws.Range("C42").Value = "Select" Or _
                ws.Range("C43").Value = "" Or _
                ws.Range("C44").Value = "" Or _
                ws.Range("C45").Value = "" Or _
                ws.Range("C46").Value = "" Then
                
                Set Multirng = Range("C3:C6, C8, C11, C13:C14, C21:C22, C34, C42:C46")


        End If
        
        If Not Multirng Is Nothing And Not rng Is Nothing Then

            Set myRange = Union(Multirng, rng)
        ElseIf Multirng Is Nothing Then

            Set myRange = rng
        ElseIf rng Is Nothing Then

            Set myRange = Multirng
        End If
        
'***Loop for listing empty fields in the questionnaire and their values
        
         For Each cell In myRange
         
         If cell = "" Or cell = "Select" Then
             
             myString = myString & cell.Address(0, 0) & ":  " & cell.Offset(0, -1).Value & "," & vbCrLf
             
         End If
         
         Next cell
         
         If Len(myString) = 0 Then
                Call AnsigterSQL
                UserForm1.Show

         Else
            If Err = True Then
                MsgBox "Mixed input in Current Residence Address and Danish address." & "If you are not in Denmark, then please only complete fields C23 and C24." _
                & vbCrLf & vbCrLf & "If already in Denmark, please only complete fields C25, C27, C28 and C29 and leave the fields C23 empty and C24 = ""Select""." _
                    , vbInformation, "Mandatory Cells Not Completed"
            
            Else
                MsgBox "Please complete these empty fields and try again:" & vbCrLf & vbCrLf & Left(myString, Len(myString) - 1), vbInformation, "Mandatory Cells Not Completed"
            End If
            
         End If

End If

End Sub


Thank you.
 
Upvote 0
Solution
REUBEN said:
when I try to cover all scenarios of whether data in either of these groups is empty or partially empty then the code hits an error.
Click to expand...
That's where I got the sense there was an error. Regardless, my intent was to only convey the notion that such information isn't of much use and no disrespect was intended. I myself am only dabbling in Excel vba so don't think of me as some sort of expert here (my strength is in Access).

I can't tell from your post why you think my post was the solution and can only think it was because of the variants. You didn't have to put each on their own line - this would OK and perhaps more concise:
Dim Multirng As Range, myRange As Range, rng As Range, cell As Range
I also put dim statements all together for the sake of easier troubleshooting. AFAIK, they are not 'executed' as lines but are loaded into memory as soon as code runs regardless of where you put them. F'rinstance, if you put
If 1=1 then
Dim var As Variant
End If
only the If and End If lines will execute but the value of var can still be returned.
I guess if all those range values really matter, you can only accomplish the goal in the fashion you've chosen but it sure looks complicated. I might have written it thus:
VBA Code: 
Option Explicit

Sub startform()
Dim Multirng As Range, myRange As Range, rng As Range, cell As Range
Dim myString As String
Dim Err As Boolean
Dim ws As Worksheet
   
If ThisWorkbook.ConnectionError = False Then
   UserForm2.Show
       
   Err = False
       
   Set ws = Sheets("Employee")
           
   If ws.Range("C23") = "" And ws.Range("C24") = "Select" And ws.Range("C25") = "" And ws.Range("C27") = "" And _
       ws.Range("C28") = "" And ws.Range("C29") = "" Then
       Set rng = ws.Range("C23:C24")
   
   ElseIf ws.Range("C23") <> "" And ws.Range("C24") <> "Select" And ws.Range("C25") = "" And ws.Range("C27") = "" And _
       ws.Range("C28") = "" And ws.Range("C29") = "" Then
       Set rng = ws.Range("C23:C24")
   
   ElseIf ws.Range("C23") <> "" And ws.Range("C24") = "Select" Or _
      ws.Range("C23") = "" And ws.Range("C24") <> "Select" Then
   
      If ws.Range("C25") = "" And ws.Range("C27") = "" And ws.Range("C28") = "" And ws.Range("C29") = "" Then
         Set rng = ws.Range("C23:C24")
      End If
           
   ElseIf ws.Range("C23") = "" And ws.Range("C24") = "Select" And ws.Range("C25") <> "" Or ws.Range("C25") = "" And _
      ws.Range("C27") <> "" Or ws.Range("C27") = "" And ws.Range("C28") <> "" Or ws.Range("C28") = "" And _
      ws.Range("C29") <> "" Or ws.Range("C29") = "" Then
      Set rng = ws.Range("C25, C27:C29")
           
   End If
           
   If ws.Range("C23") <> "" Or ws.Range("C24") <> "Select" Then
      If ws.Range("C25") <> "" Or ws.Range("C27") <> "" Or ws.Range("C28") <> "" Or ws.Range("C29") <> "" Then
         Set rng = ws.Range("C23:C25, C27:C29")
         Err = True
      End If
   End If
           
   If ws.Range("C3").Value = "" Or ws.Range("C4") = "" Or ws.Range("C5") = "" Or ws.Range("C6") = "Select" Or _
      ws.Range("C8") = "Select" Or ws.Range("C11") = "Select" Or ws.Range("C13") = "" Or ws.Range("C14") = "" Or _
      ws.Range("C21") = "" Or ws.Range("C22") = "" Or ws.Range("C34") = "Select" Or ws.Range("C42") = "Select" Or _
      ws.Range("C43") = "" Or ws.Range("C44") = "" Or ws.Range("C45") = "" Or ws.Range("C46") = "" Then
         Set Multirng = Range("C3:C6, C8, C11, C13:C14, C21:C22, C34, C42:C46")
   End If
           
   If Not Multirng Is Nothing And Not rng Is Nothing Then
      Set myRange = Union(Multirng, rng)
   ElseIf Multirng Is Nothing Then
      Set myRange = rng
   ElseIf rng Is Nothing Then
      Set myRange = Multirng
   End If
           
   '***Loop for listing empty fields in the questionnaire and their values
   For Each cell In myRange
      If cell = "" Or cell = "Select" Then
         myString = myString & cell.Address(0, 0) & ":  " & cell.Offset(0, -1).Value & "," & vbCrLf
      End If
   Next cell
            
   If Len(myString) = 0 Then
      Call AnsigterSQL
      UserForm1.Show
   Else
      If Err = True Then
         MsgBox "Mixed input in Current Residence Address and Danish address." & "If you are not in Denmark, then please only complete fields C23 and C24." _
         & vbCrLf & vbCrLf & "If already in Denmark, please only complete fields C25, C27, C28 and C29 and leave the fields C23 empty and C24 = ""Select""." _
         , vbInformation, "Mandatory Cells Not Completed"
      Else
         MsgBox "Please complete these empty fields and try again:" & vbCrLf & vbCrLf & Left(myString, Len(myString) - 1), vbInformation, "Mandatory Cells Not Completed"
      End If
            
   End If
End If

End Sub
 
Upvote 0
Micron said:
That's where I got the sense there was an error. Regardless, my intent was to only convey the notion that such information isn't of much use and no disrespect was intended. I myself am only dabbling in Excel vba so don't think of me as some sort of expert here (my strength is in Access).

I can't tell from your post why you think my post was the solution and can only think it was because of the variants. You didn't have to put each on their own line - this would OK and perhaps more concise:
Dim Multirng As Range, myRange As Range, rng As Range, cell As Range
I also put dim statements all together for the sake of easier troubleshooting. AFAIK, they are not 'executed' as lines but are loaded into memory as soon as code runs regardless of where you put them. F'rinstance, if you put
If 1=1 then
Dim var As Variant
End If
only the If and End If lines will execute but the value of var can still be returned.
I guess if all those range values really matter, you can only accomplish the goal in the fashion you've chosen but it sure looks complicated. I might have written it thus:
Click to expand...
Thanks Micron.

The explanation on the range makes more sense now. I agree putting dim statements in one line helps with troubleshooting. About the code I wouldn't change much as there are reasons why the sequence of things are like they are in addition there is another code behind this one which runs once this code has detected no empty mandatory fields. I was hoping that there could be a shorter method, other than the if statements only. Unsure if a combination of If statements with For loops could have slimmed the code down. There should be an easier way to compare interdependent fields. As you can see just IF statement blocks are confusing to think of and even more difficult to put together. As every time I tested different scenarios of empty fields just between C23:C29 there was just so many combinations to think of.

About marking the answer as the solution, I meant to mark my own reply as the solution. Which is why I put my final code there as well. I couldn't see another way of marking this thread as resolved. The system also asked me if I wanted to mark my own reply as the solution! :)

Thanks again for your follow up comments.

p.s. - On another note, I would love to get better at Access. Again as I am self taught, I have been working on a database in bits pieces over a long time for my team to manage our assignments with different clients who receive various services at different prices. Incidentally, I am working in a team with more senior folks, so they already feel way too insecure with the 200 or so different excel sheets they want to maintain and wont give up. Even though it means the work for keeping track of these assignments including keeping track of their invoices is just too slow, manual and super time consuming. But with my limited knowledge I am unable to show them the fully power and utility a database would give the team.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
498
Peter_SSs
Peter_SSs
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
270
offthelip
O
mehidy1437
  • Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
688
mehidy1437
mehidy1437
J
  • Solved
VBA loop using index and match with columns and rows.
Replies
4
Views
1K
dmt32
D
B
  • Question
How to make my worksheets into workbooks in a designated folder?
Replies
1
Views
464
Herakles
H

Forum statistics

Threads
1,214,646
Messages
6,120,718
Members
448,986
Latest member
andreguerra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top