+ Reply to Thread
Results 1 to 3 of 3

Can I enter a "Y" and return "Yes" in the same cell? PART 2

  1. #1
    rbbbbeee
    Guest

    Can I enter a "Y" and return "Yes" in the same cell? PART 2

    Last week I asked the question above and I got a couple great responces.
    However, the programming response allowed me to only deal with one column and
    I need to be able to enter a "Y" into any cell within 2 columns in the
    worksheet and have it return in the same cell the word "YES". Can someone
    please help me?

    It was also suggested that I have the auto fill turned on and I would only
    have to type the word "YES" once then "Y" and enter after that. This doesn't
    seem to work in all areas of the worksheet. I end up having to type the
    whole word in each section of the worksheet. Am I missing a setting or
    something?

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi paste this in to the worksheet module, change the range to suit.

    HTH

    Regards,
    Simon

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim mycell
    Dim rng As Range
    Set rng = Range("A1:C20")
    For Each mycell In rng
    If LCase(mycell.Text) = "y" Then
    mycell.Value = "Yes"
    ElseIf LCase(mycell.Text) <> "y" Then Exit Sub
    End If
    Next mycell
    End Sub

  3. #3
    lexcel
    Guest

    Re: Can I enter a "Y" and return "Yes" in the same cell? PART 2

    Right click on the tab of the sheet you wish to have this behaviour.
    In the menu click "View Code"
    In the VBA window that opens paste the following code (from '===== to
    '=====)

    ' ==========================================
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range

    Application.EnableEvents = False
    For Each cell In Target
    Select Case LCase(cell.Text)
    Case "y", "yes"
    cell = "Yes"
    Case "n", "no"
    cell = "No"
    End Select
    Next cell
    Application.EnableEvents = True
    End Sub
    ' ==========================================

    I made it so that it will also take "no" for an answer. If you don't
    want this, remove the lines
    Case "n", "no"
    cell = "No"

    Greetz,

    Lex


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1