Return Range using Userform

[randolphoralph]

I have created a Userform with 3 items...

#1 List Box which contains a list of Workbooks. Here is the code:

Private Sub ListBox1_Click()
    Windows(ListBox1.Value).Activate
    
    End Sub

Private Sub UserForm_Activate()
    Dim wkb As Workbook
    For Each wkb In Workbooks
        If Windows(wkb.Name).Visible Then _
          ListBox1.AddItem wkb.Name
    Next
End Sub

#2 RefEdit that allows me to select a range.

#3 Command Button. Here is the code

Private Sub CommandButton1_Click()
ActiveCell = RefEdit1.Value
Unload.Me
End Sub

The issue I have is the List Box allows me to change between workbooks, but the RefEdit does not capture the workbook name.

So for example if I am in Book1.xls and select Book2.xls in the ListBox and select the range Sheet1!A1:B5 the RefEdit only shows Sheet1!A1:B5.

How would I get RefEdit to return [Book2.xlsx]Sheet1!A1:B5?

 

[randolphoralph]

Rick...your awesome the code does what it is meant to do. I did have one other question in my previous post that I was needing some pointers on.

 

[Rick Rothstein]

Rick...your awesome the code does what it is meant to do. I did have one other question in my previous post that I was needing some pointers on.

Good, I'm glad the code worked for you. As to your additional question, I put my answer into my previous message as an edited addition to match your edited addition. So, go back and relook at my last posting.

 

[randolphoralph]

Rick I am sorry to be a pain. I after trying that I am getting an run-time error 13 'type mismatch'.

So in order to try to troubleshoot the issue I decided to change the formula to the following and still receive the same error

CurrentCell.Formula = "=" & RefEditValue & ""

 

[randolphoralph]

I tried this and it returns the value from RefEdit (ie....=Sheet1A2:A5) but does not include the workbook....I need it to return =[Book2.xls]Sheet1A2:A5

CurrentCell.Formula = "=" & RefEdit1.Value & ""

 

[Rick Rothstein]

I tried this and it returns the value from RefEdit (ie....=Sheet1A2:A5) but does not include the workbook

CurrentCell.Formula = "=" & RefEdit1.Value & ""

You are not handling the quote marks correctly. Try it this way...

CurrentCell.Formula = "=""" & RefEditValue & """"

 

[randolphoralph]

I tried the

CurrentCell.Formula = "=""" & RefEditValue & """"

and get the run-time error 13 'Type mismatch'

 

[Rick Rothstein]

As I said, it is hard to judge exactly what to do without seeing what is in the cell your are referencing or what is in the two variable in the formula you are trying to construct. But let me try another guess. Use this statement to assign the value to RefEditValue rather than the code line I posted originally...

RefEditValue = "'[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "'!" & Range(RefEdit1.Value).Address

Then try the CurrentCell.Formula code line you posted and see if it works as you want.

 

[randolphoralph]

Awesome!!!! That does everything I need. Thank you Rick! I owe you a drink after that one