-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Userform to select worksheet
- Thread starter begoldar
- Start date
Sure Norie,
What I'm trying to do is write an auto_open application that will link two files together. In the first file (the one where the macro will be stored), there are multiple worksheets each with their own set of data. Since the one people will be working with will change each time they use the file, I want to be able to prompt them to choose the worksheet they will need when they first open the file, and then run other macros off that worksheet (specifically open another workbook with data and transfer data from specific cells into this worksheet utilizing the active sheet reference.
i.e. - User selects worksheet 3 (from 1,2,3,4,5,6,7) as the one they want to transfer data to from workbook B.
What I'm trying to do is write an auto_open application that will link two files together. In the first file (the one where the macro will be stored), there are multiple worksheets each with their own set of data. Since the one people will be working with will change each time they use the file, I want to be able to prompt them to choose the worksheet they will need when they first open the file, and then run other macros off that worksheet (specifically open another workbook with data and transfer data from specific cells into this worksheet utilizing the active sheet reference.
i.e. - User selects worksheet 3 (from 1,2,3,4,5,6,7) as the one they want to transfer data to from workbook B.
Upvote
0
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
If you are absolutely requiring a programmatic solution, there are several options available to you.
One is what I posted here
http://www.mrexcel.com/forum/showthread.php?t=301685
which places a combobox on the menu bar of your workbook for a drop-down list of all sheets in the workbook, and will only be active when that workbook is active.
Note, if other users are using 2007 then this combobox will not appear on the ribbon but it will be an add-in, so this option is best with versions <=XP.
Another alternative is the below macro which, if you stick it in a standard module, will let the user choose their desired sheet from option buttons which will list all visible sheets in the workbook. Presumably you would not want people activating hidden sheets, that's why you would have hidden them, so I wrote the code for visible sheets only.
One is what I posted here
http://www.mrexcel.com/forum/showthread.php?t=301685
which places a combobox on the menu bar of your workbook for a drop-down list of all sheets in the workbook, and will only be active when that workbook is active.
Note, if other users are using 2007 then this combobox will not appear on the ribbon but it will be an add-in, so this option is best with versions <=XP.
Another alternative is the below macro which, if you stick it in a standard module, will let the user choose their desired sheet from option buttons which will list all visible sheets in the workbook. Presumably you would not want people activating hidden sheets, that's why you would have hidden them, so I wrote the code for visible sheets only.
Code:
Sub SheetSelector()
Const ColItems As Long = 20
Const LetterWidth As Long = 20
Const HeightRowz As Long = 18
Const SheetID As String = "__SheetSelection"
Dim i%, TopPos%, iSet%, optCols%, intLetters%, optMaxChars%, optLeft%
Dim wsDlg As DialogSheet, objOpt As OptionButton, optCaption$, objSheet As Object
optCaption = "": i = 0
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(SheetID).Delete
Application.DisplayAlerts = True
Err.Clear
Set wsDlg = ActiveWorkbook.DialogSheets.Add
With wsDlg
.Name = SheetID
.Visible = xlSheetHidden
iSet = 0: optCols = 0: optMaxChars = 0: optLeft = 78: TopPos = 40
For Each objSheet In ActiveWorkbook.Sheets
If objSheet.Visible = xlSheetVisible Then
i = i + 1
If i Mod ColItems = 1 Then
optCols = optCols + 1
TopPos = 40
optLeft = optLeft + (optMaxChars * LetterWidth)
optMaxChars = 0
End If
intLetters = Len(objSheet.Name)
If intLetters > optMaxChars Then optMaxChars = intLetters
iSet = iSet + 1
.OptionButtons.Add optLeft, TopPos, intLetters * LetterWidth, 16.5
.OptionButtons(iSet).text = objSheet.Name
TopPos = TopPos + 13
End If
Next objSheet
If i > 0 Then
.Buttons.Left = optLeft + (optMaxChars * LetterWidth) + 24
With .DialogFrame
.Height = Application.Max(68, WorksheetFunction.Min(iSet, ColItems) * HeightRowz + 10)
.Width = optLeft + (optMaxChars * LetterWidth) + 24
.Caption = "Select sheet to go to"
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
Exit For
End If
Next objOpt
End If
If optCaption = "" Then
MsgBox "You did not select a worksheet.", 48, "Cannot continue"
Exit Sub
Else
MsgBox "You selected the sheet named ''" & optCaption & "''." & vbCrLf & "Click OK to go there.", 64, "FYI:"
Sheets(optCaption).Activate
End If
End If
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End Sub
Upvote
0
Hi Tom,
I've been looking for a solution to a problem and your post from 2008 is the closest I've found to a solution - I was wondering if you could help please?
What I'm trying to do is allow the user to select any number of (visible) sheets to copy and paste into a different workbook.
But the interface you created looks great!
Any idea how I can adapt your code to do this?
Thanks!
I've been looking for a solution to a problem and your post from 2008 is the closest I've found to a solution - I was wondering if you could help please?
What I'm trying to do is allow the user to select any number of (visible) sheets to copy and paste into a different workbook.
But the interface you created looks great!
Any idea how I can adapt your code to do this?
Thanks!
Upvote
0
phifer2088
New Member
- Joined
- May 23, 2013
- Messages
- 3
This works well for its intended purpose but I would likehelp modifying it to work for my situation. The short is I would like it to offer all sheets from any open excelworkbook.
Example:
I have two workbooks open. "Workbook A" and "Workbook 13-as4". ("Workbook 13-as4" does not have astandard naming convention. It is alwayssomething different when I receive it.) I am on my first sheet in "Workbook A" where there are buttonswith assigned macros. If I select button"import new data”, I would like to be prompted with a list of sheets fromany open workbook. It would thencontinue down the macro I currently use to format the data in multipledifferent formats that are useful to me based on my current task.
From looking at the code currently provided, I just need toknow if ActiveWorkbook can be changed to something along the lines ofAllWorkbooks.
Upvote
0
