MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Input Box for Cell References - Someone Must Know!!!

Posted by Chris on September 25, 2001 2:49 AM

I am trying to use an input box in order that the user can select which cell in the worksheet that the result from a macro can be put into.

At the moment I am using just a standard input box and the user has to type the cell reference (eg: a1). This works fine.

However, there must be an alternative way of doing this, where the user can just highlight the cell to put the data in, such as when you change the data range in a chart and that other type of dialog box appears. Unfortunately I do not know the name of that dialog box!

The macro uses highlighted cells throughout the sub and I cannot therefore select the end cell before I start the sub routine - it has to be at the end.

Can anyone help!?

Posted by Juan Pablo on September 25, 2001 8:31 AM

Try this

Sub InputRange()

Dim varRange As Variant

On Error Resume Next

Set varRange = _
Application.InputBox("Select a range of cells:", Type:=8)

If IsObject(varRange) = False Then Exit Sub

MsgBox varRange.Rows.Count

End Sub

Juan Pablo


Posted by Chris on September 26, 2001 12:52 AM

Works great - thanks.