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.

On Sun, 10 Jan 2010 10:28:54 -0600, "John" 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

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" wrote in message
...
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.

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" wrote in message
...
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.