VBA Problem

[Lochnagar]

Hi,

I have another VBA problem that I was hoping someone might be able to help out with. Basically, I need to stop the macros that I've written mid-way and prompt the user to select the next cell that the macros is to continue from. I appreciate that I could just use Activecell.offset(...), but the position of the cell that I want it to offset to is not fixed i.e. its user specified. I've tried using a Msgbox, which does stop the macros, but won't let me click on the spreadsheet.

Any help/suggestions would be very much appreciated

Thanks in advance,

Lochnagar

 

[jim may]

Sub Test()
Dim MyRange As Range
Set MyRange = Application.InputBox("Select a range.", "Range Input", Type:=8)
MyRange.Select
End Sub

 

[VoG]

Similar to Jim's but with an error check (in case user clicks Cancel for example).

Sub SelCell()
Dim r As Range
On Error Resume Next
Set r = Application.InputBox("Click in the next cell", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
Application.Goto reference:=r
End Sub

 

[Lochnagar]

Awesome, thanks VoGII and jim may, that was bang on what I was after. Brilliant thanks again.

Lochnagar.