Force user to give additional info

[jokkebal]

Hi, this is what I need :

In a list (of variable length), a user can enter some info in cell A1. However, if he does so, it should be compulsory that he enters additional info in cells B1 and C1.

What I think of, is that the system reacts as soon as user has entered something in A1 and then tries to enter info in any other cell before having completed B1 and C1. The reaction would be a message box which can be removed by pressing the "OK" button in the box, enabling the user to then enter something in the required cells. User should not be able to continue before having completed both B1 and C1 - or deleted A1 again.

E.g. if a user enters a price in A1, system should force him to enter the currency (USD, EUR,...) in B1 and the lead time in C1.

Not to make things too complicated, I think it's ok if this works for the entire column (Row1 through Row 10000000...)

How do I do this ?

Thanks in advance !

 

[pjmorris]

highlight cells A2:C100000, then select Data Validation, then Allow as Custom, in the Formula box enter =COUNTA($A1:$C1)=3. Whenever the user tries to enter something in the row below A1 they will be unable to do so until something is in each of the 3 cells. Likewise, if cells A2:C2 are not filled with something then the user is unable to enter anything in cell A3.

Hope this helps.

 

[jokkebal]

Hi Peter,
Thanks for your prompt reply. In fact, it does not help (unless I do something wrong). The result :
If I want to enter something in A1, I receive error message - seems logical since the other two cells are empty.Same for the other cells.
If I force the system by pasting something in B1 and C1, then I can enter something in A1. But that is not the goal : user has to be able to fill something in A1. I just want him to "finish" the job by also filling B1 and C1 before proceeding in any other cell.

 

[pjmorris]

Hi,

I suspect you've applied the Data Validation to cells A1:C1 as well. In this case you will be unable to enter data in A1 because the other cells are still blank and hence the COUNTA won't return 3, which is what you're experiencing. It is important that you only highlight from A2 down to C100000 (or however many rows you need) and then use the formula =COUNTA($A1:$C1)=3 which refers to the row above the row you're entering data in. In short, for example, you can enter data in cells A5:C5 only when cells A4:C4 are complete.

Hope this helps.

 

[pjmorris]

Hi, did this work?

Regards

 

[jokkebal]

Peter
won't be able to check next two weeks but I see difference in what you said and what I did. Will test and come back.

 

[mikerickson]

You might put this in the sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 Then
            If WorksheetFunction.CountA(.EntireRow.Resize(1, 3)) <> 3 Then
                If .Column = 1 Then
                    Application.EnableEvents = False
                    Me.ScrollArea = Range(.Cells(1, 2), .Cells(1, 3)).Address
                    .Offset(0, 1).Select
                    Application.EnableEvents = True
                End If
            Else
                Me.ScrollArea = vbNullString
            End If
        End If
    End With
End Sub

 

[jokkebal]

Peter,
Seems to work but can't use it. However smart, the solution has too many assumtions to work : (1) that there is no activity in other columns; (2) don't think this works in sheets that may be sorted etc.

Mikerickson,
Thanks to you too for your support. Would like to try this but am not strong enough in VB to understand. Where do I have to enter the columns that I want my thing to work ? Please do note that I used the columns A, B and C as an example. In reality, the check needs to be done in EM and the system has to demand entry in EN and EO.
Also, am currently thinking of putting a formula in EM, which means that system shoud check as follows : if EM1>0, then EN AND EO must be filled before doing anything else.

Josse.

 

[mikerickson]

You can restrict the action with code like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not Application.Intersect(.Cells, Range("A:D, AB:AB"): Rem adjust here
        If .Cells.Count = 1 Then
            If WorksheetFunction.CountA(.EntireRow.Resize(1, 3)) <> 3 Then
                If .Column = 1 Then
                    Application.EnableEvents = False
                    Me.ScrollArea = Range(.Cells(1, 2), .Cells(1, 3)).Address
                    .Offset(0, 1).Select
                    Application.EnableEvents = True
                End If
            Else
                Me.ScrollArea = vbNullString
            End If
        End If
        End If
    End With
End Sub