VBA to produce diologe box for user to select worksheet in workbook.

[VBA learner ITG]

Hello my peers,

I was wondering if anyone has come across a piece of code that can select open an excel file and list the tabs in the opened workbook in a diologe box for the user to select.

 

[Logit]

.
Here is some of the code :

Private Sub UserForm_Initialize()


' Initialise user form
' Fill combo box with sheet names (start at 2)


Dim i As Long
Me.cmbListSheets.Clear
For i = 2 To Worksheets.Count
    Me.cmbListSheets.AddItem Worksheets(i).Name
Next i


End Sub


Private Sub cmbListSheets_Change()


' Check user selected something and enable Go button


If Me.cmbListSheets.Value <> "" Then


    Me.cmdGo.Enabled = True
    Me.cmdGo.Locked = False
    
Else


    Me.cmdGo.Enabled = False
    Me.cmdGo.Locked = True
    
End If


End Sub


Private Sub cmdCancel_Click()


'Unload form if cancel clicked


Unload Me


End Sub


Private Sub cmdGo_Click()


' Activate selected sheet


Dim ws As Worksheet


Set ws = ActiveWorkbook.Worksheets(Me.cmbListSheets.Value)


Unload Me
ws.Activate


End Sub

You can download the project here : https://www.amazon.com/clouddrive/share/sIYjhT955yKbJ9KxeLCasql4Vo2n8DdHPMmv3lUxcjv

This project does not include code for selecting a file first. Perhaps you can build on it ?

 

[VBA learner ITG]

This has now been resolved.

strMsg = "Choose the right sheet by number" & vbCrLf & vbCrLf
For Idx = 1 To wbCopyFrom.Worksheets.count
Set Sh = wbCopyFrom.Worksheets(Idx)
strMsg = strMsg & Idx & " => " & Sh.Name & vbCrLf
Next
res = CInt(InputBox(prompt:=strMsg, title:="Choose sheet to copy", Default:=1))
On Error Resume Next
Set wsCopyFrom = wbCopyFrom.Worksheets(res)
On Error GoTo 0
If wsCopyFrom Is Nothing Then
MsgBox "Wrong sheet number"
Exit Sub
End If