VBA Dialog box to select range in different workbook

by

Since I was free, I created an example for you

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

enter image description here

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

enter image description here

enter image description here

enter image description here

More Related Contents:

  1. Is the . in .Range necessary when defined by .Cells?
  2. Convert an entire range to uppercase without looping through all the cells
  3. Detect if range is empty
  4. How to copy only values in excel vba from a range?
  5. Copy a selected range to another worksheet
  6. VBA: Modify chart data range
  7. Sequence of numbers based on start and end value of 2 cells [closed]
  8. Function to convert column number to letter?
  9. Creating an Array from a Range in VBA
  10. Better way to find last used row
  11. 2 Dimensional array from range [closed]
  12. How can I create a calendar input in VBA Excel?
  13. Using Google Chrome in Selenium VBA (Installation Steps)
  14. Dir() function not working in Mac Excel 2011 VBA
  15. How to use workbook.saveas with automatic Overwrite
  16. Range limit conundrum
  17. How do you run a .exe with parameters using vba’s shell()?
  18. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  19. Passing variable from Form to Module in VBA
  20. How to give a time delay of less than one second in excel vba?
  21. Changing the date format to yyyy-mm-dd
  22. Delete all duplicate rows Excel vba
  23. Excel VBA: Convert a date string to a Unix timestamp
  24. excel to pdf ignores print area and calls printer
  25. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  26. VBA copy rows that meet criteria to another sheet
  27. Function Overloading and UDF in Excel VBA
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment