select case to replace text with different text

I'm trying to use a Select Case in a Private Sub Worksheet_Change event to 
do the following:

if I type w in a cell in col B, I want to replace it with WIDGETS
if I type g in a cell in col B, I want to replace it with GIDGETS

seems like it should be simple but I can't come up with the code.




0
John
1/10/2010 4:28:54 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
823 Views

Similar Articles

[PageSpeed] 48

On Sun, 10 Jan 2010 10:28:54 -0600, "John" <nodak457@yahoo.com> wrote:

>I'm trying to use a Select Case in a Private Sub Worksheet_Change event to 
>do the following:
>
>if I type w in a cell in col B, I want to replace it with WIDGETS
>if I type g in a cell in col B, I want to replace it with GIDGETS
>
>seems like it should be simple but I can't come up with the code.
>
>
>

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(ActiveSheet.Range("B:B"), Target) Is Nothing Then
    Select Case Target.Value
      Case "w"
        Target.Value = "WIDGETS"
      Case "g"
        Target.Value = "GIDGETS"
    End Select
  End If
End Sub

Hope this helps / Lars-�ke
0
Lars
1/10/2010 4:50:06 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B"     '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        Select Case Target.Value

            Case "w":       Target.Value = "WIDGETS"
            Case "g":       Target.Value = "GIDGETS"
        End Select
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

---
HTH

Bob Phillips

"John" <nodak457@yahoo.com> wrote in message 
news:n9qdnY4OkK7bndfWnZ2dnUVZ_s6dnZ2d@vnet-inc.com...
> I'm trying to use a Select Case in a Private Sub Worksheet_Change event to 
> do the following:
>
> if I type w in a cell in col B, I want to replace it with WIDGETS
> if I type g in a cell in col B, I want to replace it with GIDGETS
>
> seems like it should be simple but I can't come up with the code.
>
>
>
> 


0
Bob
1/10/2010 4:56:27 PM
I know you asked for a Select Case solution, but (assuming you are only 
talking about single letter codes) there is a more compact coding solution 
available using the Choose function for what you want to do...

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  If Target.Column = 2 Then Target.Value = Choose(InStr(1, "wgd", _
     Target.Value, vbTextCompare), "WIDGETS", "GIDGETS", "DIGITS")
  Application.EnableEvents = True
End Sub

I added one more item to make it more obvious how to structure the code. The 
"wgd" correspond to the codes you want to type in the cell ("w" for WIDGETS, 
"g" for GIDGETS and "d" for DIGITS) where the letter you type into the cell 
can be either upper or lower case (that is what the vbTextCompare argument 
in the InStr function call makes possible). The order of the letters in the 
"wgd" string must be the same as the listed order of what you want these 
letters replaced with in the arguments to the Choose function (as my code 
shows).

-- 
Rick (MVP - Excel)


"John" <nodak457@yahoo.com> wrote in message 
news:n9qdnY4OkK7bndfWnZ2dnUVZ_s6dnZ2d@vnet-inc.com...
> I'm trying to use a Select Case in a Private Sub Worksheet_Change event to 
> do the following:
>
> if I type w in a cell in col B, I want to replace it with WIDGETS
> if I type g in a cell in col B, I want to replace it with GIDGETS
>
> seems like it should be simple but I can't come up with the code.
>
>
>
> 

0
Rick
1/10/2010 5:50:03 PM
Reply:

Similar Artilces:

shared pop3 account with different outlook clients.
Hi! We are using different versions of Outlook. The versions used are Outlook Express 5.5, 6.0sp1 and Outlook 2000 and Outlook XP. The problem is a shared pop3 account, which all the clients receive (and leave a copy to). Some clients receive the mail properly, but some clients receive corrupt messages, i.e. messages with no subject and sender fields and a blank message body. Now, i found a corresponding KB article, but we dont have any IE 6.0:s installed. Is there any other software which might have that mlang.dll problem, or should i upgrade all browsers to 6.0SP1? Thanks in adv...

selecting cells #4
When holding down ctrl to select a number of cells to format them or see the sum in the bottom of the screen - and you accidentally select one you dont want - is there a way to unselect that one without starting over ? Thanks, Yosef Take a look at this: http://www.cpearson.com/excel/unselect.htm there's no builtin way to do it, other than starting over... -- Regards, Juan Pablo Gonz�lez Excel MVP "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:9EA0DDD8-4668-430B-BB5E-C15549FC9346@microsoft.com... > When holding down ctrl to select a number ...

New to XSLT and can't select even the first node... Any Help?
Hey, brand new to XSL and XSLT. I've downloaded an evaluation copy of XML Spy to debug my transformation, but I can't get to frist base. Can anyone help my get straightened out? My XSLT is: <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" /> <xsl:template match="/"> <xsl:for-each select="DataSet_Studio_Appt_Dates/Studio_Appt_Date_Times"> <ItWorked>YEAH</ItWorked> </xsl:for-each> </xsl:t...

add number to existing text numbers
I have 2000 columns with accounting type text numbers in each cell, i.e. 0345999, 058956, ect. I want to add an "0" in front of each account number. Anyone know how to do this withount going into each cell and doing it by hand? Hi one way: use a helper column with the formula ="0" & A1 copy this down for all rows, copy this column and insert it with 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany Stacy wrote: > I have 2000 columns with accounting type text numbers in > each cell, i.e. 0345999, 058956, ect. > > I wan...

display selection in form
Hi, In a form I display products. One of the fields in the records is "supplier". Beneath this field I placed a commandbutton "details". Clicking this button shows details of the supplier. These details are stored in a query called "supplierinfo". I built a form "frm SuppInfo"that displays the info of this query. How can I display only the matching record of "supplierinfo" Example: the value of "supplier" in the form products is "Johnson". Clicking the commandbutton must display the record in the query where the val...

Password different using RetrieveGlobals
Hi, I'm experiencing a weird problem when using RetrieveGlobals. The connection string from retrieveuserinfo is showing totally different password from what I used to login so I can't the connection to run any queries. The only account in which the password match is for "sa" account but not if I login using any other valid accounts. Have anyone experienced this issue and/or know a solution? TIA John. ------=_NextPart_0001_0D6BE0B7 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi John I am assuming that you are using Retrieveglobals.dll with Great Plai...

data validation fonts differ in drop down box
I am using Excel 2003. I have a group of cells with a data validation "list". That list contains 3 options -- check mark, "X", and "box" -- in Webdings. When you click the drop down box to make a selection the options are in alpha-numeric characters (Arial font(?)) and not Webdings. So my "check mark" becomes an "a" and my "X" becomes an "r". How can I get the drop down box to display the same font as the one in the list? Both the drop down box cells and the "list" cells are formatted with the...

SendObject behavior different after today's Windows XP updates
A client who runs XP and has been using Access2003 successfully for several years installed 3 WIndows updates today and, afterwords, code I wrote which emails orders to a distributor stopped working. In fact, any use of SendObject seems to be broken. The particular thing that happens is that it says the MS Office 2003 license expired in 2006 and he needs to either enter a product key or pay to upgrade the product. We are both assuming it means ACCESS. If he then clicks cancel on the screen wanting him to upgrade , he gets the text of the email that access generates on his s...

Select reciptients default folder.
In Outlook express 6 if I click "Create Mail" I get the New message box if I then click the "To:" tab it opens the Select Recipients box. I have a number of folder in my address book but the select recipients box always defaults to my contacts. Can I set it so that it always defaults to my folder called Work as this is the one I use the most. Thanks. Steve <swise@autogas.co.uk> wrote: > In Outlook express 6 if I click "Create Mail" I get the New message > box if I then click the "To:" tab it opens the Select Recipients box. > >...

Replace Option Group Value from form with text in report header
Hi all, Question: How can I convert the numerical option group value from a form to a string value and place it in the control source of a textbox in a report header? I have a form used to 'build' reports: users choose a query, a report, and then the rest of the controls on the form are used to build a 'where' clause for the DoCmd.OpenReports statement attached to a command button which opens the report based on all the user's choices. I've tried creating a variable on the report in the declarations area and then using a select case statement in the 'open&...

listbox highlight selection
Hi, after sorting information that I show in a listbox I would like to highlight the first item showing up at top of the listbox(right now I get by default a dotted line under the first item) Thank's ahead for any hint! My code: Dim NoDupes As New Collection Dim i As Integer, j As Integer Dim Swap1, Swap2, Item Sub slist() i = 1 Set NoDupes = New Collection For Each ws In Worksheets NoDupes.Add (Worksheets(i).Name) i = i + 1 Next ws ' Sort the collection For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) > NoDupes(j) Then ...

Counting Text Cells #2
Hi Guys, How can I determine the most common occurence of a word in a lis formatted as text in Column A. I would like a function that simpl returns the word that occurs the most to appear in cell D1. Also, i there are two words that occur the same number of times in this list, would like both words to be shown (one in D1, one in D2). Any suggestions? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi to get the most frequent text entry try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$...

How do I display a text message when a cell has a certain value??
Hi, I have a cell where if the cell is empty (A1=0), then I would like the cell to display "Enter your value here." Otherwise, I would like the cell to display the value a person enters. I have tried using an if/then/else statement and, understandably, I get a circular reference error: =IF(A1=0,"Enter your value here",A1) Does anyone have a suggestion? Thanks! Why not just enter the text itself? The value entered by the user will displace (replace) it. This should work if the form is a template. -- HTH, RD ===================================================...

Adding text to a vlookup cell
Can a cell be edited if the contents are the result of a vlookup function and still keep the rseults? I have 2003 on an XP Pro PC. My formula is =IF(A4<>"",VLOOKUP(A4,InvDetail!$A$2:$B$3000,2,FALSE),"") I trying to create an availability list for our customers but need to add comments to the end of some items. I know I can add another column but the customers are used to the comment being after the item description and space on the page is limited. Thanks, Lee Coleman If you're talking about entering comments manually, then you'd need to concatenate th...

Return Text based upon text matching
I have two separate worksheets Worksheet A COLUMNA COLUMNB Insurance Company Name Maketer NAME Aetna John Smith BCBC Mary Doolittle Abbington Mike Doe Worksheet B COLUMNA COLUMNH Insurance Company Name Marketer Name Aetna BCBC Abbington I need the Marketer Name to show up in Colu...

Two identical formulas for a different result?
Hello everyone, :) A very quick question, but nonetheless very intriguing to me (I jus lost 4h on that :( ) I don't understand why my first formula works, and the second and thir don't. {=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amount,0),0))} =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*Amount) (result is #value) =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount) (this one could work too I thought, but result in a 0) I am simply trying to sum (amount) when the fiscal year is 2003 an IncomeFeeId is RB......

Mailbox in a different AD forest
Hi all We've just created a second Active Directory forest "domain3" (win2k3) on our network and setup a 2-way trust with our exisiting domain "domain2.domain1" which contains an Exchange 2003 server. Can I setup mailboxes for the domain3 users in the domain2.domain1 Exchange server? Ideally I do not want to create disabled accounts in domain2.domain1and then associate their mailboxes with accounts in domain3, or deploy an Exchange server in domain3. I just want to host the domain3/user mailbox on the domain2.domain1 Exch2k3 server. So far I haven't read an...

How do i select different cells to be use in a formula.
Hi I need to know how to select different cells to be used in a formula, but i don't know what is the separator that i need to use to this, see the example bellow Cells that i need to include on my formula: A1 C5 BH32 Thanks. Hi you didn't mention what formula but here's a starting point: =AVERAGE(A1,C5,BH32) or depending on your regional settings =AVERAGE(A1;C5;BH32) hope this helps Cheers JulieD "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:A855A562-725C-4B10-B111-A88F7A767122@microsoft.com... > Hi > > I need to know ho...

Product key for different language
Just ordered a second copy of Office 2004 for Mac and with the shipment confirmation of the shop I have noticed that I have ordered the wrong language version and not the English one. As I already have a copy of the English Office 2004 I was wondering if it is possible to use the product key from the new non-english version for an installation from the english cd. In other words: can I use (technically and legally) the license which I have bought now to install a second copy of the english version or do I have to return the non-english version to swap it for the english one? I would ra...

Excel VBA problem
I have 2 Excel tables, part of the first table looks as follows. I kee track of parts in production by entering completion dates for eac process they go through. S/N Kitting SMT 1st Assembly Inspection 10001 30-Jun 2-Jul 8-Jul 10002 24-Jun 27-Jun 10003 Note: This table needs to be flexible in terms of the number of S/N' as well the number of processes entered. For example, there could b 15 processes parts need to go through. In addition, there could be 1 identical parts in production. I need help writing a macro which will go through each line and take the latest comple...

Disable Copy & Paste files but not text for Windows Server 2008 R2
I have a windows server 2008 r2 and am running remote desktop services. I want to disable cut/paste of files via clipboard/clipbook, while at the same time enabling cut/paste of text via clipboard/clipbook. This is working in earlier version of Windows. Any suggestion is much appreciated. -- Hannkwang ------------------------------------------------------------------------ Hannkwang's Profile: http://forums.techarena.in/members/199146.htm View this thread: http://forums.techarena.in/windows-server-help/1319418.htm http://forums.techarena.in ...

Is it possible to have flashing text in Excel 2003?
I was wondering if it was possible to have flashing text in a cell that contains a formula. And if so is it complicated to do? see: http://www.cpearson.com/excel/BlinkingText.aspx -- Gary''s Student - gsnu200748 "Scott" wrote: > I was wondering if it was possible to have flashing text in a cell that > contains a formula. And if so is it complicated to do? Thankyou Gary's Student that's just what I needed. "Gary''s Student" wrote: > see: > > http://www.cpearson.com/excel/BlinkingText.aspx > > > -- > Gary'...

Re: Using Text Styles
Front page will not have a Next Edition. AFAIK it is dead. > Ed Bennett wrote: > > Ron Hagley wrote: > >> I have a document with Headings on one line followed by a linespace > >> then on a new line a subheading followed by (on the same line ) a > >> description > >> > >> I wish to use TEXT STYLES as this pattern repeats many times, I have > >> no problem seting up the styles for the headings, but when I do the > >> same for the SUBHEADINGS the text style is applied to the WHOLE line > >> including the description...

Extract different strings from a cell?
If I have "100BDDDABAABD" in a cell and I'd like to extract the last 10 characters and put each character into a different cells. Then, the first rest of characters (first 3 in this case) into another cell. How do I do that in Excel? Thanks, Since you said "first 3 in this case", I'll assume that the number of characters will vary in each cell. With data in A1, enter this in B1, and copy across to K1: =MID(RIGHT($A1,10),COLUMNS($A:A),1) Then, in L1, enter this for the remaining characters: =LEFT(A1,LEN(A1)-10) -- HTH, RD -----------------------------------...

Curved Text
I want to use either Publisher or Word to create curved text. The first part of my text is to curve downwards, as if it was sitting on a invisible circle. The second part of the text is to curve upwards, as if it was also sitting on a invisible circle. I looked in the index at the back of the large 1,583- page "Inside Out" manual and cannot locate the instructions. Did the programmers forget to include this function when they designed the program? In Publisher 2000 you would use Word Art (the Big red W blue A on the left side of the screen). After creating your text cha...