-
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.
Macro to choose an open workbook and Worksheet
- Thread starter DChambers
- Start date
joefrench
Active Member
- Joined
- Oct 4, 2006
- Messages
- 357
Hmm. A MsgBox is just going to ...well.....give you a message and an InputBox is just going to ask for some input. I think what you will want is a UserForm with two listboxes in it.
Try creating a userform with two listBoxes and add this code. It is not complete yet but I am working on activating the selected sheet
Try creating a userform with two listBoxes and add this code. It is not complete yet but I am working on activating the selected sheet
Code:
Private Sub ListBox1_Click()
Dim actvWb, wsName As String
Dim ws As Object
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
actvWb = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
End If
Next i
Application.Workbooks(actvWb).Activate
End Sub
Private Sub UserForm_Initialize()
Dim wbName As String
Dim wb As Object
Dim listQty As Integer
Dim rStart As Range
Set rStart = Sheet1.Range("A:A")
With Me.ListBox1
For Each wb In Application.Workbooks
wbName = wb.Name
.AddItem wbName
Next
End With
End Sub
Last edited:
Upvote
0
joefrench
Active Member
- Joined
- Oct 4, 2006
- Messages
- 357
Try this:
Code:
Private Sub ListBox1_Click()
Dim actvWb, wsName As String
Dim ws As Worksheet
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
actvWb = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
End If
Next i
Application.Workbooks(actvWb).Activate
Me.ListBox2.Clear
With Me.ListBox2
For Each ws In Application.Workbooks(actvWb).Worksheets
wsName = ws.Name
.AddItem wsName
Next
End With
End Sub
'***********************************
Private Sub ListBox2_Click()
Dim actvWs As String
Dim ws As Worksheet
For i = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(i) Then
actvWs = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)
End If
Next i
Sheets(actvWs).Activate
Unload Me
End Sub
'***********************************
Private Sub UserForm_Initialize()
Dim wbName As String
Dim wb As Object
Dim listQty As Integer
Dim rStart As Range
Set rStart = Sheet1.Range("A:A")
With Me.ListBox1
For Each wb In Application.Workbooks
wbName = wb.Name
.AddItem wbName
Next
End With
End Sub
Upvote
0
Similar threads
- Question
