Not changing from "City" to "1-City"

Hello from Steved

Below is not working I've got "City" in Col B is this my issue, if not
what is wrong please. example change "City" to "1-City"

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
Select Case LCase(Left(.Value, 4))
Case "City": .Value = "1-City"
Case "Rosk": .Value = "2-Rosk"
Case "Papa": .Value = "3-Papa"
Case "Wiri": .Value = "4-Wiri"
Case "Shor": .Value = "5-Shor"
Case "Orew": .Value = "6-Orew"
Case "Swan": .Value = "7-Swan"
Case "Panm": .Value = "8-Panm"
Case "Waih": .Value = "9-Waih"
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

Thankyou.

Hi Steved,

Try:

'==============>>
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
Select Case LCase(Left(.Value, 4))
Case "city": .Value = "1-City"
Case "rosk": .Value = "2-Rosk"
Case "papa": .Value = "3-Papa"
Case "wiri": .Value = "4-Wiri"
Case "shor": .Value = "5-Shor"
Case "orew": .Value = "6-Orew"
Case "swan": .Value = "7-Swan"
Case "panm": .Value = "8-Panm"
Case "waih": .Value = "9-Waih"
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'<<==============

Note that all the Case tests have been changed to lower case.


---
Regards,
Norman



"Steved" <Steved@discussions.microsoft.com> wrote in message
news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
> Hello from Steved
>
> Below is not working I've got "City" in Col B is this my issue, if not
> what is wrong please. example change "City" to "1-City"
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> On Error GoTo ws_exit
> With Target
> Select Case LCase(Left(.Value, 4))
> Case "City": .Value = "1-City"
> Case "Rosk": .Value = "2-Rosk"
> Case "Papa": .Value = "3-Papa"
> Case "Wiri": .Value = "4-Wiri"
> Case "Shor": .Value = "5-Shor"
> Case "Orew": .Value = "6-Orew"
> Case "Swan": .Value = "7-Swan"
> Case "Panm": .Value = "8-Panm"
> Case "Waih": .Value = "9-Waih"
> End Select
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Thankyou.
>
>

Hello Norman from Steved

Thankyou for your reponse

The change will only occur if I highlite say "city" it then will change to
"1-City".

Is there something I've not done.

Cheers.

"Norman Jones" wrote:

> Hi Steved,
>
> Try:
>
> '==============>>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> On Error GoTo ws_exit
> With Target
> Select Case LCase(Left(.Value, 4))
> Case "city": .Value = "1-City"
> Case "rosk": .Value = "2-Rosk"
> Case "papa": .Value = "3-Papa"
> Case "wiri": .Value = "4-Wiri"
> Case "shor": .Value = "5-Shor"
> Case "orew": .Value = "6-Orew"
> Case "swan": .Value = "7-Swan"
> Case "panm": .Value = "8-Panm"
> Case "waih": .Value = "9-Waih"
> End Select
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> '<<==============
>
> Note that all the Case tests have been changed to lower case.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Steved" <Steved@discussions.microsoft.com> wrote in message
> news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
> > Hello from Steved
> >
> > Below is not working I've got "City" in Col B is this my issue, if not
> > what is wrong please. example change "City" to "1-City"
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Application.EnableEvents = False
> > On Error GoTo ws_exit
> > With Target
> > Select Case LCase(Left(.Value, 4))
> > Case "City": .Value = "1-City"
> > Case "Rosk": .Value = "2-Rosk"
> > Case "Papa": .Value = "3-Papa"
> > Case "Wiri": .Value = "4-Wiri"
> > Case "Shor": .Value = "5-Shor"
> > Case "Orew": .Value = "6-Orew"
> > Case "Swan": .Value = "7-Swan"
> > Case "Panm": .Value = "8-Panm"
> > Case "Waih": .Value = "9-Waih"
> > End Select
> > End With
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > Thankyou.
> >
> >
>
>
>

The event you chose fires when you edit a cell. As written, the code works
only on that cell. How do you want it to operate?

How do you intend to trigger the macro.

Since you posted your code in the change event and your case statement was
screwed up, Norman showed you how to correct the code you wrote. Thus the
assumption is that you wanted to use the change event to manipulate the data
you just entered.

--
Regards,
Tom Ogilvy


"Steved" <Steved@discussions.microsoft.com> wrote in message
news:4A1CEC0D-07C1-41A2-B386-82E460690362@microsoft.com...
> Hello Norman from Steved
>
> Thankyou for your reponse
>
> The change will only occur if I highlite say "city" it then will change to
> "1-City".
>
> Is there something I've not done.
>
> Cheers.
>
> "Norman Jones" wrote:
>
> > Hi Steved,
> >
> > Try:
> >
> > '==============>>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Application.EnableEvents = False
> > On Error GoTo ws_exit
> > With Target
> > Select Case LCase(Left(.Value, 4))
> > Case "city": .Value = "1-City"
> > Case "rosk": .Value = "2-Rosk"
> > Case "papa": .Value = "3-Papa"
> > Case "wiri": .Value = "4-Wiri"
> > Case "shor": .Value = "5-Shor"
> > Case "orew": .Value = "6-Orew"
> > Case "swan": .Value = "7-Swan"
> > Case "panm": .Value = "8-Panm"
> > Case "waih": .Value = "9-Waih"
> > End Select
> > End With
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > '<<==============
> >
> > Note that all the Case tests have been changed to lower case.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Steved" <Steved@discussions.microsoft.com> wrote in message
> > news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
> > > Hello from Steved
> > >
> > > Below is not working I've got "City" in Col B is this my issue, if not
> > > what is wrong please. example change "City" to "1-City"
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > Application.EnableEvents = False
> > > On Error GoTo ws_exit
> > > With Target
> > > Select Case LCase(Left(.Value, 4))
> > > Case "City": .Value = "1-City"
> > > Case "Rosk": .Value = "2-Rosk"
> > > Case "Papa": .Value = "3-Papa"
> > > Case "Wiri": .Value = "4-Wiri"
> > > Case "Shor": .Value = "5-Shor"
> > > Case "Orew": .Value = "6-Orew"
> > > Case "Swan": .Value = "7-Swan"
> > > Case "Panm": .Value = "8-Panm"
> > > Case "Waih": .Value = "9-Waih"
> > > End Select
> > > End With
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > Thankyou.
> > >
> > >
> >
> >
> >

Hello Tom from Steved

I copy from another file, so when I paste it this is when I wuold like the
macro to trigger form "City" to "1-City".

I think I've missed something but for the life off me I do not know what it
is.

Thankyou.


"Tom Ogilvy" wrote:

> The event you chose fires when you edit a cell. As written, the code works
> only on that cell. How do you want it to operate?
>
> How do you intend to trigger the macro.
>
> Since you posted your code in the change event and your case statement was
> screwed up, Norman showed you how to correct the code you wrote. Thus the
> assumption is that you wanted to use the change event to manipulate the data
> you just entered.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steved" <Steved@discussions.microsoft.com> wrote in message
> news:4A1CEC0D-07C1-41A2-B386-82E460690362@microsoft.com...
> > Hello Norman from Steved
> >
> > Thankyou for your reponse
> >
> > The change will only occur if I highlite say "city" it then will change to
> > "1-City".
> >
> > Is there something I've not done.
> >
> > Cheers.
> >
> > "Norman Jones" wrote:
> >
> > > Hi Steved,
> > >
> > > Try:
> > >
> > > '==============>>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > Application.EnableEvents = False
> > > On Error GoTo ws_exit
> > > With Target
> > > Select Case LCase(Left(.Value, 4))
> > > Case "city": .Value = "1-City"
> > > Case "rosk": .Value = "2-Rosk"
> > > Case "papa": .Value = "3-Papa"
> > > Case "wiri": .Value = "4-Wiri"
> > > Case "shor": .Value = "5-Shor"
> > > Case "orew": .Value = "6-Orew"
> > > Case "swan": .Value = "7-Swan"
> > > Case "panm": .Value = "8-Panm"
> > > Case "waih": .Value = "9-Waih"
> > > End Select
> > > End With
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > '<<==============
> > >
> > > Note that all the Case tests have been changed to lower case.
> > >
> > >
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > >
> > > "Steved" <Steved@discussions.microsoft.com> wrote in message
> > > news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
> > > > Hello from Steved
> > > >
> > > > Below is not working I've got "City" in Col B is this my issue, if not
> > > > what is wrong please. example change "City" to "1-City"
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > > Application.EnableEvents = False
> > > > On Error GoTo ws_exit
> > > > With Target
> > > > Select Case LCase(Left(.Value, 4))
> > > > Case "City": .Value = "1-City"
> > > > Case "Rosk": .Value = "2-Rosk"
> > > > Case "Papa": .Value = "3-Papa"
> > > > Case "Wiri": .Value = "4-Wiri"
> > > > Case "Shor": .Value = "5-Shor"
> > > > Case "Orew": .Value = "6-Orew"
> > > > Case "Swan": .Value = "7-Swan"
> > > > Case "Panm": .Value = "8-Panm"
> > > > Case "Waih": .Value = "9-Waih"
> > > > End Select
> > > > End With
> > > >
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > Thankyou.
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Hi Steved,

> I copy from another file, so when I paste it this is when I wuold
> like the macro to trigger form "City" to "1-City".

With the modification to the Case statement, the code does this.

However, to allow for a multiple cell paste, try:

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

Application.EnableEvents = False
On Error GoTo ws_exit

For Each rCell In Target.Cells
With rCell
Select Case LCase(Left(.Value, 4))
Case "city": .Value = "1-City"
Case "rosk": .Value = "2-Rosk"
Case "papa": .Value = "3-Papa"
Case "wiri": .Value = "4-Wiri"
Case "shor": .Value = "5-Shor"
Case "orew": .Value = "6-Orew"
Case "swan": .Value = "7-Swan"
Case "panm": .Value = "8-Panm"
Case "waih": .Value = "9-Waih"
End Select
End With
Next rCell

ws_exit:
Application.EnableEvents = True
End Sub
'<<==============--


---
Regards,
Norman


"Steved" <Steved@discussions.microsoft.com> wrote in message
news:28D65BBD-4777-43B0-A7BE-AD1FA19A2637@microsoft.com...
> Hello Tom from Steved
>
> I copy from another file, so when I paste it this is when I wuold like the
> macro to trigger form "City" to "1-City".
>
> I think I've missed something but for the life off me I do not know what
> it
> is.
>
> Thankyou.
>
>
> "Tom Ogilvy" wrote:
>
>> The event you chose fires when you edit a cell. As written, the code
>> works
>> only on that cell. How do you want it to operate?
>>
>> How do you intend to trigger the macro.
>>
>> Since you posted your code in the change event and your case statement
>> was
>> screwed up, Norman showed you how to correct the code you wrote. Thus
>> the
>> assumption is that you wanted to use the change event to manipulate the
>> data
>> you just entered.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Steved" <Steved@discussions.microsoft.com> wrote in message
>> news:4A1CEC0D-07C1-41A2-B386-82E460690362@microsoft.com...
>> > Hello Norman from Steved
>> >
>> > Thankyou for your reponse
>> >
>> > The change will only occur if I highlite say "city" it then will change
>> > to
>> > "1-City".
>> >
>> > Is there something I've not done.
>> >
>> > Cheers.
>> >
>> > "Norman Jones" wrote:
>> >
>> > > Hi Steved,
>> > >
>> > > Try:
>> > >
>> > > '==============>>
>> > > Private Sub Worksheet_Change(ByVal Target As Range)
>> > >
>> > > Application.EnableEvents = False
>> > > On Error GoTo ws_exit
>> > > With Target
>> > > Select Case LCase(Left(.Value, 4))
>> > > Case "city": .Value = "1-City"
>> > > Case "rosk": .Value = "2-Rosk"
>> > > Case "papa": .Value = "3-Papa"
>> > > Case "wiri": .Value = "4-Wiri"
>> > > Case "shor": .Value = "5-Shor"
>> > > Case "orew": .Value = "6-Orew"
>> > > Case "swan": .Value = "7-Swan"
>> > > Case "panm": .Value = "8-Panm"
>> > > Case "waih": .Value = "9-Waih"
>> > > End Select
>> > > End With
>> > >
>> > > ws_exit:
>> > > Application.EnableEvents = True
>> > > End Sub
>> > >
>> > > '<<==============
>> > >
>> > > Note that all the Case tests have been changed to lower case.
>> > >
>> > >
>> > > ---
>> > > Regards,
>> > > Norman
>> > >
>> > >
>> > >
>> > > "Steved" <Steved@discussions.microsoft.com> wrote in message
>> > > news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
>> > > > Hello from Steved
>> > > >
>> > > > Below is not working I've got "City" in Col B is this my issue, if
>> > > > not
>> > > > what is wrong please. example change "City" to "1-City"
>> > > >
>> > > > Private Sub Worksheet_Change(ByVal Target As Range)
>> > > >
>> > > > Application.EnableEvents = False
>> > > > On Error GoTo ws_exit
>> > > > With Target
>> > > > Select Case LCase(Left(.Value, 4))
>> > > > Case "City": .Value = "1-City"
>> > > > Case "Rosk": .Value = "2-Rosk"
>> > > > Case "Papa": .Value = "3-Papa"
>> > > > Case "Wiri": .Value = "4-Wiri"
>> > > > Case "Shor": .Value = "5-Shor"
>> > > > Case "Orew": .Value = "6-Orew"
>> > > > Case "Swan": .Value = "7-Swan"
>> > > > Case "Panm": .Value = "8-Panm"
>> > > > Case "Waih": .Value = "9-Waih"
>> > > > End Select
>> > > > End With
>> > > >
>> > > > ws_exit:
>> > > > Application.EnableEvents = True
>> > > > End Sub
>> > > >
>> > > > Thankyou.
>> > > >
>> > > >
>> > >
>> > >
>> > >
>>
>>
>>

Hello from Steved

That did it.

Thankyou verymuch.

"Norman Jones" wrote:

> Hi Steved,
>
> > I copy from another file, so when I paste it this is when I wuold
> > like the macro to trigger form "City" to "1-City".
>
> With the modification to the Case statement, the code does this.
>
> However, to allow for a multiple cell paste, try:
>
> '==============>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rCell As Range
>
> Application.EnableEvents = False
> On Error GoTo ws_exit
>
> For Each rCell In Target.Cells
> With rCell
> Select Case LCase(Left(.Value, 4))
> Case "city": .Value = "1-City"
> Case "rosk": .Value = "2-Rosk"
> Case "papa": .Value = "3-Papa"
> Case "wiri": .Value = "4-Wiri"
> Case "shor": .Value = "5-Shor"
> Case "orew": .Value = "6-Orew"
> Case "swan": .Value = "7-Swan"
> Case "panm": .Value = "8-Panm"
> Case "waih": .Value = "9-Waih"
> End Select
> End With
> Next rCell
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
> '<<==============--
>
>
> ---
> Regards,
> Norman
>
>
> "Steved" <Steved@discussions.microsoft.com> wrote in message
> news:28D65BBD-4777-43B0-A7BE-AD1FA19A2637@microsoft.com...
> > Hello Tom from Steved
> >
> > I copy from another file, so when I paste it this is when I wuold like the
> > macro to trigger form "City" to "1-City".
> >
> > I think I've missed something but for the life off me I do not know what
> > it
> > is.
> >
> > Thankyou.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> The event you chose fires when you edit a cell. As written, the code
> >> works
> >> only on that cell. How do you want it to operate?
> >>
> >> How do you intend to trigger the macro.
> >>
> >> Since you posted your code in the change event and your case statement
> >> was
> >> screwed up, Norman showed you how to correct the code you wrote. Thus
> >> the
> >> assumption is that you wanted to use the change event to manipulate the
> >> data
> >> you just entered.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Steved" <Steved@discussions.microsoft.com> wrote in message
> >> news:4A1CEC0D-07C1-41A2-B386-82E460690362@microsoft.com...
> >> > Hello Norman from Steved
> >> >
> >> > Thankyou for your reponse
> >> >
> >> > The change will only occur if I highlite say "city" it then will change
> >> > to
> >> > "1-City".
> >> >
> >> > Is there something I've not done.
> >> >
> >> > Cheers.
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> > > Hi Steved,
> >> > >
> >> > > Try:
> >> > >
> >> > > '==============>>
> >> > > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > >
> >> > > Application.EnableEvents = False
> >> > > On Error GoTo ws_exit
> >> > > With Target
> >> > > Select Case LCase(Left(.Value, 4))
> >> > > Case "city": .Value = "1-City"
> >> > > Case "rosk": .Value = "2-Rosk"
> >> > > Case "papa": .Value = "3-Papa"
> >> > > Case "wiri": .Value = "4-Wiri"
> >> > > Case "shor": .Value = "5-Shor"
> >> > > Case "orew": .Value = "6-Orew"
> >> > > Case "swan": .Value = "7-Swan"
> >> > > Case "panm": .Value = "8-Panm"
> >> > > Case "waih": .Value = "9-Waih"
> >> > > End Select
> >> > > End With
> >> > >
> >> > > ws_exit:
> >> > > Application.EnableEvents = True
> >> > > End Sub
> >> > >
> >> > > '<<==============
> >> > >
> >> > > Note that all the Case tests have been changed to lower case.
> >> > >
> >> > >
> >> > > ---
> >> > > Regards,
> >> > > Norman
> >> > >
> >> > >
> >> > >
> >> > > "Steved" <Steved@discussions.microsoft.com> wrote in message
> >> > > news:B4115CE6-C5E0-4E51-B74C-AC34E95D39DE@microsoft.com...
> >> > > > Hello from Steved
> >> > > >
> >> > > > Below is not working I've got "City" in Col B is this my issue, if
> >> > > > not
> >> > > > what is wrong please. example change "City" to "1-City"
> >> > > >
> >> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > > >
> >> > > > Application.EnableEvents = False
> >> > > > On Error GoTo ws_exit
> >> > > > With Target
> >> > > > Select Case LCase(Left(.Value, 4))
> >> > > > Case "City": .Value = "1-City"
> >> > > > Case "Rosk": .Value = "2-Rosk"
> >> > > > Case "Papa": .Value = "3-Papa"
> >> > > > Case "Wiri": .Value = "4-Wiri"
> >> > > > Case "Shor": .Value = "5-Shor"
> >> > > > Case "Orew": .Value = "6-Orew"
> >> > > > Case "Swan": .Value = "7-Swan"
> >> > > > Case "Panm": .Value = "8-Panm"
> >> > > > Case "Waih": .Value = "9-Waih"
> >> > > > End Select
> >> > > > End With
> >> > > >
> >> > > > ws_exit:
> >> > > > Application.EnableEvents = True
> >> > > > End Sub
> >> > > >
> >> > > > Thankyou.
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>