Macro Inside Cell

[dah]

Hi:

I have a column titled "Description"

In one row of this column I have a entry for "Order No:"

Right now when a person tabs to this field with Order No. they have to click to enter the order number. Is there a way to create a macro to prompt the person for the order number and then when they press enter or whatever that number is enter into the appropriate area? Or, is there a way to split a cell?

Description

Order No: I have to click here to enter a number

[Nick B]

You can use a macro that runs when you click on a cell (or tab into the
cell). The macro can prompt for a value and update any cell.

The code below will fire an input box when you click on cell B2. It will
then store the value in cell B2. Is something like this what you are looking
for?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then
Target.Value = InputBox("Please enter an Order Number", "Order
Number")
End If
End Sub

"dah" wrote:

>
> Hi:
>
> I have a column titled "Description"
>
> In one row of this column I have a entry for "Order No:"
>
> Right now when a person tabs to this field with Order No. they have to
> click to enter the order number. Is there a way to create a macro to
> prompt the person for the order number and then when they press enter
> or whatever that number is enter into the appropriate area? Or, is
> there a way to split a cell?
>
> Description
>
> Order No: I have to click here to enter a number
>
>
> --
> dah
> ------------------------------------------------------------------------
> dah's Profile: http://www.excelforum.com/member.php...fo&userid=6493
> View this thread: http://www.excelforum.com/showthread...hreadid=504047
>
>

[dah]

This appears to be what would work but I get an error message (compile) that Sub Cellchange() is wrong.


Sub CellChange()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$25" Then
Target.Value = InputBox("Please enter an Order Number", "Order Number")
End If

End Sub

[Gord Dibben]

Drop the Sub CellChange()

Drop the last double quote. Good practice also to stick in an error trap to
re-enable events if an error occurs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$25" Then
On Error GoTo CleanUp
Application.EnableEvents = False
Target.Value = InputBox("Please enter an Order Number", "Order Number ")
End If
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 23 Jan 2006 13:40:21 -0600, dah
<[email protected]> wrote:

>
>This appears to be what would work but I get an error message (compile)
>that Sub Cellchange() is wrong.
>
>
>Sub CellChange()
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>If Target.Address = "$B$25" Then
>Target.Value = InputBox("Please enter an Order Number", "Order
>Number")
>End If
>
>End Sub