Hi All
I want to use an InputBox to ask the user for a cell location.
Can someone provide me with some code to do this please.
This is what I have so far.
Basically the last line of the example is where it breaks !!

Any input is appreciated
Michael

Dim x As String, z As Range
Application.ScreenUpdating = False
userEntry = InputBox("Enter Preferred Cell Location")
EndRow = Range("E65536").End(xlUp).Row
Set z = Worksheets("Sheet1").Range("userEntry")

Option Explicit
sub testme()
dim Rng as range
dim xStr as string
dim nextCell as range

set rng = nothing
on error resume next
set rng = application.inputbox(Prompt:="Click a cell",type:=8)
on error goto 0

if rng is nothing then
'user hit cancel
exit sub '???
end if

with worksheets("someworksheetnamehere")
set nextcell = .cells(.rows.count,"E").end(xlup).offset(1,0)
end with

rng.copy _
destination:=nextcell

end sub

I wasn't sure what you wanted to do, though. I'm not sure I got it right.

Michael M wrote:

Hi All
I want to use an InputBox to ask the user for a cell location.
Can someone provide me with some code to do this please.
This is what I have so far.
Basically the last line of the example is where it breaks !!

Any input is appreciated
Michael

Dim x As String, z As Range
Application.ScreenUpdating = False
userEntry = InputBox("Enter Preferred Cell Location")
EndRow = Range("E65536").End(xlUp).Row
Set z = Worksheets("Sheet1").Range("userEntry")

--

Dave Peterson

Thanks Dave
Firstly, the code I am using is from a VBA book by Bill Jelens and then
modified by myself. Unfortunately the more I modify, the more I want !!
Basically I want the user to be able to select the cell location they want
the chart to appear in,....anywhere on the sheet !!
As you can see it currently defaults to "G16"

Regards
Michael

Sub PlaceGraph()
Dim x As String, z As Range
Application.ScreenUpdating = False
EndRow = Range("E65536").End(xlUp).Row
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:E" & EndRow
& ""), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
x = "G:\Temp\graph.gif"
Set z = Worksheets("Sheet1").Range("G16")
On Error Resume Next
z.Comment.Delete
On Error GoTo 0
Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.Export x
With z.AddComment
With .Shape
.Height = 322
.Width = 465
.Fill.UserPicture x
End With
End With
Worksheets("Sheet1").ChartObjects(1).Delete
Application.ScreenUpdating = True
End Sub

This works:

Sub GetRange()
Dim myWS As Worksheet
Dim x As String, z As Range, userentry As Range
Dim endrow As Long
Set myWS = ThisWorkbook.Worksheets("sheet1")
Application.ScreenUpdating = False
Set userentry = Application.InputBox(prompt:="Enter Cell location",
Type:=8)
endrow = Range("E" & Rows.Count).End(xlUp).Row

Set z = myWS.Range(userentry.Address)
MsgBox z.Address
Application.ScreenUpdating = True
End Sub

However, I was expecting the inputbox to act a little differently (with the
little icon you can click to let you directly select the cells from the
worksheet.) But it did not do this. I thought that was what type:=8 was
doing. But looking at help, I guess it just ensures that it is a valid cell
reference.
--
Kevin Vaughn


"Michael M" wrote:

Hi All
I want to use an InputBox to ask the user for a cell location.
Can someone provide me with some code to do this please.
This is what I have so far.
Basically the last line of the example is where it breaks !!

Any input is appreciated
Michael

Dim x As String, z As Range
Application.ScreenUpdating = False
userEntry = InputBox("Enter Preferred Cell Location")
EndRow = Range("E65536").End(xlUp).Row
Set z = Worksheets("Sheet1").Range("userEntry")

Thank you both for your input
I managed to modify Daves code to meet my needs perfectly.
Sorry, I'm a bit slow with VBA and it took me a while to realise the answer
was there.

Regards
Michael

"Kevin Vaughn" wrote:

This works:

Sub GetRange()
Dim myWS As Worksheet
Dim x As String, z As Range, userentry As Range
Dim endrow As Long
Set myWS = ThisWorkbook.Worksheets("sheet1")
Application.ScreenUpdating = False
Set userentry = Application.InputBox(prompt:="Enter Cell location",
Type:=8)
endrow = Range("E" & Rows.Count).End(xlUp).Row

Set z = myWS.Range(userentry.Address)
MsgBox z.Address
Application.ScreenUpdating = True
End Sub

However, I was expecting the inputbox to act a little differently (with the
little icon you can click to let you directly select the cells from the
worksheet.) But it did not do this. I thought that was what type:=8 was
doing. But looking at help, I guess it just ensures that it is a valid cell
reference.
--
Kevin Vaughn


"Michael M" wrote:

Hi All
I want to use an InputBox to ask the user for a cell location.
Can someone provide me with some code to do this please.
This is what I have so far.
Basically the last line of the example is where it breaks !!

Any input is appreciated
Michael

Dim x As String, z As Range
Application.ScreenUpdating = False
userEntry = InputBox("Enter Preferred Cell Location")
EndRow = Range("E65536").End(xlUp).Row
Set z = Worksheets("Sheet1").Range("userEntry")

I finally figured out why my inputbox was not working the way I thought it
should. The application.screenupdating = false line prevented me from being
able to select cells from the worksheet.

--
Kevin Vaughn


"Kevin Vaughn" wrote:

This works:

Sub GetRange()
Dim myWS As Worksheet
Dim x As String, z As Range, userentry As Range
Dim endrow As Long
Set myWS = ThisWorkbook.Worksheets("sheet1")
Application.ScreenUpdating = False
Set userentry = Application.InputBox(prompt:="Enter Cell location",
Type:=8)
endrow = Range("E" & Rows.Count).End(xlUp).Row

Set z = myWS.Range(userentry.Address)
MsgBox z.Address
Application.ScreenUpdating = True
End Sub

However, I was expecting the inputbox to act a little differently (with the
little icon you can click to let you directly select the cells from the
worksheet.) But it did not do this. I thought that was what type:=8 was
doing. But looking at help, I guess it just ensures that it is a valid cell
reference.
--
Kevin Vaughn


"Michael M" wrote:

Hi All
I want to use an InputBox to ask the user for a cell location.
Can someone provide me with some code to do this please.
This is what I have so far.
Basically the last line of the example is where it breaks !!

Any input is appreciated
Michael

Dim x As String, z As Range
Application.ScreenUpdating = False
userEntry = InputBox("Enter Preferred Cell Location")
EndRow = Range("E65536").End(xlUp).Row
Set z = Worksheets("Sheet1").Range("userEntry")