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

[EricT]

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.

any help would be greatly appreciated.
Thanks, all.

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

[Ken Puls]

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.

[EricT]

awesome thank you soo much. if you want i can attach the form im woking on?
Thanks,

[Ken Puls]

Sure! That would be quite helpful.

[EricT]

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

[Roger Govier]

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

Dim myRange As Range
    Set myRange = Range("needfill")
    If Me.Range("A28") Then
        Set myRange = Range("mustfill")
    End If

[EricT]

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

[EricT]

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.

[EricT]

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

[Roger Govier]

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.

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