Web Development

General discussion

Gravatar
Locked

Data entry validation in Excel

By Steve_ald ·
I am currently working on a spreadsheet in Excel that is a shared file.
My problem is that some users never check the cell they are entering data into and that data will not be in a range I would like to specify.... i.e. I want them to start entry on the next available cell in column A instead of any cell they would like to use.

An example would be...I have data in cells (A:1)
through (AA:26)
I want that user to enter their data in cell (A:27) and NOT (A:2015) or (D:2015) and they must be able to manipulate the data that is already entered.

I thought of using the ISBLANK() function, but that precludes that I know the cell that they have selected.

Can you pass a cell reference to Isblank() and then test each cell from (A:1) to the end of the list and then force the cell select to that location?

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
+ Follow this Discussion ·
| Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by bschaettle In reply to Data entry validation in ...

I would protect the worksheet and prompt them for the data using a macro. The macro can be assigned to a keyboard shortcut to make their task simpler. Here's the VBA code:

Public Sub EnterNewData()
Set MyRange = MyWorksheet.Cells(1, 1).CurrentRegion
iNextEmptyRow = MyRange.Rows.Count + 1
sNewData = InputBox("Please enter your data")
If Len(sNewData) > 0 Then
With MyWorksheet
.Unprotect
.Cells(iNextEmptyRow, 1).Value = sNewData
.Protect
End With
End If
Set MyRange = Nothing
End Sub

Back to Web Development Forum
1 total post (Page 1 of 1)  

Start or search

Related Discussions

Related Forums