Show text in worksheet cell advising user to enter the relevant text here

[ipbr21054]

On my worksheet in cell G39 i wish for the user to type the relevant text in that cell.
Can we put a faded text in that cells to advise user where its to be placed.

Example cell G39 shows PIN CODE HERE.
User would then type 123456 in the cell & PIN CODE HERE would be overwritten or removed until next time.

I dont want to use a label etc so is this possible please


I had seen this code / short video clip which is what i require but its for a TextBox

Private Sub TextBox2_Change()
    TextBox2.BackStyle = IIf(Len(TextBox2.Text) = 0, fmBackStyleTransparent, fmBackStyleOpaque)
End Sub

 

[dfsobral]

let me see if i understood

after the code for printing the invoice is executed, the code clears the information present in every cells right?

but cell B39 can't be cleared, in must have PIN CODE HERE

if you want to show PIN CODE HERE only when the other information in inserted?

 

[dfsobral]

just see your post

you can't clear cell B39, you must insert PIN CODE HERE, it will be the clear state for that cell

 

[ipbr21054]

G39 not B39
Once invoice has printed & cells in question cleared PIN CODE HERE is NOT shown in cell

 

[Snakehips]

@ipbr21054
I think the issue is that following the printing of the invoice you have code that is clearing a number of cells.
If that code is clearing a range that is greater than a single cell then the first line of the code that YOU posted as being at the top of the Worksheet Change code will cause an exit from that code.
Therefore it never gets to the code to reset G39.
To hopefully get around this, try two changes.
Edit the bottom half of the change code as below regarding the 'Application.EnableEvents = True' lines.
Add a suggested line of code, see InvoiceSample, to your invoice code so that G39 is cleared as a single cell.

Hopefully the notes in the code will help?

Private Sub Worksheet_Change(ByVal Target As Range)

'******* the first line of code below will exit the sub if the Target range is more than a single cell  ******
'if your invoice print  code is clearing more than a single cell then this code will not get past the first line
'so the cleared G39 cell will not be reset to 'PIN CODE HERE'
'Add a separate code line to your invoicecode so that G39is cleared as a single cell
'***********

    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    If Not Intersect(Target, Range("L14:L18,G13:G18,G27:M51")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("G13")) Is Nothing Then
        Range("G1").Select
    End If

If Intersect(Target, Range("G39")) Is Nothing Then Exit Sub
    Dim Rng As Range
    Set Rng = Range("G39")
    Application.EnableEvents = False
    If IsNumeric(Trim(Rng)) Then
        Rng.Font.ColorIndex = -4105
        Application.EnableEvents = True  '<<<******
    Else
        Rng = "PIN CODE HERE"
        Rng.Font.ColorIndex = 15
        Application.EnableEvents = True   '<<<******
End If
End Sub

Sub InvoiceExample()
Application.ScreenUpdating = False
'Assuming this to be your invoice printing code
'for test purposes here is just  a message
MsgBox "Invoice Printed"
'After which you are likely clearing several cells in INV
'Eg.    Sheets("INV").Range("A1:H139").ClearContents
'the cell count of that range is greater than 1 and will cause an early exit from change code
'so use either of below to clear and reset G39
Sheets("INV").Range("G39") = ""
'OR
 Sheets("INV").Range("G39").ClearContents
 'as a separate command in order to re-set G39
 Application.ScreenUpdating = True
End Sub