Hari,

Cracking up. Of course it could use single option buttons. Here is a revised
version, also ignoring hidden workbooks

Option Explicit

Sub Hari()
Dim sFilename

sFilename = GetWorkbook
If sFilename < "" Then
Workbooks(sFilename).Activate
Else
sFilename = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If sFilename < False Then
Workbooks.Open Filename:=sFilename
End If
End If

End Sub

Function GetWorkbook()
Const nPerColumn As Long = 35 'number of items per* column
Const nWidth As Long = 7 'width of each lette*r
Const nHeight As Long = 18 'height of each row
Const sID As String = "___WorkbookSelect" 'name of dialog shee*t
Const kCaption As String = " Select workbook to open"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cLeft As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim ob As OptionButton

Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Function
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To Application.Workbooks.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If Windows(Workbooks(i).Name).Visible Then
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each ob In thisDlg.OptionButtons
If ob.Value = xlOn Then
GetWorkbook = ob.Caption
Exit For
End If
Next ob
Else
GetWorkbook = ""
End If
Application.DisplayAlerts = False

.Delete

End With

End Function







--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Hari,

It would not be possible to change from a checkbox to optionbuttons. I
agree
with you that buttons are nicer, but the code is using a dialog, and thus
once the dialog is shown my code does not get a look in until a button is
pressed. And as optionbuttons allow more than 1 at a time to be clicked,
by
the time my code knows there could be any number clicked. To achieve what
you suggest would need a custom userform I think.

As to Personal.xls, you could do it one of two ways, you could check
specifically for that workbook, in the loop that sets it up, like so

For i = 1 To Application.Workbooks.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If LCase(Application.Workbooks(i).Name) < "personal.xls" Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i

or, and this would be my chosen way, ignore all hidden workbooks, like so

For i = 1 To Application.Workbooks.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If Application.Windows(Application.Workbooks(i).Name) .Visible
Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" wrote in message
...
Hi Bob,

Is it possible for this code to be modified from a check-box to a Radio
control kind of box where only one workbook could be chosen.

Also, presently your code gives an option to choose personal.xls as
well.
Could that be omitted from the list.

Lastly (hope am not overreaching myself...) may be u could post this
code
in
ur website the way sum-product is there. I think this is also a nice
tool
which many people could use/integrate in their code.

Thanks a lot,
Hari
India


"Bob Phillips" wrote in message
...
Hi Hari,

Sorry about that. I had adapted some code I used with worksheets, and
did
not fully change it it.

Change the line

Set CurrentSheet = ActiveWorkbook.Worksheets(i)

to

Set CurrentSheet = ActiveWorkbook.ActiveSheet

and it should be okay.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" wrote in message
...
Hi Bob,

Just a..

If I have 2 workbooks open (excluding personal.xls). One of the
workbooks
have 8 sheets while the other one has 3. Both these contain data. I
create
a
new workbook by pressing Ctrl +N and if I run the macro then the
macro
breaks at -- Set CurrentSheet = ActiveWorkbook.Worksheets(i) -- by
generating a -- Run time error '9' subscript error out of range.

If I comment the above statement and re-run the macro then it doesnt
break.

And since a new workbook has 3 worksheets by default (as set in my
system)
so in the macro when I hover my mouse over the value of i, it shows
as
4.
In
Immediate window if I type -- ?activeworkbook.name -- I get Book 1 --
and
if
I type -- ?activesheet.name-- I get Sheet 1. Now, Book1 is the newly
created workbook (Ctrl +N) just before the running of the macro. Your
code
is trying to access the fourth worksheet (i=4) within that workbook ?

Thanks a lot,
Hari
India