prompt user to select a cell for use ina formula

[Guest]

I would like to creat a macro to do the following:

1) Move the focus to a specific area on my spreadsheet. (A listing of paper
types and weights, with multiple weights per paper type.)

2) Then prompt the user to select the appropriate cell, by actually
selecting that cell not just entering the cell reference, and then to press
the "enter" key.

3) Upon pressing enter to move back to the location of the command button
that activated the macro.

4) Place the cell that was selected by the user into a specific cell. (This
cell is then used in a formula.)

Any and all help will be greatly appriciated.

 

[Debra Dalgleish]

You can use the InputBox method to get a range. For example:

'====================
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets("Data")
ws.Activate
ws.Range("A1").Activate
Set rng = Application.InputBox("Select a cell", Type:=8)
With Sheets("Menu")
.Activate
.Range("D10").Formula = _
"=" & ws.Name & "!" & rng.Address & "*0.5"
End With
End Sub
'===============================