VBA Dialog box to select range in different workbook

Question

I want to allow a user to select a range that is likely to be in a different workbook.

I have attempted to do this with inputbox("",type:=8) which works to select data in the workbook but refuses to allow me to select a range in a different workbook.

Hence I would like a dialog box that allows me to perform this task.

Solution

Since I was free, I created an example for you

Create a Userform and place a ComboBox, A RefEdit Control and a Label

Next paste this code in the Userform

Private Sub UserForm_Initialize()
    Dim wb As Workbook

    '~~> Get the name of all the workbooks in the combobox
    For Each wb In Application.Workbooks
        ComboBox1.AddItem wb.Name
    Next

    ComboBox1 = ActiveWorkbook.Name
End Sub

'~~> This lets you toggle between all open workbooks
Private Sub Combobox1_Change()
    If ComboBox1 <> "" Then Application.Workbooks(ComboBox1.Text).Activate

    Label1.Caption = "": RefEdit1 = ""
End Sub

'~~> And this lets you choose the relevant range
Private Sub RefEdit1_Change()
    Label1.Caption = ""

    If RefEdit1.Value <> "" Then _
    Label1.Caption = "[" & ComboBox1 & "]" & RefEdit1
End Sub

This is what you get when you run the Userform

---

---

Comments

[personne3000]

This will fail if the destination workbook name has spaces, or if the destination sheet has spaces (in Excel 2007 at least). You need to split the refEdit value around the "!", remove quotes around the sheet name if the RefEdit added those, and set the reference as '[workbook name]sheet name'!Reference (note the single quotes)

[Darren Bartrup-Cook]

Bit of a late comment, but as @personne3000 indicated, change the last line to Label1.Caption = "'[" & ComboBox1 & "]" & Replace(RefEdit1,"!","'!") and should be good to go with spaces.

[ChrisB]

Clever solution! I also used a Userform but with a "Select Range" command button instead of a RefEdit control. When clicked it launches a type 8 InputBox and saves the full range address (including book and sheet references) to a Label. When selecting a range in a different book Excel didn't return focus to the Userform on the original book. To get around this I set the form to vbModeless when originally shown. After the user selects a range, I hide the form, activate the original book, add a 1/4 second delay, and show the form again. It was a pain to setup but works well now.