Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Sure! That would be quite helpful.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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
    Attached Files Attached Files

  6. #6
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  7. #7
    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

  8. #8
    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.

  9. #9
    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
    Attached Files Attached Files

  10. #10
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •