0

I am looking for assistance in getting Excel to ask the user what workbook should be selected to work with. I have a Workbook that has buttons linked to macros to perform tasks but each month I need to import updated data. I would like to have the two workbooks open at the same time and then allow the user to be prompted which workbook should be utilized for importing updated data. Currently I have code that allows the user to select which sheet should be used but only from the document in which the Macro is run from. The monthly workbooks don't have a standard naming convention.

Sub Macro6()
'
' Macro6 Macro
'
' 
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


Sheets(optCaption).Activate

End If

End If

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True

End With


End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
phifer2088
  • 15
  • 1
  • 6
  • Investigate FileDialogs to allow the user to select a workbook. – Andy G Oct 25 '18 at 10:31
  • Also, you should not be using DialogSheets, they are a long obsolete feature. – Andy G Oct 25 '18 at 10:32
  • FileDialogs would allow the user to open a file if it is saved. Typically these files are sent in an e-mail and opened from withing the e-mail and not saved locally. – phifer2088 Oct 25 '18 at 10:35
  • If the workbook to choose is already open then build a UserForm to display a list of open workbooks to choose from. – Andy G Oct 25 '18 at 10:41
  • I would like a UserForm to list the open workbooks and then to be offered a list of sheets from the selected workbook. That is the goal, but I have not had success in making it happen or finding the answers to make it happen. – phifer2088 Oct 25 '18 at 12:10
  • Break it down. Practice building a userform, adding listboxes, getting the list of open workbooks... This is too broad a request for a stackoverflow question. – Andy G Oct 25 '18 at 12:18

1 Answers1

0

If you want a workbook, just use an Application.Inputbox with type:=8 (i.e. select range). This will allow them to select any cell in any worksheet in any open workbook.

The worksheet is the parent of the cell and the workbook is the parent of the worksheet.

dim rng as range, wb as workbook

set rng  = application.inputbox("select a cell on the workbook you want.", type:=8)

debug.print rng.address(0,0)
debug.print rng.address(0,0, external:=true)
debug.print rng.parent.name
debug.print rng.parent.codename
debug.print rng.parent.parent.name
debug.print rng.parent.parent.fullname

set wb = rng.parent.parent
debug.print wb.name
debug.print wb.worksheets(1).name
  • This allows me to select a cell from another workbook, but it does not make that sheet the active sheet for the remiander of the macro. – phifer2088 Oct 25 '18 at 12:19
  • Truth be told ... [you never need an ActiveSheet](https://stackoverflow.com/questions/10714251). –  Oct 25 '18 at 12:51