Adding default values to cells

[willhaywood]

Hi All,

I am having some difficulty adding a default value to a cell.

What I want to do is have a cell that will read "Enter value here" if nothing has been added, but display the users text when changed.

I've been playing with the IF statement feature for a while, but am getting nowhere.

Any ideas?

Thanks

Will

 

[Jonmo1]

Welcome to the board...

Try
Right click the Cell, "Insert Comment"

 

[willhaywood]

Thanks,

I did consider that, but there are two many instances where I need to have an "enter value here" cell on the sheet I am using and it becomes too clutered

Will

 

[Mike Blackman]

Hi Will,

Is it the entire sheet or just certain ranges? You could maybe use something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B2:B100")) Is Nothing Then
    For Each cell In Range("B2:B100")
        If cell.Value = "" Then
            cell.Value = "Please Enter value here"
        End If
        
    Next cell
    DoEvents
End If

End Sub

 

[willhaywood]

Hi mike,

Thanks for your response. It is certain ranges within the spreadsheet. I'm a bit of a VB noob, where should I be putting the code?

Thanks

Will

 

[Mike Blackman]

Hi,

From Excel, Right click on the sheet tab this is relevant for and click on view code.

Paste this in;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, [B][COLOR="Red"]Range("B2:B100")[/COLOR][/B]) Is Nothing Then
    For Each cell In [B][COLOR="red"]Range("B2:B100")[/COLOR][/B]
        If cell.Value = "" Then
            cell.Value = "Please Enter value here"
        End If
        
    Next cell
    DoEvents
End If

End Sub

Please note that the range of cells that you wish to affect are Bold and Red and you need to change both sets.

If you have two seperate ranges of cells or more you can ammend the code as such;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, [B][COLOR="Red"]Range("B2:B100,D4:D8,G8:G14")[/COLOR][/B]) Is Nothing Then
    For Each cell In [B][COLOR="red"]Range("B2:B100,D4:D8,G8:G14")[/COLOR][/B]
        If cell.Value = "" Then
            cell.Value = "Please Enter value here"
        End If
        
    Next cell
    DoEvents
End If

End Sub

Give it a try and see if it solves your problem.

 

[willhaywood]

Hi Mike,

Thanks a lot for your response, unfortunately I cant get it to work with my excel 2003

Will

 

[Mike Blackman]

Hi Will,

What is happening? Where is it falling over?

 

[willhaywood]

Hi Mike,

I have the code in the "this workbook" window (as pops up when view code is selected), but there is no change to the cells selected. Both sets of values are the same (eg b1:b100) but nothing is changing.

Thanks

Will

 

[jproffer]

That needs to be in the workSHEET code module , not the workBOOK code module. Right click on the applicable sheet tab at the bottom of your window. "View Code" is the very bottom choice.