Placeholder

[Quietus]

I am sorry if this has been asked before. I tried to search but did not have any luck.

I am looking to have placeholders in an excel spreadsheet for multiple cells and on multiple sheets. I would like to see the very simple thing to help accomplish this. What I am needing is for any cell I chose to have a letter in that cell when not being used. Then when being used the letter is cleared or deleted. Then when the cell is cleared form the date it goes back to the placeholder letter. Example Cell A1 will have a placeholder of (A), then when something is entered "Clients name" then the (A) will go away of course. But when the "Clients name" is removed the (A) comes back as a placeholder. Each sheet could have up to 20 placeholders and up to 8 sheets. Thoughts?

 

[John_w]

See if this does what you want. Put the code in the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim placeholderCells As String
    Dim placeholders As String
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Select Case Sh.Name
    
        Case "Sheet1"
        
            placeholderCells = "A5,A10,A15,A20"
            placeholders = "A,B,C,D,E"
            
            If Not Intersect(Target, Range(placeholderCells)) Is Nothing Then
                If IsEmpty(Target.Value) Then
                    Reset_Placeholder_In_Cell Target, placeholderCells, placeholders
                End If
            End If
            
        Case "Sheet2"
        
            placeholderCells = "B5,B10,B15,B20,B25"
            placeholders = "A,B,C,D,E,F"
            
            If Not Intersect(Target, Range(placeholderCells)) Is Nothing Then
                If IsEmpty(Target.Value) Then
                    Reset_Placeholder_In_Cell Target, placeholderCells, placeholders
                End If
            End If
        
    End Select
    
End Sub


Private Sub Reset_Placeholder_In_Cell(placeholderCell As Range, placeholderCells As String, placeholders As String)

    Dim placeholderCellsArray As Variant
    Dim placeholdersArray As Variant
    Dim i As Long

    'Find index of placeholderCell in placeholderCellsArray and change placeholderCell to the same index in the placeholdersArray
    
    placeholderCellsArray = Split(placeholderCells, ",")
    placeholdersArray = Split(placeholders, ",")
    i = 0
    While i < UBound(placeholderCellsArray) And placeholderCell.Address(False, False) <> placeholderCellsArray(i)
        i = i + 1
    Wend
    
    Application.EnableEvents = False
    placeholderCell.Value = placeholdersArray(i)
    Application.EnableEvents = True

End Sub

As you can see, the code defines the sheet names, placeholder cells and corresponding placeholder letters. Change these and add extra Case statement blocks as required.