Help with manditory fill cells

E

EricT

New member
Joined
Mar 22, 2011
Messages
18
Reaction score
0
Points
0
I am currently using this code on a worksheet to make sure all the cells in the range (mustfill) are filled in with out moving on to the next cell in the range unless the previous is filled in. however this isnt very user friendly because you cant go to a previously filled in cell and change it if you have made a mistake untill all the fields are filled. does anyone have a code that will allow me to use the ("MustFill") range but be allowed to change a previously entered required field while moving through the range?

Thanks :)

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim myCell As Range
     Dim myRange As Range
     On Error GoTo NoRange
     If Range("B10").Value <= 0 Then Exit Sub
     Set myRange = Range("MustFill")
     For Each myCell In Range("MustFill")
          If myCell.Value = "" Then
          Application.EnableEvents = False
          myCell.Select
          Application.EnableEvents = True
          Exit Sub
     End If
     Next myCell
NoRange:
     Application.EnableEvents = True
End Sub
 
Last edited by a moderator:
Hi there,

Perhaps you could use something like 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 this cell first before continuing!", vbInformation, "ERROR!"
        End If
    End If
End Sub

HTH
 
Zack,
Thanks for looking into this :becky: but this code will only populate an error message when the "mustfill" section is not filled out completly. the code i have now will only let the user move to the next cell when the previous one has an entry. and i want the user not to be able to to move to the next cell till the previous box has been filled. but if they notice something they want to change can click on a previously entered cell (and MUST change to continue, i.e. cant be selected and left blank. then will move on to the next cell that needs to be filled in.) right now the code makes the user finish all "mustfill" section before being able to change the "mustfill" cells.

Thanks for your help.
 
So, let me reiterate this, to make sure I understand you correctly. You want the users to fill in your "MustFill" named range before making any other changes. (I don't know how big your range is, but I am testing with 4 cells, 2 rows and 2 columns.) If there is no data in the "MustFill" range, the first cell will be selected. If there is data in the "MustFill" range already, but there are blank cells in that named range, the next available cell will become selected.

If they have only part of the "MustFill" named range filled, would you want the next available cell selected, or would you like them to be able to select any cell in that range (i.e. to change a previous value)? Or would you perhaps want them to only be able to change when all values have been input into the "MustFill" range? I'm afraid it looks like you're contradicting yourself for this requirement, and needs clarification (could just be me though). With the code I posted above, you should be able to freely go anywhere inside the "MustFill" range, and you'll get an error message if you select any other cell outside of it, then you are taken back to the "MustFill" next empty cell.

Sorry if I'm making this more complicated than it needs to be.
 
Zack,
Sorry if this sounds crazy. but i have attached the form i am playing with, i have named the range in this (needfill). there are some 45 cells, i have them tabbed into a specific order View attachment Pers. Info Sheet 3-22-11.xls when the first cell has data entered it moves to the next in order. i would like the user to be able to go back to previously entered cells and change that cell but then returned to the next cell to be filled in the given order.

here is a quick example

C2 (must fill)----> E2 (must fill)------> H2 (must fill)

you must enter data into C2 to go to E2 and there must be data to go H2. but if you are on H2 and need to change C2 you can go back change it, then be taken back to the next cell that requires data entry. (in the end all cells under cell range (mustfill) must have data in them.
Thank you SOOOO MUCH for your help. :)
EricT
 
I'm afraid it's a slight contradiction, as far as I can see. To be able to fill in a cell in the desired range, then be taken to the next, that is one thing. It is another to programmatically go to the next cell, but allow them to go back to the previous cell, two different actions based on the same (previous) action. While I think it could be done with additional coding, I wonder if it wouldn't be better to re-think it a little bit? What is wrong with just having the entire range of must fill cells open to the user? And by 'open' I mean they can select any of those cells, but can't select any other cell outside of that range until all of them are filled. But while filling, they can navigate to any cell in that range. Would that not be acceptable? I think you're coming into a point of too much control, and generally micro-managing. I don't want to tell you what to do, but as a user, I certainly wouldn't want to be controlled that much.
 
Agreed

Zack,
I agree i have played around with the code and this should be everything that i need :clap2:. only problem now is that i cant get every cell in the "mustfill" range to stay part of the range. I hold Ctrl and click in order the cells that i want in the range after i have deleted "mustfill" and then rename the range "mustfill" but for some reason it wont take the last couple of cells i have selected. any thoughts? excel doesnt have a limit on the # of cells you can put in a range does it?

Thanks:becky:


P.S. thank you very much for your help it was driving me crazy.
 
I don't believe there is a limit. Have you tried adding it manually? Press Ctrl + F3 to open the named range manager, select your range, click in the RefersTo box, add the range address to the end. Your worksheet protection must be lifted (turned off) to adjust this.
 
You must log in or register to reply here.
Back
Top