creating lists to choose from referencing cell ranges in VBA?

[hissonrr]

Good day all,

I was wondering if it is possible to create a list to choose from in a pop up box references another sheet.

For example... this is my current script below and it basically copy's another sheet from a closed workbook in my drive and renames in based on a certain cell range.

What I would like to do is instead of having the "ActiveSheet.Name = Worksheets("Geoprog").Range("A52")" command just copy a single name from a range of cells, I would like to have a dialogue box pop up that had a bunch of choices of cell ranges to choose from to select the re-naming.

Is this possible using VBA?

if anyone can help let me know.

-R

Public Sub CopySheetFromClosedWorkbook()    Dim sourceBook As Workbook
    Application.ScreenUpdating = False
    
    'sets destination of workbook to copy sheets from
    Set sourceBook = Workbooks.Open("C:\Users\rhissong\Desktop\test PLAN REVIEW.xlsm")
    
    'copy's sheets from this destination
    sourceBook.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    'closes workbook
    sourceBook.Close
    
    ActiveSheet.Name = Worksheets("Geoprog").Range("A52")
    
    Sheets("Asset Team Checklist").Select
    
    Application.ScreenUpdating = True
End Sub

 

[Akuini]

Good day all,

I was wondering if it is possible to create a list to choose from in a pop up box references another sheet.

For example... this is my current script below and it basically copy's another sheet from a closed workbook in my drive and renames in based on a certain cell range.

What I would like to do is instead of having the "ActiveSheet.Name = Worksheets("Geoprog").Range("A52")" command just copy a single name from a range of cells, I would like to have a dialogue box pop up that had a bunch of choices of cell ranges to choose from to select the re-naming.

Try this:
Let's say you have the name list in sheet2 col A starting from A1 down.
Create a userform, insert a listbox & a commandbutton. Don't change their name, they should be UserForm1,ListBox1 & CommandButton1.
Put this code in the userform code window,

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] UserForm_Initialize()
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"sheet2"[/COLOR])
ListBox1.List = .Range([COLOR=brown]"A1"[/COLOR], .Cells(.Rows.Count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)).Value
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
CommandButton1.Caption = [COLOR=brown]"OK"[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] CommandButton1_Click()
ActiveSheet.Name = ListBox1.Value
[COLOR=Royalblue]Unload[/COLOR] [COLOR=Royalblue]Me[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

As for your Sub, to open the userfrom use 'UserForm1.Show", so it becomes:

[FONT=lucida console][COLOR=Royalblue]Public[/COLOR] [COLOR=Royalblue]Sub[/COLOR] CopySheetFromClosedWorkbook()
[COLOR=Royalblue]Dim[/COLOR] sourceBook [COLOR=Royalblue]As[/COLOR] Workbook
    Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
    
    [I][COLOR=seagreen]'sets destination of workbook to copy sheets from[/COLOR][/I]
    [COLOR=Royalblue]Set[/COLOR] sourceBook = Workbooks.Open([COLOR=brown]"C:\Users\rhissong\Desktop\test PLAN REVIEW.xlsm"[/COLOR])
    
    [I][COLOR=seagreen]'copy's sheets from this destination[/COLOR][/I]
    sourceBook.Sheets([COLOR=brown]"Sheet1"[/COLOR]).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    [I][COLOR=seagreen]'closes workbook[/COLOR][/I]
    sourceBook.Close
    
    UserForm1.Show
    
    Sheets([COLOR=brown]"Asset Team Checklist"[/COLOR]).[COLOR=Royalblue]Select[/COLOR]
    
    Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[hissonrr]

Works perfectly

I didn't even know userforms exist!

thanks for the lesson/help. I appreciate it.


-R

 

[Akuini]

Works perfectly

I didn't even know userforms exist!

thanks for the lesson/help. I appreciate it.


-R

You're welcome, glad to help, & thanks for the feedback.