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 Ge
Color Value For", Type:=8)

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


End Sub

TSColor is a function I created to pull the background color in numbe
format so it could be sorted by color. I am trying to get this to typ
the formula for them (kind of a teaching thing for new formulas) whe
they select the range to use. I am getting the value in the range
select and not the range itself. If I enter C7 into cell C7 this work
to put the formula in cell C8 but if they select C7 and it is blank i
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"
message
news:[email protected]...

 

[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]

Well that looks easy enough.



Thanks!!