Results 1 to 7 of 7
  1. #1
    ccj
    Guest

    Format cells with same beginning characters

    I have some cells in a workbook that all use the same letters (WON-) in the first part of the entry but the last part is different. For example the first cell will be WON-abc and the second will be WON-def. I can create cells with the first letters in them but adding the subsequent letters is clumsy since I have to manually go to the end of the field to enter the next part. Is there a way I can create cells with the first letters of the cell in place and the cursor goes to the next space after them so I can simply begin data entry. Thanks for your help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Format cells with same beginning characters

    Hi,

    If the prefix letters are just for display and are not important as text values the following custom format should work :-

    "WON-"@

    for the example you gave.

    Hope this is adequate,

    Andrew

  3. #3
    ccj
    Guest

    Re: Format cells with same beginning characters

    Perfect. That is just what I was looking for. Thanks much.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells with same beginning characters

    Here is some code that replaces your entry with the same entry but "WON" is added in front of it. It makes use of the change event of the worksheet. So you should put this code on the right place in the VBE. Look for the WorkSheet_Event subroutine of the worksheet where you want to enter your data. Then paste this code in the VBE (Visual Basic Editor). Here I defined R as column A. If you want to expand this, go ahead; e.g. Set R = Range("A:G") defines the range R as the columns A to G. The Target range has the address of the cell that you are changing on your worksheet. I define NewR as the intersection of Target and R. If this intersection is not empty, then I change the contents of the intersection by adding "WON" in front of it. I make a difference between a text content and something else using the vartype command. I disable the events because I change the contents of the cell by using code. If the events were not disabled, the worksheet_change event would be fired again which is not what is wanted here. Of course, after changing the cell content I have to enable the events again.

    Hope this helps.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    Dim NewR As Range
    Set R = Range("A:A")
    Set NewR = Application.Intersect(Target, R)
    If Not NewR Is Nothing Then
    Application.EnableEvents = False
    If VarType(NewR.Value) <> vbString Then
    NewR.Value = "WON" & Str$(NewR.Value)
    Else
    NewR.Value = "WON" & NewR.Value
    End If
    Application.EnableEvents = True
    End If
    End Sub

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cells with same beginning characters

    If you don't really need to have the prefix in the cells, then the method Andrew gave you should work. If you do need the prefix in the cell, then there are several possibilities:

    1- Put the prefix in the cell, and then when you go to the cell just hit the F2 key and the cursor should be properly placed to begin typing the rest.

    2- You can type all of the variable data in the cells without the prefix. If this is in column A, the choose an empty column and put the following formula in the first cell:

    <pre>="WON-" & A1
    </pre>


    Fill this formula down the column for as many cells as there are variable values. This should display what you want. Then select this column and copy it, then select the original column and do a Edit | Paste Special and select Values. You can then delete the column with the formulas.

    3- You can write a event macro for the worksheet change event that will insert the "WON-" in front of whatever you type in a cell every time the cell changes if the new value is not null.
    Legare Coleman

  6. #6
    ccj
    Guest

    Re: Format cells with same beginning characters

    Thanks that will help. I'm starting to work with macros and this is good.

  7. #7
    ccj
    Guest

    Re: Format cells with same beginning characters

    I'm impressed with the great responses I got. Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •