excel - 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.

Answer 1

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

---

---