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


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=545428

Using input box to improve my method
 

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" wrote
in message ...

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

Using input box to improve my method
 

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