Combining Text

[jdmx]

What I want to do:

When I tab or enter out of a cell I wanted the inputed data to be combined with a predefined variable.

Example:

If I type in 123456 into a cell and hit enter or tab to put it into the cell, I want the cell to read "MYTEXT123456"

I've honestly tried to the best of my skills to do this and I can't get it. Ha, I guess my skills aren't very great.

Thanks, guys

[Ron Coderre]

Here's a little VBA routine that you can adapt to suit your needs:

Note: In my test, I trigger a prefix for anything entered into a cell in column G.

TIP: Whenever working with VBA code....save a backup, first.

1)Open the Visual Basic Editor [Alt]+[F11]

2)Under Microsoft Excel Objects:
Select the sheet you want impacted by the code.

3)Right Click on that sheet name and select View Code.

4)Copy/Paste this code into that module:

[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(ActiveCell, [g:g]) Is Nothing) Then

If ActiveCell.Value <> "" Then
Application.EnableEvents = False
ActiveCell.Value = "MyPrefix" & ActiveCell.Value
Application.EnableEvents = True
End If

End If

End Sub
[/vba]

5)Change the reference from g:g to whatever your situation demands. If it's just one cell (say: A5), change the reference to [A5]. And, change the prefix text to whatever you prefer.

Done....now, go enter something in the worksheet.

Does that help?

Ron

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = "MYTEXT" & .Value
End With
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

"jdmx" <[email protected]> wrote in message
news:[email protected]...
>
> What I want to do:
>
> When I tab or enter out of a cell I wanted the inputed data to be
> combined with a predefined variable.
>
> Example:
>
> If I type in 123456 into a cell and hit enter or tab to put it into the
> cell, I want the cell to read "MYTEXT123456"
>
> I've honestly tried to the best of my skills to do this and I can't get
> it. Ha, I guess my skills aren't very great.
>
> Thanks, guys
>
>
> --
> jdmx
> ------------------------------------------------------------------------
> jdmx's Profile:
http://www.excelforum.com/member.php...o&userid=24018
> View this thread: http://www.excelforum.com/showthread...hreadid=376357
>

[jdmx]

Does that help?

Man, Can you think of anything of why it wouldn't work? You did code it so I should be able to select a cell, type in it, enter out, and it change the cell? It's not working.

Thank you for the help

[jdmx]

Bob, yours worked. That's really great. Thanks
Ron, I really appreciate your quick reply and help.

Thanks, guys