Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 high cpu usage, format cell, wrap text | Excel Discussion (Misc queries) | |||
Excel 2007 import text file VBA code - Origin:= | Excel Programming | |||
Text format - setting text colour with code | Excel Discussion (Misc queries) | |||
Excel 2007 - change size of text as a result of conditional format | Excel Discussion (Misc queries) | |||
How do I enter cell text in list format in Excel 2007? | Excel Discussion (Misc queries) |