Textbox text

[Francis Hookam]

Textbox text

Several questions about Textbox

In a door schedule I am working on, project details (name, job no, drawing
no, date, etc) are entered in B1:B7 of the Input sheet and I need to
transfer these details to a textbox on the Schedule sheet

There is no problem with writing a macro to produce the textbox to contain
these details but there are some questions regarding the format of the text
within the textbox

1
It would help the general appearance of the textbox if some words could be
bold ­ for instance the project title contained in Cells(1, 2) ­ is it
possible to specify within the concatenation which text will be bold?

2
I need to replace the previous textbox with the new one so I have given the
textbox a name so it can be deleted before the new one is formatted. I have
forgotten how to ignore the Œdelete¹ if the previous textbox had already
been deleted and so avoid the error which occurs

3
For reasons I cannot fathom naming the textbox seems to interfere with the
TextFrame.MarginŠ settings I wanted
(Since writing this I find that Set AutoShape Defaults beforehand sorts this
out but I should still like to know if the margins can be set as I have
attempted since the Workbook will run on other computers which may not be
set with the same margins)

4
Dates in the worksheet are formatted ³d mmm yy² but appear in the
concatenated text in the textbox as ³02/05/2005². One way round this is to
format the date cell as ³Text² and type the date as ³2 May 05² but that
might confuse the person entering the data, expecting normal ³3/5/05² (or
simply ³3/5²) entry in an already date formatted cell. Anyway round that?

5
You will see I have added spaces ­
& Chr(10) & "Drawing no.: " & Cells(5, 2) _
to try to line up the data but, without a fixed space font, that does not
work well ­ any way of introducing a tab character in the concatenation?
Recording tabbing does not show a tab Chr in the code as it did Chr(10) for
the line return, it simply added a miscellaneous number of spaces

6
We are rightly encouraged to format code so actions are carried out without
actually selecting what is being acted upon. Although I can normally achieve
the transformation between recorded actions and direct instructions and
often can write directly without recording, in this case I am at a loss to
do so ­ please show me how the following could be improved

What a fantastic programme ­ the insight you give is appreciated very much

Francis Hookham

Sub ProjectDetails()
'goto InputSheet
Sheets("InputSheet").Select
'concatenate project details into varable 'Project'
sProject = "Project: " & Chr(10) & Cells(1, 2) _
& Chr(10) & "Job no.: " & Cells(2, 2) _
& Chr(10) & "Drawing title: " & Cells(3, 2) _
& Chr(10) & "Drawing date: " & Cells(4, 2) _
& Chr(10) & "Drawing no.: " & Cells(5, 2) _
& Chr(10) & "Revision: " & Cells(6, 2) _
& Chr(10) & "Revision date.: " & Cells(7, 2)
'goto Shedule sheet
Sheets("Schedule").Select
'delete previous textbox
ActiveSheet.Shapes("JobTitleTextBox").Delete
'form and name new textbox
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
20, 20, 300, 150).Select
'name the textbox
Selection.Name = "JobTitleTextBox"
'insert concatenated text
Selection.Characters.Text = sProject
'(the following 'With-End with' was generated during the early recording
'part of the macro. It seems to make no difference if it is there or not.
'If, if, the normal/bold text could be determined in the concatenation
variable
'this certainly would not be needed)
' With Selection.Characters(Start:=1, Length:=8).Font
' .Name = "Arial"
' .FontStyle = "Regular"
' .Size = 12
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = xlAutomatic
' End With
'set margins of textbox
Selection.ShapeRange.TextFrame.MarginLeft = 7
Selection.ShapeRange.TextFrame.MarginRight = 7
Selection.ShapeRange.TextFrame.MarginTop = 7
Selection.ShapeRange.TextFrame.MarginBottom = 7
'deselect textbox
Cells(1, 1).Select
End Sub

 

[JE McGimpsey]

There is no problem with writing a macro to produce the textbox to contain
these details but there are some questions regarding the format of the text
within the textbox

Sorry this has been so late. I don't have time to give a comprehensive
answer to all your questions, but perhaps I can answer them piecemeal...

1. It would help the general appearance of the textbox if some words
could be bold ­ for instance the project title contained in Cells(1,
2) ­ is it possible to specify within the concatenation which text
will be bold?

You can't specify within the string itself. In my apps, I approach it
more like

Const sTB_STR1 As String = "Project: "
Const sTB_STR2 As String = "Drawing title: "
Const sTB_STR3 As String = "Drawing date: "
'etc.
Dim vCells As Variant
Dim sTB_Str As String
Dim nNL As Long

nNL = Len(vbNewLine)
With ActiveSheet
vCells = .Cells(1, 2).Resize(7, 1).Value
sTB_Str = sTB_STR1 & vbNewLine & vCells(1, 1) & _
vbNewLine & sTB_STR2 & vCells(2, 1) & _
vbNewLine & sTB_STR3 & vCells(3, 1) 'etc.
With .Shapes("JobTitleTextBox").TextFrame
With .Characters
.Text = sTB_Str
With .Font
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Subscript = False
.Superscript = False
.OutlineFont = False
.Shadow = False
.Underline = False
.ColorIndex = xlColorIndexAutomatic
End WIth
End With
.Characters(Len(sTB_STR1) + nNL + 1, _
Len(vCells(1, 1))).Font.Bold = True
End With
End With

 

[JE McGimpsey]

I need to replace the previous textbox with the new one so I have given the
textbox a name so it can be deleted before the new one is formatted. I have
forgotten how to ignore the Œdelete¹ if the previous textbox had already
been deleted and so avoid the error which occurs

One way:

On Error Resume Next
ActiveSheet.TextBoxes("JobTitleTextBox").Delete
On Error GoTo 0

an alternative which avoids the error and resetting the error handler:

Dim i As Long
With ActiveSheet.TextBoxes
For i = 1 To .Count
If .Item(i).Name = "JobTitleTextBox" Then
.Item(i).Delete
Exit For
End If
Next i
End With

 

[JE McGimpsey]

4
Dates in the worksheet are formatted ³d mmm yy² but appear in the
concatenated text in the textbox as ³02/05/2005². One way round this is to
format the date cell as ³Text² and type the date as ³2 May 05² but that
might confuse the person entering the data, expecting normal ³3/5/05² (or
simply ³3/5²) entry in an already date formatted cell. Anyway round that?

One way:

'...
& vbNewLine & "Drawing date: " & Format(Cells(4, 2).Value, "d mmm yy") _
'...