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

alright guys,
after playing with that for awhile i finally got the code to work the way i wanted it to.....THANKS A TONS GUYS. now how do i get VBA to allow me to use this
Code:
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rNextCell As Range, bMsg As Boolean
    If Not Intersect(Target, Me.Range("mustfill")) Is Nothing Then
        Exit Sub
    End If
    If WorksheetFunction.CountA(Me.Range("mustfill")) <> Me.Range("mustfill").Cells.Count Then
        bMsg = False
        For Each rNextCell In Me.Range("mustfill").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
for the first part of the worksheet and then
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
    
    Application.EnableEvents = False
    
    Set myRange = Range("required")
    If Me.Range("A28") = "NO" 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!"
            End If
        End If
    End If
    
    Application.EnableEvents = True
End Sub
for the second part of the work book....so essentially the user will finish the first section and fill out what is necessary and then toggel the second part of the code with a YES or NO in the dropdown box.


Thanks SOOOOO MUCH :)
 
Hi Eric

You hadn't said that there were 2 different sections involved and 3 different ranges.

To achieve what you want try

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("mustfill")
  
    If Not Intersect(Target, myRange) Is Nothing Then
        GoTo range2
    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
    GoTo exit_sub

range2:
    Set myRange = Range("Required")
    If Me.Range("A28") = "NO" Then
        Set myRange = Range("Not Required")
    End If
    If Not Intersect(Target, myRange) Is Nothing Then
         GoTo 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
    
exit_sub:
Application.EnableEvents = True
    
End Sub
 
Roger,
this is somewhat of what i am trying to achieve but when it nothing is entered it sends the user to range 2 instead of forcing the completion of range one. i would like the user to have to complete the first section (mustfill) and when that is complete they can go from cell to cell changing what they want in the other open cell and if they change something in the must fill they need to enter somthing else in the cell before moving on. once they have completed all parts of "mustfill" they then do the (YES/NO) drop down that then starts the range 2 series of events where they need certain fields for "required" (YES on the drop down) and certain fields for "notrequired" (NO on the drop down)...seperatly these codes work for what i want. any input? i will include the TEST file with the code you gave me so you can see what im talking about. :) thanks man
 
this is the test file with the combo of both codes. Thanks :)
 

Attachments

  • TEST.xls
    83 KB · Views: 28
i think i got it, it kept going to Range2 and i just made sure to exit the sub. thanks.
 
Back
Top