IF "yes" then this range, IF "NO" then this range.

E

EricT

New member
Joined
Mar 22, 2011
Messages
18
Reaction score
0
Points
0
Hey all,
i have a question. I am currently using this code below to make a specified range "needfill" as a setion of a work sheet that needs to be filled out to completion. i want to do the same thing for another set of ranges "IFYES" "IFNO" i want the range set "IFYES" to be required the same as the code below if a box is checked YES. i also want to require another range "IFNO" if the box is checked NO. not sure how to make this one cell $A$28 that has a dropdown list(YES or NO) the deciding value. :confused2:

any help would be greatly appreciated.
Thanks, all. :becky:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rNextCell As Range, bMsg As Boolean
    If Not Intersect(Target, Me.Range("needfill")) Is Nothing Then
        Exit Sub
    End If
    If WorksheetFunction.CountA(Me.Range("needfill")) <> Me.Range("needfill").Cells.Count Then
        bMsg = False
        For Each rNextCell In Me.Range("needfill").Cells
            If Len(rNextCell.Value) = 0 Then
                Application.EnableEvents = False
                rNextCell.Select
                Application.EnableEvents = True
                bMsg = True
                Exit For
            End If
        Next rNextCell
        If bMsg = True Then
            MsgBox "Please fill in required cells before continuing!", vbInformation, "ERROR!"
        End If
    End If
End Sub
 
Hi Eric,

I can't get at this right at the moment, but if no one else gets there before, I'll pick it up later tonight.
 
awesome thank you soo much. if you want i can attach the form im woking on?
Thanks,
 
Here is the attachment, there is a radio button in A28 that i want to be the YES NO dropdown that causes the program to restrict the manditor selection from the ranges i was talking about above.
Thanks,
Eric
 

Attachments

  • Pers. Info Sheet 3-22-11.xls
    86.5 KB · Views: 50
Hi

If you set the properties of the check box you have located at A28 so that the linked cell is A28, then if the box is checked, A28 will be True, if not then it will be false.

Then change most of your code to reference myRange as opposed to needfill, and insert the following into your code

Code: 
Dim myRange As Range
    Set myRange = Range("needfill")
    If Me.Range("A28") Then
        Set myRange = Range("mustfill")
    End If
 
there are two ranges that should be manditory, this will cover one of the ranges if the box is "true" but what if it is unchecked and false? how do i force it to choose the other ranger?
thanks,
E
 
i tried putting the code you porvided in for a check box i created with view>toolbars>control toolbox and i couldnt get it to link A28 with a check for true causing my.range "required" and my.range "notrequired" to be manditor with the true and false for the click. what im looking to do is make that check box togle the code im using for the above section so that when its clicked they need to fill in all the shaded area and when its unclicked they need to fill in only a few boxes. i think it would be better to make it a dropdown list for YES and NO that way it would requrie the user to fill in a response before the above actions took place.
Thanks,
Eric


any help is greatly appreciated.
 
this TEST file is what i have been playing around with, i just remove the code that forces the entry of fields "needfill" earlier in the worksheet and try to make the cell A28 (=DEC) that when YES drop down in selected it is recognized as TRUE and all the shaded areas need filled. but when NO is selected then only 3 of the shaded areas are required to be filled like the "needfill" section.
Thanks,
Eric
 

Attachments

  • TEST.xls
    82 KB · Views: 26
Hi Eric

I modified the code as follows, and it seems to work for me.
I needed to add a line to exit the sub, if the user was trying to change the selection on cell A28 from YES to NO.

I'm not certain I have the alternatives, Needfill and Mustfill in the correct order for YES and NO, but it is easy for you to reverse that.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rNextCell As Range, bMsg As Boolean
    Dim myRange As Range
    If Target.Address = "$A$28" Then Exit Sub
    Set myRange = Range("needfill")
    If Me.Range("A28") = "YES" Then
        Set myRange = Range("mustfill")
    End If
    If Not Intersect(Target, myRange) Is Nothing Then
        Exit Sub
    End If
    If WorksheetFunction.CountA(myRange) <> myRange.Cells.Count Then
        bMsg = False
        For Each rNextCell In myRange.Cells
            If Len(rNextCell.Value) = 0 Then
                Application.EnableEvents = False
                rNextCell.Select
                Application.EnableEvents = True
                bMsg = True
                Exit For
            End If
        Next rNextCell
        If bMsg = True Then
            MsgBox "Please fill in required cells before continuing!", vbInformation, "ERROR!"
        End If
    End If
End Sub
 

Attachments

  • TEST.xls
    88.5 KB · Views: 39
i named the range that i wanted to be manditory if YES is selected in A28 required. i used this
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rNextCell As Range, bMsg As Boolean
    Dim myRange As Range
    If Target.Address = "$A$28" Then Exit Sub
    Set myRange = Range("required")
    If Me.Range("A28") = "YES" Then
        Set myRange = Range("required")
    End If
    If Not Intersect(Target, myRange) Is Nothing Then
        Exit Sub
    End If
    If WorksheetFunction.CountA(myRange) <> myRange.Cells.Count Then
        bMsg = False
        For Each rNextCell In myRange.Cells
            If Len(rNextCell.Value) = 0 Then
                Application.EnableEvents = False
                rNextCell.Select
                Application.EnableEvents = True
                bMsg = True
                Exit For
            End If
        Next rNextCell
        If bMsg = True Then
            MsgBox "Please fill in required cells before continuing!", vbInformation, "ERROR!"
        End If
    End If
End Sub
but i keep getting this compile error: ambiguous name detected Worksheet_SelectionChange
 
AMbiguous name means that you have two identically named procedures, the SelectionChange procedure in this case. You need to merge them.
 
Hi Eric,

Make sure that you have your code in the Worksheet module applicable to the worksheet you want the code to run on (not a standard module.)

Also make sure that you don't have another version of the same code in there. It sounds like you have two procedures with the same name, so you'll want to either comment the old one out, or delete the old one.

HTH,
 
the code is working now but the result is the same for "NO" as it is for "YES" im trying to make the distinction that "Yes" requires my.range "requried" and "NO" will result in the range "noreq". i still cant get that from this code.
 
Link the checkbox to cell A28 as Roger said, and then use

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rNextCell As Range, bMsg As Boolean
    Dim myRange As Range
    
    If Target.Address = "$A$28" Then Exit Sub
    
    Application.EnableEvents = False
    
    Set myRange = Range("needfill")
    If Me.Range("A28") Then Set myRange = Range("mustfill")
    
    If Intersect(Target, myRange) Is Nothing Then
    
        If WorksheetFunction.CountA(myRange) <> myRange.Cells.Count Then
        
            bMsg = False
            For Each rNextCell In myRange.Cells
            
                If Len(rNextCell.Value) = 0 Then
                
                    rNextCell.Select
                    bMsg = True
                    Exit For
                End If
            Next rNextCell
            
            If bMsg Then
            
                MsgBox "Please fill in required cells before continuing!", vbInformation, "ERROR!"
            End If
        End If
    End If
    
    Application.EnableEvents = True
End Sub
 
Hi Eric

The file I uploaded, had the code in it that I posted and it ran fine for me.
It also worked, in that it made the range either needfill or mustfill (there were only 2 cells different between the two ranges.

You now seem to be using 2 different ranges Required and NotRequired.

You have not given it the option of changing to NotRequired.

You should be setting the range first to Required.
Then, with the IF statement changing to Not Required see code below

Code: 
What you have amended the code to


Set myRange = Range("required")
    If Me.Range("A28") = "YES" Then
        Set myRange = Range("required")
    End If

what you should have changed it to


Set myRange = Range("required")
    If Me.Range("A28") = "YES" Then
        Set myRange = Range("Notrequired")
    End If

or
Set myRange = Range("required")
    If Me.Range("A28") = "NO" Then
        Set myRange = Range("Notrequired")
    End If


It all depends upon what you regard No or YES as wanting
 
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rNextCell As Range, bMsg As Boolean
Dim myRange As Range
 
If Target.Address = "$A$28" Then Exit Sub
 
Application.EnableEvents = False
 
Set myRange = Range("required")
If Me.Range("A28") = "YES" Then
Set myRange = Range("Notrequired")
End If
 
If Intersect(Target, myRange) Is Nothing Then
 
If WorksheetFunction.CountA(myRange) <> myRange.Cells.Count Then
 
bMsg = False
For Each rNextCell In myRange.Cells
 
If Len(rNextCell.Value) = 0 Then
 
rNextCell.Select
bMsg = True
Exit For
End If
Next rNextCell
 
If bMsg Then
 
MsgBox "Please fill in required cells before continuing!", vbInformation, "ERROR!"
now i get complie error expected end sub if anyone is working off of my TEST sheet could you upload with the code you say is working :)
****
also the "needfill" and "mustfill" sections are for the above section that still must have that code. the personalization section and the presort section should have two diffent codes. the personalization section will have the boolean code to make sure all sections for must and need fill are entered. the second section is the one that requres the YES/NO distinction for the "required" "notrequired"
 
Last edited:
Hi Eric,

Right after the msgbox line, add a line that says "End Sub". (Gotta have that to tell VBA where the routine ends.)
 
Guys,
Sorry if im confusing anyone but i am now testing this with a blank worksheet and naming two sections "mustfill" and "needfill" and having a dropdown box that has YES and NO and i still can seem to get the above code to work. I am by not mean any good with VBA, but i believe this is the only way i can do this. i do apologize if this is confusing :/
 
You must log in or register to reply here.
Back
Top