How do i add Placeholder (grey text) to an Excel sales Quote

0

I have created a new Sales Quote form for work and i would like to have the section where customer information is typed in to have "placeholder" text telling the person filling out the form what goes in each box. i have tryed to do it using macros but cannot seem to get it to work on the actual worksheet.

Answer
Discuss

Answers

0

That is quite a task. Install the code below in the code sheet of the worksheet on which you want the action. Then follow the instructions below (and in) the code to adjust it to your needs.

Option Explicit
    ' set any color you like in a cell and select it.
    ' in the Immediate window type "? Activecell.Font.Color" (without quotation marks
    Const FontColor As Long = 10921638              ' = grey (change as required)
    Dim InputCell() As String
    Dim InputText() As String
    Dim PrevCell As Range
    
Private Sub Worksheet_Activate()
    ' 06 Aug 2017
' ==========================================================
    ' this constant must be set to match the number
    ' of InputHelp elements you specify below:-
    Const NumberOfInputCells As Long = 2
' ==========================================================
    
    Dim InputHelp(NumberOfInputCells) As String
    ReDim InputCell(NumberOfInputCells)
    ReDim InputText(NumberOfInputCells)
    Dim Sp() As String
    Dim i As Long
    
    ' for each cell with input help specify the
    ' address and help text, comma separated:-
    InputHelp(0) = "A1,Enter your name"
    InputHelp(1) = "A4,Enter your birthday"
    InputHelp(2) = "A6,Enter your favourite color"
    Application.EnableEvents = False
    For i = 0 To NumberOfInputCells
        Sp = Split(InputHelp(i), ",")
        InputCell(i) = Sp(0)
        InputText(i) = Sp(1)
        With Range(Sp(0))
            .Font.Color = FontColor
            .HorizontalAlignment = xlLeft
            .Value = Sp(1)
        End With
    Next i
    Application.EnableEvents = True
    On Error Resume Next
    Set PrevCell = ActiveCell
    If Err Then
        PrevCell = Cells(1, 1)
        Err = 0
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 06 Aug 2017
    
    Dim Idx As Long
    
    Application.EnableEvents = False
    Idx = Insect(PrevCell)
    If Idx > -1 Then
        With Range(InputCell(Idx))
            If Len(PrevCell.Value) = 0 Then
                .Value = InputText(Idx)
                .Font.Color = FontColor
            End If
        End With
    End If
    Set PrevCell = Target.Cells(1)
    
    Idx = Insect(Target)
    If Idx > -1 Then
        With Range(InputCell(Idx))
            If .Value = InputText(Idx) Then
                .Value = ""
                .Font.Color = 0
            End If
        End With
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 06 Aug 2017
    Dim Idx As Long
    
    Idx = Insect(Target)
    If Idx > -1 Then
        With Target
            If (.Value = "") Or (.Value = InputText(Idx)) Then
                Application.EnableEvents = False
                .Value = InputText(Idx)
                .Font.Color = FontColor
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub
Private Function Insect(Target As Range) As Long
    ' 06 Aug 2017
    ' return the index of InputCells
    
    Dim Rng As Range
    Dim i As Long
    
    On Error GoTo ErrHandler
    Set Rng = Range(InputCell(0))
    For i = 0 To UBound(InputCell)
        Set Rng = Application.Union(Rng, Range(InputCell(i)))
    Next i
    
    If Application.Intersect(Rng, Target) Is Nothing Then
        Insect = -1                     ' no match
    Else
        With Target
            For i = 0 To UBound(InputCell)
                If .Address = Range(InputCell(i)).Address Then Exit For
            Next i
        End With
        Insect = i
    End If
    Exit Function
    
ErrHandler:
    If Err = 9 Then
        Worksheet_Activate
        Err = 0
        Resume 0
    End If
End Function

It's absolutely essential that you set the constant 'NumberOfInputCells' to the correct value. The "correct value" is the number of 'InputHelp' elements you want to declare. The index of that array starts counting at 0. In the above example the highest count is 2 (meaning 3 cells to have help texts). Therefore 'NumberOfInputCells' = 2.

Note that the default font colour is black (=0). Look for ".Font.Color = 0" in the Selection_Change procedure. That's where you can change it if you want another default colour.

Also note that the input help texts will be updated when you activate a sheet. For your first try, install the code in a blank workbook behind a blank sheet. Activate another sheet and select the first sheet again. You will see the help in A1, A4 and A6.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login