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. :confused2:

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


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

[EricT]

i named the range that i wanted to be manditory if YES is selected in A28 required. i used this

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

[Bob Phillips]

AMbiguous name means that you have two identically named procedures, the SelectionChange procedure in this case. You need to merge them.

[Ken Puls]

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,

[EricT]

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.

[Bob Phillips]

Link the checkbox to cell A28 as Roger said, and then use



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

[Roger Govier]

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



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

[EricT]

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"

[Ken Puls]

Hi Eric,

Right after the msgbox line, add a line that says "End Sub". (Gotta have that to tell VBA where the routine ends.)

[Ken Puls]

Also, Eric, quick tip for you. You can have your code format like I've done above using CODE tags on this board. Here's how: http://www.excelguru.ca/forums/misc.php?do=bbcode#code

:)

[EricT]

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 :/

[EricT]

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
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
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 :)

[Roger Govier]

Hi Eric

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

To achieve what you want try





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

[EricT]

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

[EricT]

this is the test file with the combo of both codes. Thanks :)

[EricT]

i think i got it, it kept going to Range2 and i just made sure to exit the sub. thanks.