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.
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
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
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
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.
awesome thank you soo much. if you want i can attach the form im woking on?
Thanks,
Sure! That would be quite helpful.![]()
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
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.
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
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
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
Bookmarks