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?

 

[mikerickson]

The RefEdit control will capture the name of the workbook, unless that workbook is active when the selection is made.

What you could do is put this in the RefEdit AfterUpdate event

RefEdit1.Tag = Range(RefEdit1.Text).Address(,,,True)

and then your last routine would become

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

Although, I have doubts about using ActiveCell, after the ListBox code has changed the ActiveWorkbook.

 

[Rick Rothstein]

#3 Command Button. Here is the code

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

Does changing your CommandButton Click event code to this do what you want?

Private Sub CommandButton1_Click()
  Range(RefEdit1.Value).Select
  Unload Me
End Sub

 

[randolphoralph]

mikerickson- it does appear that I can not use ActiveCell after the ListBox code has changed the ActiveWorkbook.

Rick Rothstein- your suggestion to changing the CommandButton Click event code does not return what I want.

I am wanting the RefEdit1.Value returned to the active cell prior to Activating the UserForm.

I am thinking prior to the Userform opening I need to specify the Active Book...Active Sheet...and Active Cell and then set that equal to RefEdit1.Value?

 

[Rick Rothstein]

Okay, I think I misunderstood your request. Let me try again. Are you trying to return the value in another sheet to the current sheet's active cell? If so, try this... change your UserForm code to the following.

Private CurrentWorkbook As Workbook, CurrentCell As Range
 
Private Sub ListBox1_Click()
    Windows(ListBox1.Value).Activate
End Sub
 
Private Sub UserForm_Activate()
    Dim wkb As Workbook
    Set CurrentWorkbook = ThisWorkbook
    Set CurrentCell = ActiveCell
    For Each wkb In Workbooks
        If Windows(wkb.Name).Visible Then ListBox1.AddItem wkb.Name
    Next
End Sub
 
Private Sub CommandButton1_Click()
  Dim RefEditValue As Variant
  RefEditValue = Range(RefEdit1.Value).Value
  CurrentWorkbook.Activate
  CurrentCell.Value = RefEditValue
  Unload Me
End Sub

 

[randolphoralph]

Rick

I have tried this and when I run the code I am getting an error under the CommandButton1_Click event stating Run-time error '424': Object required and the following is highlighted yellow.

CurrentWorkbook.Activate

 

[randolphoralph]

After trying to debug it appears that I am getting a empty value for the following

Set CurrentWorkbook = ThisWorkbook
Set CurrentCell = ActiveCell

 

[Rick Rothstein]

I have tried this and when I run the code I am getting an error under the CommandButton1_Click event stating Run-time error '424': Object required and the following is highlighted yellow.

CurrentWorkbook.Activate

Did you copy/paste all of the code I posted into your UserForm code module (replacing what you currently had in it)? I think you may have missed my first code line, namely this one...

Private CurrentWorkbook As Workbook, CurrentCell As Range

which must be located where I showed it in my posting (that is, outside of any procedures).

 

[randolphoralph]

Rick,

You are correct I missed the 1st line of your code. I have added it...

Thanks

One last question...How could I get change the

CurrentCell.Value = RefEditValue

to return something like the following?

CurrentCell.Formula = "=INDEX(" & RefEditValue & ",MATCH(" & MatchValue & "," & LookUpRange & ",0))"

 

[Rick Rothstein]

You are correct I missed the 1st line of your code. I have added it...

Then can we assume the code I posted does what you wanted it to do? Or are there still issues that have not been addressed?

FOLLOW UP TO YOUR EDITED ADDITIONS

Without seeing the values set into RefEditValue, MatchValue or LookUpRange, all I can do is guess. My guess is maybe you are missing quote marks that are needed in the final formula. Give this a try...

CurrentCell.Formula = "=INDEX(""" & RefEditValue & """,MATCH(""" & MatchValue & """," & LookUpRange & ",0))"