Interactive selection of worksheet and range

[krishnak]

Hi All,

I am working on a script which compares the cell values of two ranges in different workbooks. I could work out the code with the actual workbooks, worksheets and range names manually written in the code before running the script. Now I want to generalize this script to select the workbooks, worksheets and the ranges while running the script. I could select the workbooks by the GetOpenFileName function. Once the workbook is open, I want to scroll to the worksheet, range and then import the information (like "Sheet2" and "A4 D65") into the script just by selecting them. The program has to wait until I select the parameters and then start running again by capturing the selected information.
Please advise how I can do it.
Thanks in advance

[lucas]

[VBA]Sub Test()

Dim Selectedarea As Range
Dim MyErr As Long

On Error Resume Next
Set Selectedarea = Application.InputBox(prompt:="Select the cell/range...", _
Title:="SWITCH CELL SELECTION", Default:=Selection.Address, Type:=8)
MyErr = Err
On Error GoTo 0
If MyErr <> 0 Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Sub
End If

MsgBox Selectedarea.Cells.Count 'Change to do what you want if Cells.Count = 1

End Sub[/VBA]

[krishnak]

Hi Lucas,

Thanks for the advice. That worked very well. I had to modify slightly to get the worksheet name into the code. Since I am not proficient in VBA, will you please explain me what is the purpose of the following: ( I am sure that there is a good reason)

Dim MyErr As Long
MyErr = Err
On Error GoTo 0
If MyErr <> 0 Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Function
End If

[lucas]

run the sub I gave you and hit cancel instead of selecting a range....
simple error handler with msgbox for cance.....use or discard as you wish.