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

 

[Joe4]

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 ?

OK, try placing this line at the top of your procedure, to confirm that it is firing/running:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Event Procedure code is running"
    ...

Now, try triggering the code to run again. Do you get that Message Box?
If not, then the code is not firing, which would mean one of the following:
- VBA code is disabled in your workbook
- You have placed the VBA code in the wrong module (I think you just confirmed that is not the case)
- Events have been disabled (Snakehips showed you one way to re-enable them).

Here is another way you can re-enable events. Simply run this little procedure manually:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

If you ARE getting the Message Box, but things still do not appear to be working correctly, add a break line to your code at the new MsgBox line.
Then, when you trigger the code to run, it should stop here. You can then use the F8 key to go through your code one line at a time. Do this, and watch which path it takes through your code, and see where it is exiting or failing out. This is a good debugging technique (known as "stepping into your code").

 

[Joe4]

Should it go here ?

If so how does it know to run on sheet INV

View attachment 115863

NO! Event Procedure do NOT work in General Modules.
They only work in the ThisWorkbook and Sheet modules.

If you want it to run in the "INV" sheet, you MUST place the code in the module named "Sheet15 (INV)".

 

[ipbr21054]

Like this ?
If so still no text in cell

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Event Procedure code is running"
    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]

It is now shown using this.
Before nothing would appear but now it is.

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]

Thanks for the input

 

[ipbr21054]

@Snakehips
Can you take a look at this code please.

My goal / intension was this.
I open the worksheet & in the cell G39 I see the text PIN CODE HERE
The user types in the cell say 123456
The invoice is printed.
All values in cells are cleared ready for the next invoice.
At this point I’m expecting to see the text PIN. CODE HERE but I see nothing.

Basically how can this code be edited so the text PIN CODE HERE can be shown again otherwise I might as well not use it as the a new user won’t see anything so won’t know to type there.

Once the cell values are cleared what can be down to say refresh / reload the code to be placed there again.

Thanks.

 

[dfsobral]

Should it go here ?

If so how does it know to run on sheet INV

View attachment 115863

the code should go in the sheet15, not the module

it works fine with me as well

 

[dfsobral]

@Snakehips
Can you take a look at this code please.

My goal / intension was this.
I open the worksheet & in the cell G39 I see the text PIN CODE HERE
The user types in the cell say 123456
The invoice is printed.
All values in cells are cleared ready for the next invoice.
At this point I’m expecting to see the text PIN. CODE HERE but I see nothing.

Basically how can this code be edited so the text PIN CODE HERE can be shown again otherwise I might as well not use it as the a new user won’t see anything so won’t know to type there.

Once the cell values are cleared what can be down to say refresh / reload the code to be placed there again.

Thanks.

how does the order to print the invoice is given? with a procedure? if so you need to had the command to insert the text after your code

if you want to change the text when open the file you need to insert the code not in the "sheet15" but in "thisworkbook" in the open event

 

[ipbr21054]

The code is on the correct sheet & when it wasn’t working I was just asking a question.

So my new post basically asks that the PIN CODE BERW should be present or the user types a value in the cell this PIN CODE HERE TEXT then not shown.

My issue is once my invoice has been printed & cells cleared of there typed values cell G39 shows nothing.

So I’m asking how. An the code be refreshed so PIN CODE HERE becomes present again.

 

[ipbr21054]

I see you posted again whilst I was typing.

This is how it should work.

User opens worksheet.
Cell G39 should display PIN CODE HERE.
User types values in various cells & cell G39 being one of the cells.
Invoice is printed.
Various cells on sheet are cleared & G39 is one of them.
Now at this time cell G39 should show PIN CODE HERE but currently cell shows nothing.