Using input box to improve my method

Hi, about a month ago (with some help) i came up with the following method for saving required filepaths to my worksheet. This project has since grown and i now have many filepaths required. For this reason i would like to change the below section in red to incorporate a input box that allows user to select a cell to update, meaning i can use same code only once.

I thought i could use

dim rng as range
set rng = Application.InputBox("Click Cell You wish to change")

Unfortunately it seems that the rng is not being set when the user clicks ok on inputbox. WHere am i going wrong? Thanks

--Original code----
Dim pathChange As Range
Dim pathandfilename As Variant

Public Sub NQAJ_change()
Range("C15").Select
Set pathChange = ActiveCellGetPath
End Sub

Sub GetPath()
pathandfilename = Application.GetOpenFilename

If pathandfilename = False Then
MsgBox "User quits the dialog"
Else
' MsgBox "The path is: " & pathandfilename
pathChange = pathandfilename
End If

End Sub

Set rng = Application.InputBox("Click Cell You wish to change", Type:=8)

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> Hi, about a month ago (with some help) i came up with the following
> method for saving required filepaths to my worksheet. This project has
> since grown and i now have many filepaths required. For this reason i
> would like to change the below section in red to incorporate a input
> box that allows user to select a cell to update, meaning i can use same
> code only once.
>
> I thought i could use
>
> dim rng as range
> set rng = Application.InputBox("Click Cell You wish to change")
>
> Unfortunately it seems that the rng is not being set when the user
> clicks ok on inputbox. WHere am i going wrong? Thanks
>
> --Original code----
> Dim pathChange As Range
> Dim pathandfilename As Variant
>
> Public Sub NQAJ_change()
> Range("C15").Select
> Set pathChange = ActiveCellGetPath
> End Sub
>
> Sub GetPath()
> pathandfilename = Application.GetOpenFilename
>
> If pathandfilename = False Then
> MsgBox "User quits the dialog"
> Else
> ' MsgBox "The path is: " & pathandfilename
> pathChange = pathandfilename
> End If
>
> End Sub
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=545428
>

Hi Cereldine,

You should also check if user selected valid range or didn't hit cancel
button:

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Click Cell You wish to change",
Type:=8)
If Err <> 0 Then
MsgBox "Not valid range or cancel"
End If

Regards,
Ivan