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]

if you use a worksheet event to replace the value and formating of that cell everytime it changes to empty would do the trick

then another event that replace the format to standard when someone types something there

 

[Snakehips]

@ipbr21054

Give this a try. Paste code into the code module of the sheet concerned.

Private Sub Worksheet_Change(ByVal Target As Range)
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
Else
Rng = "PIN CODE HERE"
    Rng.Font.ColorIndex = 15
End If
Application.EnableEvents = True
End Sub

HTH

 

[ipbr21054]

Hi,
I have a change event allready so my code is now as supplied.
I do not see anything in cell G39


Also how would i change so i could use hex code for the color
Do you see an issue with the code ?

Private Sub Worksheet_Change(ByVal Target As Range)
    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
Else
Rng = "PIN CODE HERE"
    Rng.Font.ColorIndex = 15
End If
Application.EnableEvents = True

End Sub

 

[ipbr21054]

I have removed the existing code & tried it with only your code but made no difference.

I have also tried it on another sheet & the same.

 

[Snakehips]

I have removed the existing code & tried it with only your code but made no difference.

I have also tried it on another sheet & the same.

It appears to work fine for me.
Have you, while testing, had it error or quit without 'Application.EnableEvents = False" having been reestablished a True ?
Try Application.EnableEvents = True in the immediate pane and hit return.

 

[ipbr21054]

No errors or anything.
If it helps i dont even see the text in the cell its supposed to be shown in.

true also made no difference

I opened a new woorkbook / worksheet & still i see no text in the cell

 

[Joe4]

What is the name of the VBA module where you placed that "Worksheet_Change" code?

 

[ipbr21054]

I right clicked on the INV worksheet then placed it in the change event
Reading original post it shows code module,is that then right click & paste ?

 

[ipbr21054]

Should it go here ?

If so how does it know to run on sheet INV