Can a workbook variable be set by clicking the workbook?

K

Kobayashi

Dave,

Many thanks for responded so quickly!

However, I should have made it clearer by saying that I want to selec
a different workbook to the one that the procedure is run from?

Ordinarily I would just set a variable to the other workbook that
want. However, the workbook name will always be different so I nee
someway of getting the user to select it?

Thanks,

Adria
 
D

Dave Peterson

You can use Window on the menubar to go to other open workbooks.

You could even arrange your windows to be tiled (if there aren't too many??) and
then resize the windows.

Or maybe best is to create a little userform that shows the list of open
workbooks.

I created a small form with a combobox and two commandbuttons.

I put this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
WkbkName = Me.ComboBox1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wkbk As Workbook
With Me.ComboBox1
.Style = fmStyleDropDownList
For Each wkbk In Application.Workbooks
.AddItem wkbk.Name
Next wkbk
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Ok"

End Sub

Then in a general module:

Option Explicit
Public WkbkName As String
Sub testme()
Dim wkbk As Workbook
WkbkName = ""
UserForm1.Show
If WkbkName = "" Then
'do nothing--user didn't select one
Else
Set wkbk = Workbooks(WkbkName)
MsgBox wkbk.Name 'for example
End If
End Sub
 
D

Dave Peterson

If you want to skip the workbook with the code, you can do this:

Private Sub UserForm_Initialize()
Dim wkbk As Workbook
With Me.ComboBox1
.Style = fmStyleDropDownList
For Each wkbk In Application.Workbooks
if wkbk.name = thisworkbook.name then
'do nothing
else
.AddItem wkbk.Name
end if
Next wkbk
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Ok"

End Sub

Dave said:
You can use Window on the menubar to go to other open workbooks.

You could even arrange your windows to be tiled (if there aren't too many??) and
then resize the windows.

Or maybe best is to create a little userform that shows the list of open
workbooks.

I created a small form with a combobox and two commandbuttons.

I put this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
WkbkName = Me.ComboBox1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wkbk As Workbook
With Me.ComboBox1
.Style = fmStyleDropDownList
For Each wkbk In Application.Workbooks
.AddItem wkbk.Name
Next wkbk
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Ok"

End Sub

Then in a general module:

Option Explicit
Public WkbkName As String
Sub testme()
Dim wkbk As Workbook
WkbkName = ""
UserForm1.Show
If WkbkName = "" Then
'do nothing--user didn't select one
Else
Set wkbk = Workbooks(WkbkName)
MsgBox wkbk.Name 'for example
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top