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 t
click to enter the order number. Is there a way to create a macro t
prompt the person for the order number and then when they press ente
or whatever that number is enter into the appropriate area? Or, i
there a way to split a cell?

Description

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

 

[Guest]

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]

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