text in the background

[B4L4KS]

hi

is there a way of putting text in the background of a cell so when i start typing in the cell it disappears, but then re-appears when there is no text

thanks

kristian

 

[Airfix9]

Assuming the cell is A1, amend to suit. This can only be accomplished using VBA.

Open the VB Editor and go to the sheet module for the sheet where you want this to happen. Put in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value = vbNullString Then
            Application.EnableEvents = False
            Range("A1").Value = "Enter Text Here"
            Application.EnableEvents = True
        End If
    End If
End Sub

Edit to suit.

 

[energman58]

Not that I am aware of - you can use conditional formatting to colour a cell if it has no data in or to make it transparent so you can hide/unhide backgound detail but alligning the background with the cells properly would be nearly impossible unless your cells were huge.

Can you do it with text boxes and some VBA?

 

[B4L4KS]

hi airfix

i have tried your VB code and i cant get it to work could you explain how it works and what all the different aspect of them are, this is what i want it for below

i want the appropriated names in the boxes, before you write in them

thanks

kristian

 

[Airfix9]

Is there any reason why you have merged cells? Makes the problem more difficult if you do!

If you can re-design with cells unmerged, that would help.

If you did, and assuming that the entries remain in column N, use this code (now with some notes):

Private Sub Worksheet_Change(ByVal Target As Range)

' this looks to see if the changed cell is in the range that we are interested in
    If Not Intersect(Target, Range("N12:N13,N17:N20")) Is Nothing Then

' if it is, it checks to see if there is no entry in that cell
        If Target.Value = vbNullString Then
            
' if there is no entry, EnableEvents is switched off to prevent repeated firings
            Application.EnableEvents = False
            
' this now looks to find out which row we are in, because the words to enter vary by row
            Select Case Target.Row

' this enters the required value to the cell in question
                Case 12
                    Target.Value = "First Name"
                Case 13
                    Target.Value = "Second Name"
                Case 17
                    Target.Value = "1st Line"
                Case 18
                    Target.Value = "2nd Line"
                Case 19
                    Target.Value = "Post Code"
                Case 20
                    Target.Value = "County"

' finally a "safety net" to ensure that we have missed no cells
                Case Else
                    'do nothing
            End Select
            
' this switches EnableEvents back on so that it will fire again when needed
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[B4L4KS]

hi

thanks for the help, but one last question

do i have to assign this code to a box, cell or what

thanks

kristian

 

[Airfix9]

Just post it in the sheet module for the sheet that you are working on

 

[B4L4KS]

hi

sorry for being a bit thick but where is the sheet module, is it in the visual basic

im lost

thanks

kristian

 

[Airfix9]

In Excel, press ALT+F11 to open the VB Editor.

Find the Project Explorer, if it isn't there, press CTRL+R

Find the workbook you are working on and find the submenu "Microsoft Excel Objects".

There will be one for each sheet in the workbook and one called "ThisWorkbook". Find the one for the sheet you are on and double-click it to open it.

Copy the code and paste into the opened module.

 

[B4L4KS]

hi

thanks for all the help

most appreciated

kristian