Prompt user for data input in Excel

choy77
choy77 used Ask the Experts™
on
All,

Hopefully this will be a quick fix.  I would like to add some messages to the attached document to enforce the user to input text in a certain field?  This document is quite basic and used for raising internal orders.

I have added some information in the spreadsheet as to what I want.  I basically want the document to prompt the user if a certain cell has had no data inputted in it.  And also add a drop down box with certain information to enforce the user to select a division.

Any help greatly appreciated.
example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ardhendu SarangiSr. Project Manager

Commented:
this is quite simple.. you need to add a data validation list for the column cell and the following code -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Dim i As Integer
Set targ = [B:D]    'Watch these cells for changes
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub
 
Application.ScreenUpdating = False
'MsgBox (targ.Address)
n = Mid(targ.Address, 3, 4)
If targ <> "" And Range("I" & n) = "" Then
MsgBox ("Nominal Code cannot be empty. Please enter a valid value.")
End If
Application.ScreenUpdating = True
End Sub

Open in new window


see attached excel for a working example.

- Ardhendu

Author

Commented:
Hi,

I cant see the attached file for a working sample?

Thanks
Sr. Project Manager
Commented:
trying again... see attached file
example.xls
Ardhendu SarangiSr. Project Manager

Commented:
any luck?

Author

Commented:
Works perfect thanks :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial