General discussion

  • Creator
    Topic
  • #2193401

    Data entry validation in Excel

    Locked

    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?

All Comments

  • Author
    Replies
    • #3097318

      Reply To: Data entry validation in Excel

      by bschaettle ·

      In reply to Data entry validation in Excel

      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

Viewing 0 reply threads