Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Format text entries with code - Excel 2007

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Format text entries with code - Excel 2007

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.

--
Rick (MVP - Excel)


"Marsh" wrote in message
...
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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default Format text entries with code - Excel 2007

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
--
Data Hog


"Marsh" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Format text entries with code - Excel 2007

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.

"J_Knowles" wrote:

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
--
Data Hog


"Marsh" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Format text entries with code - Excel 2007

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 (MVP - Excel)


"Marsh" wrote in message
...
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.

"J_Knowles" wrote:

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
--
Data Hog


"Marsh" wrote:

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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Format text entries with code - Excel 2007

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Marsh" wrote in message
...
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.

"J_Knowles" wrote:

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
--
Data Hog


"Marsh" wrote:

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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Format text entries with code - Excel 2007

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

"Rick Rothstein" wrote:

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 (MVP - Excel)


"Marsh" wrote in message
...
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.

"J_Knowles" wrote:

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
--
Data Hog


"Marsh" wrote:

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


.

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Format text entries with code - Excel 2007


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

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

"Rick Rothstein" wrote:

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 (MVP - Excel)


"Marsh" wrote in message
...
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.

"J_Knowles" wrote:

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
--
Data Hog


"Marsh" wrote:

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


.

  #9   Report Post  
Posted to microsoft.public.excel.programming
PA PA is offline
external usenet poster
 
Posts: 101
Default Format text entries with code - Excel 2007

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

"Marsh" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 high cpu usage, format cell, wrap text Michael S. Wall Excel Discussion (Misc queries) 9 October 16th 09 07:34 PM
Excel 2007 import text file VBA code - Origin:= NLC Excel Programming 1 August 19th 09 04:21 PM
Text format - setting text colour with code NDBC Excel Discussion (Misc queries) 6 July 1st 09 10:15 PM
Excel 2007 - change size of text as a result of conditional format Emece Excel Discussion (Misc queries) 4 December 4th 08 06:12 PM
How do I enter cell text in list format in Excel 2007? John Excel Discussion (Misc queries) 1 March 3rd 08 10:09 PM


All times are GMT +1. The time now is 06:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"