Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Fill-In (2000 SR2)

    What I would like to do with a macro is have Column C update based on the user entry. The user will enter either a letter 'M' or 'C' and I would like the field to populate with 'Micro' or 'Chemistry' accordingly and then tab to the next field.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text Fill-In (2000 SR2)

    If you change the column heading to something that doesn't begin with a C or an M, and enter Micro and Chemistry in cells below it, Excel's autocomplete feature will change C to Chemistry and M to Micro in subsequent cells in column C. No code needed.

    Another option is to right-click the sheet tab and select View Code from the popup menu. Copy/paste the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("C:C")).Cells
    Select Case UCase(Left(oCell, 1))
    Case "C"
    oCell = "Chemistry"
    Case "M"
    oCell = "Micro"
    End Select
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    If the user enters M or C in a cell in column C and presses Enter or Tab, the code will complete the entry.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Fill-In (2000 SR2)

    Thanks Hans! I am making this for another company, so I think I was wanting to make things "cooler" than they need be.

    I think the autocomplete feature will probably be the better way to go this time.

    I'm sure the code will come in handy sometime soon! Thanks!

    Chris

Posting Permissions

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