Conditional Text?

[Sige]

Hi There,

Is the following possible?

I would like to show in my cell:

<Double Click Me>

when the cell is empty.



Like you could do with conditional formatting, Color the cell condition
the cell is empty.

Looking forward to your solutions,
Sige

 

[Norman Jones]

Hi Sige,

Try:

'=====================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("A1") '<<======= CHANGE

If Not Intersect(rng, Target) Is Nothing Then
If IsEmpty(rng) Then rng.Value = "Double Click Me"
End If
End Sub
'<<=====================

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

 

[Bob Phillips]

How about this for a bit of nonsense?

For all cells that you want this to apply to, create a complementary cell
referencing (such as =A1)

Then in the complementary cells, add a custom format of
General;-General;"<Double click me>"

Using the camera tool, copy images of the complementary cells over to the
actual cells.

 

[Norman Jones]

Hi Sige,

I omitted to disable / re-enable events, so the following version would be
preferable:

'=====================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo XIT
Set rng = Range("A1") '<<======= CHANGE

If Not Intersect(rng, Target) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(rng) Then rng.Value = "Double Click Me"
End If

XIT:
Application.EnableEvents = True
End Sub
'<<=====================

 

[Sige]

Hi Bob, Norman,

Thanks for your solutions ...

Bob: It works perfectly fine but -for once- I do not want to have a
formula in my cell to invoke it it ...
Norman: How can I invoke it? ... How does it get triggered?

Sige

 

[Norman Jones]

Hi Sige,

Norman: How can I invoke it? ... How does it get triggered?

If the code is pasted into the worksheet's code module, then the code should
respond automatically to changes in the sheet's A1 cell.

 

[Sige]

Hi Norman,
Thanks!

Thats what I thought and nothing happened ... my workbook got on Manual
Calc, maybe thats why.

Is it possible to set the range.... on the cell in last row in column
D?

Something like:
Dim intColumn As Integer, lngLastRow As Long
intColumn = 4
lngLastRow = .Cells(.Rows.Count, intColumn).End(xlUp).Row

But this obviously does not set the last cell...

Cheers Sige

 

[Sige]

This does not set the range neither...

Set rng = Range("d65536").End(xlUp).Address

 

[Norman Jones]

Hi Sige,

Thats what I thought and nothing happened ... my workbook got on
Manual Calc, maybe thats why.

The calculation setting should have no bearing.

Are you sure that the code has been pasted into the code module behind the
active worksheet?

If so, perhaps you have inadvertently disabled events. To eliminate this
possibility, paste the following code into a standard module:

Sub AAA
Application.EnableEvents = True
End Sub

After running this sub, try deleting the contents of cell A1, which should
produce the required message text in A1.

Is it possible to set the range.... on the cell in last row in
column D?

If your intention is that the cell after the last populated cell in column D
should display the message text, then try instead:

'=====================>>
Private Sub Worksheet_Calculate()
Dim rng As Range
Dim LastCell As Range
Dim rcell As Range
Dim sStr As String
Const IntCol As Long = 4

sStr = "Double Click Me"

Set LastCell = Cells(Me.Rows.Count, IntCol).End(xlUp)(2)
Set rng = Range(Cells(1, IntCol), LastCell)

On Error GoTo XIT

Application.EnableEvents = False

For Each rcell In rng.Cells
If rcell.Value = sStr Then rcell.ClearContents
Next rcell

Set LastCell = Cells(Me.Rows.Count, IntCol).End(xlUp)(2)
LastCell.Value = sStr

XIT:
Application.EnableEvents = True

End Sub
''<<=====================

Unlike the previous procedure, this code responds to the worksheet's
calculate event and, therefore, requires that calculation should not be set
to manual.

In order to coerce the calculation event, you might consider including a
volatile worksheet function (such as Now(), Indirect(), Offset)) somewhere
in the sheet.

 

[Tom Ogilvy]

Dim rng as Range
Set rng = Range("d65536").End(xlUp)

or

Dim sAddr as String
sAddr = Range("d65536").End(xlUp).Address

 

[Sige]

)))
That's my baby!!!

I do not want to exagerate but ....if i want to see the "Double Click
Me" in "5" other columns ( 7- 10 -15-...)

I repeat
Set LastCell = Cells(Me.Rows.Count, IntCol).End(xlUp)(2)

5 times? with 5 new IntCol constants?

Or is it possible to loop through the Cases?
Sige

 

[Norman Jones]

Hi Sige,

I do not want to exagerate but ....if i want to see the "Double Click
Me" in "5" other columns ( 7- 10 -15-...)

Try:

'=====================>>
Private Sub Worksheet_Calculate()
Dim rng As Range
Dim LastCell As Range
Dim rcell As Range
Dim sStr As String
Dim arr As Variant
Dim i As Long

arr = Array(4, 6, 7, 10, 15) '<<===== CHANGE

sStr = "Double Click Me"

On Error GoTo XIT
Application.EnableEvents = False

For i = LBound(arr) To UBound(arr)

Set LastCell = Cells(Me.Rows.Count, arr(i)).End(xlUp)(2)
Set rng = Range(Cells(1, arr(i)), LastCell)

For Each rcell In rng.Cells
If rcell.Value = sStr Then rcell.ClearContents
Next rcell

Set LastCell = Cells(Me.Rows.Count, arr(i)).End(xlUp)(2)
LastCell.Value = sStr

Next i

XIT:
Application.EnableEvents = True

End Sub
''<<=====================

Change the arr() values to reflect the columns of interest.

 

[Sige]

I am speechless;

Thx a trillion ... !!!


)))))))))
Sige

 

[Sige]

Nevertheless ... if conditional formatting is possible...
It might be worthwile for MS to create a Conditional Text-functionality
as well...

(ok I admit, more than 256 columns would be more usefull)

Crossing my fingers for next release ;o)

Anybody wants to make this call with MS?

 

[Tom Ogilvy]

Conditional text is incompatible with the philosophy of the conditional
format technique, so I think you can relax and forget it.