HI..

1.Is it possible to select a workbook by a browse button which we normally
see on windows ...and trap its name in a variable for processing?

2. is it possible to select a worksheet.... using a browse button and then
trap its name ?

the basic idea is to minimize hard coding of the workbook/worksheets path
....and also sometimes it happens that the path changes...then the user will
have to edit the code.

thanks a lot
Monika

Monika,

Hello again.

1. GetOpenFileName is the beastie for you. An example

Dim sFilename

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

If you want to set a start directory, precede the GetOpenFileName with
ChDir.

2. A bit trickier, there is no function to utilise. This code uses my
favourite current technique

Sub BrowseSheets
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

iBooks = 0

TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i

PrintDlg.Buttons.Left = 240

CurrentSheet.Activate

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

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

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
HI..

1.Is it possible to select a workbook by a browse button which we normally
see on windows ...and trap its name in a variable for processing?

2. is it possible to select a worksheet.... using a browse button and then
trap its name ?

the basic idea is to minimize hard coding of the workbook/worksheets path
...and also sometimes it happens that the path changes...then the user
will
have to edit the code.

thanks a lot
Monika

Just for interest, if you want to see a commented version of the
BrowseSheets code (slightly different - uses check boxes and has added code
to print out the selected sheets), you can look at John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip48.htm
Displaying a Menu of Worksheets to Print

Only if your are interested in how the code works.

--
Regards,
Tom Ogilvy


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

Hello again.

1. GetOpenFileName is the beastie for you. An example

Dim sFilename

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

If you want to set a start directory, precede the GetOpenFileName with
ChDir.

2. A bit trickier, there is no function to utilise. This code uses my
favourite current technique

Sub BrowseSheets
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

iBooks = 0

TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i

PrintDlg.Buttons.Left = 240

CurrentSheet.Activate

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

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

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
HI..

1.Is it possible to select a workbook by a browse button which we
normally
see on windows ...and trap its name in a variable for processing?

2. is it possible to select a worksheet.... using a browse button and
then
trap its name ?

the basic idea is to minimize hard coding of the workbook/worksheets
path
...and also sometimes it happens that the path changes...then the user
will
have to edit the code.

thanks a lot
Monika

Tom is right, that is where the idea originates. My adaptation uses
optionbuttons and activates the selected sheet to specifically address
Monika's problem.

Normally do attribute, but I find it so useful, and am using it so much
recently, I often forget.

Bob

"Tom Ogilvy" wrote in message
...
Just for interest, if you want to see a commented version of the
BrowseSheets code (slightly different - uses check boxes and has added
code
to print out the selected sheets), you can look at John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip48.htm
Displaying a Menu of Worksheets to Print

Only if your are interested in how the code works.

--
Regards,
Tom Ogilvy

No intent to be critical - just thought if the OP wanted to understand the
code in the interest of learning, since John's is commented, it would be
useful to look at.

for example, these lines

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

are kind of a mystery (to me at least) unless you read John's comment on
them.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Tom is right, that is where the idea originates. My adaptation uses
optionbuttons and activates the selected sheet to specifically address
Monika's problem.

Normally do attribute, but I find it so useful, and am using it so much
recently, I often forget.

Bob

"Tom Ogilvy" wrote in message
...
Just for interest, if you want to see a commented version of the
BrowseSheets code (slightly different - uses check boxes and has added
code
to print out the selected sheets), you can look at John Walkenbach's
site:

http://j-walk.com/ss/excel/tips/tip48.htm
Displaying a Menu of Worksheets to Print

Only if your are interested in how the code works.

--
Regards,
Tom Ogilvy

Not taken as such Tom, and attributing is good for the soul. I only replied
to point out the changes I made, trying to show the versatility of John's
original idea (I have used it for worksheets, workbooks, and something else
that escapes me right now, and only once to print). As I said, I only
discovered the technique from an OP asking a question, and have found it
really useful (probably to the extent that I over-use it).

Your point about comments is good, although I admit I deliberately stripped
them in the code I shipped. This was for two reasons. Firstly, I really
abhor meaningless comments (and John has a couple of these, although the one
you point out is extremely useful) as I believe that bad comments can make a
reader think all the comments are bad and then ignore them.Secondly, I read
it that Monika wanted a solution, not a technique she can build upon. Upon
reflection, this is probably a mistaken assumption as Monika is posting in
the programming group (which I think is the most advanced group<vbg), and
she has posted quite a few questions recently.

Bob

"Tom Ogilvy" wrote in message
...
No intent to be critical - just thought if the OP wanted to understand the
code in the interest of learning, since John's is commented, it would be
useful to look at.

for example, these lines

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

are kind of a mystery (to me at least) unless you read John's comment on
them.

--
Regards,
Tom Ogilvy

fname = Application.GetOpenfilename()

or

fname = Application.GetSaveAsFilename

is that what you want?

Or are you talking about just selecting folder:



--
Regards,
Tom Ogilvy

"monika" wrote in message
...
HI..

1.Is it possible to select a workbook by a browse button which we normally
see on windows ...and trap its name in a variable for processing?

2. is it possible to select a worksheet.... using a browse button and then
trap its name ?

the basic idea is to minimize hard coding of the workbook/worksheets path
...and also sometimes it happens that the path changes...then the user
will
have to edit the code.

thanks a lot
Monika