Results 1 to 8 of 8

Thread: Help with manditory fill cells

  1. #1

    Help with manditory fill cells



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

    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 Zack Barresse; 2011-03-22 at 07:20 PM. Reason: Added CODE tags

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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
    Regards,
    Zack Barresse

  3. #3
    Zack,
    Thanks for looking into this 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.

  4. #4
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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.
    Regards,
    Zack Barresse

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

  6. #6
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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.
    Regards,
    Zack Barresse

  7. #7

    Agreed

    Zack,
    I agree i have played around with the code and this should be everything that i need . 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


    P.S. thank you very much for your help it was driving me crazy.

  8. #8
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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.
    Regards,
    Zack Barresse

Posting Permissions

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