how can i add text to the cell below and next to the selected cell

[tonywatsonhelp]

Hi Everyone,
I have an document and in the Sales sheet i have an area that needs to be filled out,
it Columns AA to AH

So we start at cell AA11 for example (this is the very first cell in the sheet) It will read "Start new sale here" now when the person adds in data to that cell the next cell says "Please complete" until we reach column AH when it stops and the message
"Start new sale here" is put in cell AA12

so if you can imagine each line is a guid
the line starts saying "Start new sale here" then once tgey have put that in it goes "Please complete" for the next lot of columns until they are all filled in when it says "Start new sale here" in next row down,
if possible also if they try doing anything else why the words "Please complete" are on show messabox saying "You must complete this section before moving on!" and take them back to the cell.

please help if you can

Tony

 

[Peter_SSs]

Hmm, this could be very frustrating for a user but you could try this in the worksheet's module. It assumes that you already have "Start new sale here" or "Please complete" somewhere in columns AA:AH
It also, of course, relies on the user having macros enabled.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim NextCell As Range

  Set NextCell = Columns("AA:AH").Find(What:="Start new Sale here", LookAt:=xlWhole)
  If NextCell Is Nothing Then Set NextCell = Columns("AA:AH").Find(What:="Please complete", LookAt:=xlWhole)
  If NextCell Is Nothing Then
    MsgBox "Worksheet error"
  Else
    Application.EnableEvents = False
    NextCell.Select
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Value = "" Then
    Application.EnableEvents = False
    If Target.Column = 27 Then
      Target.Value = "Start new sale here"
    Else
      Target.Value = "Please complete"
    End If
    Application.EnableEvents = True
  End If
  If Target.Value <> "Start new sale here" And Target.Value <> "Please complete" Then
    Application.EnableEvents = False
    If Target.Column = 34 Then
      With Range("AA" & Target.Row + 1)
        .Select
        .Value = "Start new sale here"
      End With
    Else
      With Target.Offset(, 1)
        .Select
        .Value = "Please complete"
      End With
    End If
    Application.EnableEvents = True
  End If
End Sub