Format text entries with code - Excel 2007

[Marsh]

Good Morning,
A group of 22 users have asked if it possibe to insert bullets into text
entries in a column, for example column H. Inserting a Symbol is not
convenient. I did a search and found the following answer to this question
back in Feb 2006,
A simple code line

Set TB = UserForm1.TextBox1
TB.Value= Replace(TB.Value, CHR$(10), "* ")

should do the trick.
I am not a programmer. Could someone please let me know how I can implement
this into our file
THank you
Marsh

 

[Rick Rothstein]

As long as the cell entries in Column H are text constants, and not the
result of a formula, then you can use this macro to do what you want...

Sub InsertBullets()
Dim X As Long, DataEndRow As Long
Const DataStartRow As Long = 2
Const DataCol As String = "H"
Const SheetName As String = "Sheet2"
With Worksheets(SheetName)
DataEndRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For X = DataStartRow To DataEndRow
.Cells(X, DataCol).Value = Chr(159) & " " & .Cells(X, DataCol).Value
.Cells(X, DataCol).Characters(1, 1).Font.Name = "Wingdings"
Next
End With
End Sub

Note: You have to change the example values I used after the equal signs for
the three Const statements in the above code to reflect the actual starting
row for your data, the actual column that the data is in, and the worksheet
name that the data is on.

If you are not familiar with working with macros, do the following... When
on any worksheet, press Alt+F11 which will take you to the VB editor, then
click Insert/Module on the VB editor's menu bar and copy/pasted the above
code into the code window that opened up. Now, back at your worksheet you
want to perform use this macro on, then press Alt+F8 and select
InsertBullets from the list, then click the Run button.

 

[J_Knowles]

If you want a formula, you can place a bullet in column H by using the
char(149) for a bullet symbol and concatenate some text.

cell H1 =CHAR(149) &" "&G1

substitue your cell reference for G1

HTH

 

[Marsh]

Thanks, that works well but is not pratical for us. All columns are being
used out to column AE. To have the users go out beyond AE to enter the text
and then move back to the range near column H will greatly effect production,
negatively. THey would have the bullets, but data entry would be to slow to
accomadate the high volume required by our operation.
I am still searching for a coding method that a beginner in VBA can implement.

 

[Rick Rothstein]

Try this macro...

Sub InsertBullets()
Dim X As Long, DataEndRow As Long, Col As Range
Const DataStartRow As Long = 2
Const DataCols As String = "H:AE"
Const SheetName As String = "Sheet2"
With Worksheets(SheetName)
For Each Col In Columns(DataCols)
DataEndRow = .Cells(.Rows.Count, Col.Column).End(xlUp).Row
For X = DataStartRow To DataEndRow
If Len(.Cells(X, Col.Column).Value) Then .Cells(X, Col.Column). _
Value = Chr(149) & " " & .Cells(X, Col.Column).Value
Next
Next
End With
End Sub

Note: You have to change the example values I used after the equal signs for
the three Const statements in the above code to reflect the actual starting
row for your data, the actual column range that the data is in, and the
worksheet name that the data is on.

If you are not familiar with working with macros, do the following... When
on any worksheet, press Alt+F11 which will take you to the VB editor, then
click Insert/Module on the VB editor's menu bar and copy/pasted the above
code into the code window that opened up. Now, back at your worksheet you
want to perform use this macro on, then press Alt+F8 and select
InsertBullets from the list, then click the Run button.

 

[Rick Rothstein]

I forgot to mention... you can also assign this macro to a button to make
things easier for your users.

 

[Marsh]

Thanks
I got this one to work, the second version could not be implemented, and I
do know why.

 

[Marsh]

Sorry, that should be I do not know why :-(

Thanks
I got this one to work, the second version could not be implemented, and I
do know why.

 

[PA]

I have done this before using AutoCorrect.
Simply have autocorrect replace a preselected character or string of
characters with the appropriate symbol. YOu can also get the "bullet" on
multiple lines in the same cell after an <Alt>+<Enter>