PDA

View Full Version : Interactive selection of worksheet and range



krishnak
05-08-2007, 01:31 PM
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
05-08-2007, 02:11 PM
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

krishnak
05-08-2007, 05:52 PM
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
05-08-2007, 06:10 PM
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.