Macro Inside Cell
 

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

Macro Inside Cell
 

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

Macro Inside Cell
 

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


--
dah
------------------------------------------------------------------------
dah's Profile: http://www.excelforum.com/member.php...fo&userid=6493
View this thread: http://www.excelforum.com/showthread...hreadid=504047

Macro Inside Cell
 

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
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