Selecting a cell during a macro
I want the user to be able to use the mouse to click on the position for the
rest of the macro to run.
A form asks the user to select the correct position. At present I have this
done by getting the row number typed in to the form (or the number of rows up
or down to move). This is rather awkward and old-fashioned and I would like
the form just to tell the user to click on the required position.

Sue,

Not sure if this is the answer your looking for but,

Load your user form as vbModeless; so Userform.show vbModeless, then you can
click anywhere on the sheet you want to. Then make sure that your macro runs
on the activecell.

Cheers,

Paul

"Sue" wrote:

Selecting a cell during a macro
I want the user to be able to use the mouse to click on the position for the
rest of the macro to run.
A form asks the user to select the correct position. At present I have this
done by getting the row number typed in to the form (or the number of rows up
or down to move). This is rather awkward and old-fashioned and I would like
the form just to tell the user to click on the required position.

On Feb 21, 10:13*am, Paul wrote:
Sue,

Not sure if this is the answer your looking for but,

Load your user form as vbModeless; so Userform.show vbModeless, then you can
click anywhere on the sheet you want to. Then make sure that your macro runs
on the activecell.

Cheers,

Paul



"Sue" wrote:
Selecting a cell during a macro
I want the user to be able to use the mouse to click on the position for the
rest of the macro to run.
A form asks the user to select the correct position. At present I have this
done by getting the row number typed in to the form (or the number of rows up
or down to move). This is rather awkward and old-fashioned and I would like
the form just to tell the user to click on the required position.- Hide quoted text -

- Show quoted text -

Hi
Two ways:

1. Put a RefEdit control on your userform. This is available from the
Toolbox when creating a Userform. It is the one which allows the user
to select a Range with the mouse.
Then
Set myRange = Userform1.myRefEditControl.Value
If not myRange is nothing then
'code using myRange
End if
2. If you don't have a userform use the Application inputbox

Set myRange = Application.Inputbox("Please Select a Range with the
Mouse", Type:=8)
If not myRange is nothing then
'code using myRange
End if

This will display the inputbox with an OK and Cancel Button. If Cancel
is clicked then myRange is Nothing. The Type 8 setting is for a Range
as input.

regards
Paul

the application.inputbox does exactly what you need...


Sub text_this()

Dim rChosen As Range

Set rChosen = Application.InputBox("Select a cell or range", Type:=8)

rChosen.Interior.Color = vbBlue

End Sub

"Sue" wrote:

Selecting a cell during a macro
I want the user to be able to use the mouse to click on the position for the
rest of the macro to run.
A form asks the user to select the correct position. At present I have this
done by getting the row number typed in to the form (or the number of rows up
or down to move). This is rather awkward and old-fashioned and I would like
the form just to tell the user to click on the required position.