Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

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

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Diana,

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

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    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.
    Last edited by soadokdls; 01-19-10 at 14:25.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •