Cell enters default text in/or a text box

[ph24]

Hi All,

I am trying to get a particular cell to have normal dimensions when not within that cell, but once opened, contains a default text preferably within a text box format/size.

How can this be done?

Thanks in advance for your help!

Regards
Phil

 

[John_w]

Put a Text Box (default name TextBox1) from the Controls Toolbox (ActiveX controls) in cell B4 on the sheet, then in the sheet's module put the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("B4").Address Then
        ActiveSheet.TextBox1.Visible = True
        ActiveSheet.TextBox1.Activate
    Else
        ActiveSheet.TextBox1.Visible = False
    End If
End Sub

 

[ph24]

Put a Text Box (default name TextBox1) from the Controls Toolbox (ActiveX controls) in cell B4 on the sheet, then in the sheet's module put the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("B4").Address Then
        ActiveSheet.TextBox1.Visible = True
        ActiveSheet.TextBox1.Activate
    Else
        ActiveSheet.TextBox1.Visible = False
    End If
End Sub

Maybe I'm just being thick, but do you mean just to enter a text box over the cell B4? If so, then I can followed the steps as mentioned. The text box just shows in full size regardless of being in the cell or not.

Your help is appreciated as I'm rather new to anything VBA based!

Thanks
Phil

 

[ph24]

Forget the above... Got the box to work perfectly.

I need seperate text boxes with default text to appear in ever cell in column B.
Is there a quick way to do this?

Thanks again!

Phil

 

[John_w]

I see you've twigged. Just to confirm - yes, in Design Mode manually place a text box control (from the Control Toolbox, not the Forms Toolbar) over cell B4 (for example).

 

[John_w]

Please be more specific - define 'every cell' in Column B. Do you mean all 65K rows?

To get you started try something like this. It adds a text box to column B rows 1 to 10 with default text, sized and positioned to sit over each cell. If the size and position is not correct then use the macro recorder with you manually adding a text box to get the correct required size and position and modify the code accordingly.

Sub Add_Text_Boxes()

    Dim r As Integer
    Dim tb As OLEObject
    
    For r = 1 To 10
        With ActiveSheet
            Set tb = .OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, DisplayAsIcon:=False, _
                Left:=.Cells(r, "B").Left + 2, Top:=.Cells(r, "B").Top + 2, Width:=.Cells(r, "B").Width - 4, Height:=.Cells(r, "B").Height - 4)
            tb.Object.Value = "my default text " & r
        End With
    Next
    
End Sub

 

[ph24]

Thanks John. Ill give it a go tomorrow and let you know how I get on.

By the way, I don't mean all cells, but about 200-300 rows in column B.

 

[John_w]

If you want all the text boxes in column B to act in the same way as the B4 text box previously discussed then use the following code instead. It creates invisible text boxes in cells B2:B10 on the active sheet and Worksheet_SelectionChange displays the associated text box when the user selects one of the B2:B10 cells and hides it when the user clicks another cell.

Module1

Sub Add_Text_Boxes()

    Dim r As Integer
    Dim tb As OLEObject
    
    For r = 2 To 10
        With ActiveSheet
            Set tb = .OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, DisplayAsIcon:=False, _
                Left:=.Cells(r, "B").Left + 2, Top:=.Cells(r, "B").Top + 2, Width:=.Cells(r, "B").Width - 4, Height:=.Cells(r, "B").Height - 4)
            tb.Object.Value = "my default text " & r
            tb.Name = "MyTextBox" & r
            tb.Visible = False
        End With
    Next
    
End Sub

Sheet module (e.g. Sheet1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static tb As OLEObject
    
    If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
        If Not tb Is Nothing Then tb.Visible = False
        Set tb = ActiveSheet.OLEObjects("MyTextBox" & Target.Row)
        tb.Visible = True
        tb.Activate
    Else
        If Not tb Is Nothing Then tb.Visible = False
    End If
    
End Sub

 

[ph24]

I'm trying to figure out where to drop these codes but I don't seem to be getting anywhere.
I've started again by adding an ActiveX Textbox over/around cell B4.

I go into Visual Basics, go to Sheet1 and place the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static tb As OLEObject

If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
If Not tb Is Nothing Then tb.Visible = False
Set tb = ActiveSheet.OLEObjects("MyTextBox" & Target.Row)
tb.Visible = True
tb.Activate
Else
If Not tb Is Nothing Then tb.Visible = False
End If

End Sub

... I then insert a new module (Module1) and placed the following code:

Sub Add_Text_Boxes()
Dim r As Integer
Dim tb As OLEObject

For r = 2 To 10
With ActiveSheet
Set tb = .OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=.Cells(r, "B").Left + 2, Top:=.Cells(r, "B").Top + 2, Width:=.Cells(r, "B").Width - 4, Height:=.Cells(r, "B").Height - 4)
tb.Object.Value = "my default text " & r
tb.Name = "MyTextBox" & r
tb.Visible = False
End With
Next

End Sub

Appreciate your patience.

 

[John_w]

No, don't add a text box to B4; the code adds it for you, plus the other text boxes as previously stated.

Start with a new workbook. Add the code - you are correct in the places you put it. Then run Add_Text_Boxes once. Then select (click) any of the cells B2:B10 on Sheet1 and the text box should appear. Click another cell B2:B10 and the associated text box should appear, hiding the previously visible text box. Click any other cell outside B2:B10 and the previously visible text box should disappear.