Excell - Condition to Fill up Cell

Hello every one
i want to have the attached form to allow user fill in his expense
he has to fill in :
invoice_date - invoice number - category - personnel number
the category is choose from a drop down list
but i want to ask is it possible to put a condition
if user select a specific value of category , let say : "Telephone"
to enforce him that the field "Personnel Number" will be mandatory
hope my question is clear
Form.xlsx
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Try below in Worksheet Code, it will prompt user to enter Person Number if Telephone is selected in Column C:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 2 To 100
    If Not Intersect(Target, Cells(i, 3)) Is Nothing Then
        If Target.Value = "Telephone" Then
            MsgBox "Please Enter Person Number"
        End If
    End If
Next i
End Sub

Open in new window

Add below code in Workbook, which will restrict user to enter Person Name, if Column C has Telephone & Column D is left blank:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
For i = 2 To 100
    If Cells(i, 3) = "Telephone" And Cells(i, 4) = "" Then
        MsgBox "Person Number Field requires users input"
        Cancel = True
    End If
Next i
End Sub

Open in new window

Hope this helps.
Restrict-Cell-Entries.xlsm
NiceMan331Author Commented:
thank u
the file is good
but only 2 comment
1- the first code is working ,
2-the second code is not working
by the way , can i add one more request
if after user finish the last cells in the sheet ( which is D here ) , how can let the cursor move to the next raw at A
appriciated
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorCommented:
I think try this:
Exceltips.docx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ShumsExcel & VBA ExpertCommented:
Second is working as well, try to leave any blank cells in Col D if Col C has Telephone, it wont exit Excel unless you enter Person Number.

For your second request of moving the cursor after entering in Col D to Col A, try below code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 2 To 100
    If Not Intersect(Target, Cells(i, 3)) Is Nothing Then
        If Target.Value = "Telephone" Then
            MsgBox "Please Enter Person Number"
        End If
    End If
    If Not Intersect(Target, Cells(i, 4)) Is Nothing Then
        If Target.Value <> "" Then
            Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        End If
    End If
Next i
End Sub

Open in new window

Restrict-Cell-Entries_v1.xlsm
NiceMan331Author Commented:
thank u very much
yes now is ok , it will not leave the file unless filling persons number
but can edit the code to restrict not to leave the cell D if still blank and the category is telephone
ShumsExcel & VBA ExpertCommented:
It means solution provided solved your purpose?
NiceMan331Author Commented:
it solved part of my requirement
still if please adjust it to stay on cell D if it is blank ,
ShumsExcel & VBA ExpertCommented:
Try adding new entries with below code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 2 To 100
    If Not Intersect(Target, Cells(i, 3)) Is Nothing Then
        If Target.Value = "Telephone" Then
            MsgBox "Please Enter Person Number"
            Target.Offset(0, 1).Select
        End If
    End If
    If Not Intersect(Target, Cells(i, 4)) Is Nothing Then
        If Target.Value <> "" Then
            Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        End If
    End If
    If Not Intersect(Target, Cells(i, 4)) Is Nothing Then
        If Target.Offset(0, -1).Value = "Telephone" And Target.Value = "" Then
            MsgBox "Please Enter Person Number"
            Target.Select
        End If
    End If
Next i

End Sub

Open in new window

Restrict-Cell-Entries_v1.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
Great
it is good
thank u
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.