Can a workbook variable be set by clicking the workbook?
 

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

--
Kobayash
-----------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...info&userid=87
View this thread: http://www.excelforum.com/showthread.php?threadid=27331

Can a workbook variable be set by clicking the workbook?
 

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


Kobayashi wrote:

Dave,

Many thanks for responded so quickly!

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

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

Thanks,

Adrian

--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=273314

--

Dave Peterson

Can a workbook variable be set by clicking the workbook?
 

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 Peterson wrote:

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

Kobayashi wrote:

Dave,

Many thanks for responded so quickly!

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

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

Thanks,

Adrian

--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=273314

--

Dave Peterson


--

Dave Peterson