Text which disappears when new text is entered but reappears if the new text is deleted

[Detectiveclem]

Help needed I would like to have greyed-out text (watermark?) in some cells to give users short instructions for that particular cell. If they type something in that cell, the text they input is in plain dark black...but if they delete what they type, the cell goes back to having the greyed-out text.

So just for an example, one cell might say ENTER LAST NAME. The next cell going down the same column may say ENTER YOUR DATE OF BIRTH. When the user starts typing into these cells the greyed-out text disappears and their inputted text shows up in DARK BLACK. BUT if they delete what they typed, the original grey-out text reappears (e.g. ENTER YOUR LAST NAME, or what ever was in the cell previously reappears greyed-out)

As this spreadsheet will be shared with others, I am looking for a solution which avoids VBA.

Does anyone have a suggestion?

Thank you.

 

[Rick Rothstein]

So are you saying that they should not be allowed to fill in any non-mandatory cell located after a mandatory cell if that mandatory cell has not been filled in yet? If so, how will you control someone going back and deleting the text in a mandatory cell when there is text in non-mandatory cells after it? I think I understand what you are trying to do, I just don't understand how the controls over it should be implemented.

 

[Detectiveclem]

So are you saying that they should not be allowed to fill in any non-mandatory cell located after a mandatory cell if that mandatory cell has not been filled in yet? If so, how will you control someone going back and deleting the text in a mandatory cell when there is text in non-mandatory cells after it? I think I understand what you are trying to do, I just don't understand how the controls over it should be implemented.

Hi Rick, I may be over complicating things here. I just wanted to ensure people didn’t miss filling in vital questions, e.g. when a response is needed for example when a YES/NO type question is being asked. Or please provide details here. Etc. But hey you’ve been great already so please don’t go to any more trouble if it’s a big ask. Thanks a million, Paul

 

[Rick Rothstein]

The problem implementing what you want on a worksheet is that you have no good way to control what the user does. If you had all your input fields on a UserForm, you could force the user to fill in mandatory fields by not letting him/her close the UserForm until they were all filled in, but you do not have such a control mechanism on a worksheet short of not allowing the user to close the workbook until the mandatory fields were all filled in. However, you have so many cells involved on your worksheet that I am guessing filling it in is a multi-day affair, so not allowing the workbook to be closed is probably not feasible.

 

[Detectiveclem]

The problem implementing what you want on a worksheet is that you have no good way to control what the user does. If you had all your input fields on a UserForm, you could force the user to fill in mandatory fields by not letting him/her close the UserForm until they were all filled in, but you do not have such a control mechanism on a worksheet short of not allowing the user to close the workbook until the mandatory fields were all filled in. However, you have so many cells involved on your worksheet that I am guessing filling it in is a multi-day affair, so not allowing the workbook to be closed is probably not feasible.

Hi Rick, I agree. I will just leave it as it is, but using your code. Thank you so much again. Kindest regards. Paul

 

[Detectiveclem]

Hi again Rick,

Is it possible to add to the code you have already provided to me to ensure the following cells are changed to 'Proper' irrespective of what case the person initially types into the cell.

D13-D17
D20
D22
D24-27
D32-35
D51-57

And CAPITALS in cells

D30
D38
D60

Thank you, Paul

 

[Rick Rothstein]

Replace the code I gave you earlier with the following and you should get the proper case and upper case lettering in the cells you indicated...

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim Colr As Long, Txt As String
  
  If Target.Count = 1 Then
    If Not Intersect(Target, Range("D8:D194")) Is Nothing Then
      Txt = Sheets("CodeTemplate").Range(Target.Address).Value
    End If
  
    Application.EnableEvents = False
    If Len(Target.Value) = 0 Or Target.Value = Txt Then
      Target.Font.ColorIndex = 16
      Target.Value = Txt
    Else
      If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57")) Is Nothing Then
        Target.Value = Application.Proper(Target.Value)
      ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then
        Target.Value = UCase(Target.Value)
      End If
      Target.Font.ColorIndex = 1
    End If
    Application.EnableEvents = True
  End If

End Sub

 

[Detectiveclem]

Replace the code I gave you earlier with the following and you should get the proper case and upper case lettering in the cells you indicated...

Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim Colr As Long, Txt As String
 
  If Target.Count = 1 Then
    If Not Intersect(Target, Range("D8:D194")) Is Nothing Then
      Txt = Sheets("CodeTemplate").Range(Target.Address).Value
    End If
 
    Application.EnableEvents = False
    If Len(Target.Value) = 0 Or Target.Value = Txt Then
      Target.Font.ColorIndex = 16
      Target.Value = Txt
    Else
      If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57")) Is Nothing Then
        Target.Value = Application.Proper(Target.Value)
      ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then
        Target.Value = UCase(Target.Value)
      End If
      Target.Font.ColorIndex = 1
    End If
    Application.EnableEvents = True
  End If

End Sub

Hi Rick, this works perfectly. Thank you so much. I wish I had your talent. Cheers Paul

 

[Detectiveclem]

Hi Rick, Help please. For some reason nothing is working now. I have tried copying and do it again but absolutely nothing works. Can you guide how I restore this Macro? Thank you

 

[Detectiveclem]

Replace the code I gave you earlier with the following and you should get the proper case and upper case lettering in the cells you indicated...

Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim Colr As Long, Txt As String
 
  If Target.Count = 1 Then
    If Not Intersect(Target, Range("D8:D194")) Is Nothing Then
      Txt = Sheets("CodeTemplate").Range(Target.Address).Value
    End If
 
    Application.EnableEvents = False
    If Len(Target.Value) = 0 Or Target.Value = Txt Then
      Target.Font.ColorIndex = 16
      Target.Value = Txt
    Else
      If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57")) Is Nothing Then
        Target.Value = Application.Proper(Target.Value)
      ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then
        Target.Value = UCase(Target.Value)
      End If
      Target.Font.ColorIndex = 1
    End If
    Application.EnableEvents = True
  End If

End Sub

Hi Rick, just to add more confusion I have tried the above code on a another very simple spreadsheet and it works fine, but just not now on the spreadsheet I want to work on. I really would appreciate what you can to assist me as it was previously working well. Thank Paul

 

[Rick Rothstein]

Where did you put it? Basically, there are three modules where code can be placed... a general module where macros and functions are placed, the ThisWorkbook module where event code for any or all workbook-wide event code procedures go and sheet modules where sheet specific event code procedures go. The code I gave you is a sheet level event procedure so it must go in the module for the specific sheet where it will function at. The easiest way to get to the correct sheet module is to select the worksheet that the event code is meant for, then right-click its tab and select "View Code" from the popup menu that appears.