Stop for input

[Guest]

have built following routines need to be able to input text into 'A2' When it
is sized and formatted it will be right for a mail merge Not knowledgeable of
how to pause for input then continue. Want one procedure as it will be called
many times. From different locations.
Hope you can understand me
Thanks
(e-mail address removed)


Option Explicit
Sub final()
'
' Desc_1 Macro
' Macro recorded 1/11/2007 by Default
With Range("N16").Select 'Active cell later
Sheets("Desc").Select
Range("A2").Select This is cell I want to input text to
save in next with
Rows("2:2").RowHeight = 78
Columns("A:A").ColumnWidth = 39
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Locked = True
Selection.FormulaHidden = False
End With


'
' Desc_2 Macro
' Macro recorded 1/11/2007 by Default
'
With Range("A2").Select
Selection.Cut
Sheets("Data").Select
Range("M16").Select 'Active cell Later offset
ActiveSheet.Paste
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = True
.MergeCells = False
End With
Selection.Locked = True
Selection.FormulaHidden = False
End With
End Sub

 

[Jon Peltier]

Use Application.InputBox to solicit input from the user, then put this input
value into the target cell.

- Jon

 

[Guest]

I am limited as to amount of text. I have formated a cell on 'Desc' to limit
the text. Not sure that inputbox can be programed as to limit text structure.
Have formated a cell 39 wide x 78 height this fits where I need it. would
have to dig to get charaters There 4 rows of text. Is there a way to do this?

 

[NickHK]

Curt,
If you are using a fixed width font (doubtful), then you can know how many
characters will fit in that cell. Then just use
<DestinationCell>.Value=Left(ReturnString,MaxChars)

However, proportional fonts are more difficult, as it will depend on which
characters are used. Excel can tell you with a function like below:

Private Sub CommandButton1_Click()
Const SomeText As String = "This is some text that has to fit in the preset
cell. If it is too long, you have to decide what to do. Truncate ?"

MsgBox TestTextForCell(Range("H3"), SomeText)
End Sub

Private Function TestTextForCell(DestinationCell As Range, DesiredText As
String) As Boolean

'Preferably on a different (hidden) sheet
With Worksheets(2).Range("Z1")
'Set to wrap text etc to match
.HorizontalAlignment = DestinationCell.HorizontalAlignment
.VerticalAlignment = DestinationCell.VerticalAlignment
.WrapText = True

'Set the same width as the destination
.ColumnWidth = DestinationCell.ColumnWidth

.Value = DesiredText
.EntireRow.AutoFit

TestTextForCell = .RowHeight <= DestinationCell.RowHeight
End With

End Function

NickHK