Clear contents of cell if clicked, then repopulate if nothing is entered

soadokdls · #1 (**permalink**) 01-18-10, 20:14

So right now I have the following:

If Target.Address = "$Q$65:$T$65" Then
Selection.ClearContents

Which works fine. But I want something that will repopulate that range with "Type member # here" if they navigate away from that cell without entering anything. Any ideas?

Thanks,
Diana

Colin Legg · #2 (**permalink**) 01-19-10, 05:48

Hi Diana,

Which worksheet event handler are you using the above code in, and is Q65:T65 a merged cell?

soadokdls · #3 (**permalink**) 01-19-10, 13:25

I've made some progress, but it's still not doing what I want. And yes, that is a merged cell which I've now named "Member1" and I'm using that instead. Here is everything I have so far:

Dim vSave, cSave As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
cSave = "Type member # here"
If ActiveCell.Address = Range("Member1").Address Then
'Had to change to activecell.address because I wanted to use a named cell and it didn't work with target.address
If ActiveCell.Value <> cSave Then vSave = Selection.Value
'This is not working but if I exclude it, the clearcontents works as well as repopulating with "Type member # here" if the cell is blank
Selection.ClearContents
ActiveCell.Font.ColorIndex = 1
Else
If Range("Member1").Value = "" Then
Range("Member1").Value = cSave
Range("Member1").Font.Color = RGB(150, 150, 150)
End If
End If
End Sub

Basically, I want the people to be able to click in the cell, which clears "Type member # here," then type in their member number. If they navigate away from the cell without typing anything, I want it to put "Type member # here" back in the cell. However, if they do type a number in, leave the cell, and then go back to the cell for whatever reason, I don't want it to clear the contents (but if they delete what's in there so it's blank again I want it to put "Type member # here" again. So basically, if there is anything other than cSave, I don't want it to clearcontents.

Thanks so much,
Diana

Colin Legg · #4 (**permalink**) 01-19-10, 14:05

Hi Diana,

Try this code in the worksheet's class module:

Code:

Option Explicit

Private pLastCell As Range

Private Sub Worksheet_Activate()
    Set pLastCell = ActiveCell.MergeArea
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const sSTART As String = "Type member #1 here"
    Const sMEMBER As String = "Member1"
    
    On Error GoTo ErrorHandler
    
    Application.EnableEvents = False

    
    
    'has the Member1 cell been selected?
    If Target.Address = Range(sMEMBER).Address Then
        
        If CStr(Target.Cells(1).Value) = sSTART Then Target.Cells(1).Value = vbNullString
        
    'has the Member1 cell been exited?
    Else
        If Not pLastCell Is Nothing Then
            If pLastCell.Address = Range(sMEMBER).Address Then
                If IsEmpty(Target.Cells(1)) Then pLastCell.Cells(1).Value = sSTART
            End If
        End If
    End If

ErrorExit:
    Application.EnableEvents = True
    Set pLastCell = Target
    
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume ErrorExit
    
End Sub

To make this perfect you would have to trap events at a higher level. By that, I mean that certain actions such as opening the workbook or switching between open workbooks will not raise this worksheet's activate event, so there are certain circumstances where this code will not immediately kick in. But I think it should be a good enough starting point.

Hope that helps...

soadokdls · #5 (**permalink**) 01-19-10, 14:18

Alright I figured it out (an easy way):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
cSave = "Type member # here"
If ActiveCell.Address = Range("Member1").Address Then
If ActiveCell.Value = cSave Then Selection.ClearContents
ActiveCell.Font.ColorIndex = 1
Else
If Range("Member1").Value = "" Then
Range("Member1").Value = cSave
Range("Member1").Font.Color = RGB(150, 150, 150)
End If
End If
End Sub

By the way, how to you get the indents to work in this forum? I tried using the increase indent feature but that actually made it so everything was on one line, and it deletes my spaces when it posts.

Colin Legg · #6 (**permalink**) 01-19-10, 14:37

Hi,

Quote:

By the way, how to you get the indents to work in this forum? I tried using the increase indent feature but that actually made it so everything was on one line, and it deletes my spaces when it posts.

You have to enclose the code with the code tags for white space to be observed. There's a list of bbcode tags and examples here:
dBforums - BB Code List

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech