Inputbox to pull cell location

[stvgarner]

Any idea what I am doing wrong here:

Sub TestRangeValue()

Dim dValue As Range

Set dValue = Application.InputBox(prompt:="Enter Cell Reference to Get Color Value For", Type:=8)

ActiveCell.Value = "=TSColor(" & dValue & ")"


End Sub

TSColor is a function I created to pull the background color in number format so it could be sorted by color. I am trying to get this to type the formula for them (kind of a teaching thing for new formulas) when they select the range to use. I am getting the value in the range I select and not the range itself. If I enter C7 into cell C7 this works to put the formula in cell C8 but if they select C7 and it is blank it returns '=TSColor()'

Thanks for any help.

[Chip Pearson]

Try

Dim dValue As Range
On Error Resume Next
Set dValue = Application.InputBox( _
prompt:="Enter Cell Reference to GetColor Value For",
Type:=8)
On Error GoTo 0
If Not dValue Is Nothing Then
ActiveCell.Value = "=TSColor(" & dValue.Address & ")"
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"stvgarner"
<[email protected]> wrote in
message
news:[email protected]...
>
> Any idea what I am doing wrong here:
>
> Sub TestRangeValue()
>
> Dim dValue As Range
>
> Set dValue = Application.InputBox(prompt:="Enter Cell Reference
> to Get
> Color Value For", Type:=8)
>
> ActiveCell.Value = "=TSColor(" & dValue & ")"
>
>
> End Sub
>
> TSColor is a function I created to pull the background color in
> number
> format so it could be sorted by color. I am trying to get this
> to type
> the formula for them (kind of a teaching thing for new
> formulas) when
> they select the range to use. I am getting the value in the
> range I
> select and not the range itself. If I enter C7 into cell C7
> this works
> to put the formula in cell C8 but if they select C7 and it is
> blank it
> returns '=TSColor()'
>
> Thanks for any help.
>
>
> --
> stvgarner
> ------------------------------------------------------------------------
> stvgarner's Profile:
> http://www.excelforum.com/member.php...o&userid=20599
> View this thread:
> http://www.excelforum.com/showthread...hreadid=533460
>

[stvgarner]

That did it, Thanks!!!

Do you know if there is a way to make the range not be absolute (C7 instead of $C$7)? Not to worried about this part though.

[Dave Peterson]

ActiveCell.Value = "=TSColor(" & dValue.Address & ")"
becomes
ActiveCell.Value = "=TSColor(" & dValue.Address(0,0) & ")"

stvgarner wrote:
>
> That did it, Thanks!!!
>
> Do you know if there is a way to make the range not be absolute (C7
> instead of $C$7)? Not to worried about this part though.
>
> --
> stvgarner
> ------------------------------------------------------------------------
> stvgarner's Profile: http://www.excelforum.com/member.php...o&userid=20599
> View this thread: http://www.excelforum.com/showthread...hreadid=533460

--

Dave Peterson

[stvgarner]

Well that looks easy enough.



Thanks!!