selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

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

selecting workbook/worksheet from browse button
 

Monika,

Application.ScreenUpdating simply turns off the re-painting of the Excel
window when your code makes a change, for speed. It is perfectly okay to do
this, as long as you re-set at the end, so you see the final sum of changes.

As to what the code does, here is a quick synopsis
- firstly it checks if the workbook is protected, and exit if so
- then it adds a dialog sheet, which is an old style form, but still
maintained in Excel
- then we have a loop that goes through the data and determines which items
that fit our criteria, and adds a control to our new dialog sheet for each
required item. In this case, we pick out each worksheet and add an option
button, although I have given other suggestions that check all open
workbooks, and add checkboxes. I added optionbuttons as you can only
activate one sheet per your requirement, and only one option button can be
selected at a time, whereas many checkboxes can be selected
- it then resizes the dialog accordingly to how many controls are on it, and
adds a title
- then it forces the focus onto the OK button (a bit of magic here, but
obscure)
- then it shows the dialog so that you the user can make your choice
- a loop is run which checks which option is selected, and activates the
sheet associated with that option
- finally, it deletes the dialog

Methinks you apologise too much (that is a joke, not a criticism). Tom made
a perfectly valid point, and I added to that point. I got this idea from
John Walkenbach, but I did change it including stripping the comments
(partly to assist me making the changes, partly as I explained in a later
posting). Although I wouldn't go so far to say that this is all John's idea,
these things are rarely one person's work they usually are the culmination
of many inputs, but John refined it, and published a very nice working
solution on his site. I have acknowledged this previously, and it is good to
acknowledge, although I cannot promise that I will always acknowledge where
I get these ideas from, but I do try.

I like questions such as you post, this one has led me to create a generic
solution that I can easily adapt to many problems, and I get the feeling you
are learning lots, so keep it up, you'll get tons of support here.

--

HTH

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

"monika" wrote in message
...
Thank you both for providing help...

I tried the code for Workbook...its exactly what I wanted. I can trap the
whole filepath in a variable. The worksheet code is also running ... but
its
not so comprehendible ...I tried the link
http://j-walk.com/ss/excel/tips/tip48.htm
to understand the code. but it doesn't explain the concept.

like i cannot make out the meaning of Application.ScreenUpdating ..forall
this i need to study the Microsoft links for objects and all ...

but thanks again for the wonderful prompt solution...!

best regards
Monika
(i hope my posting ques in this group wouldn't become an issue... ;-))


"Tom Ogilvy" wrote in message
...
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

selecting workbook/worksheet from browse button
 

hi Bob,

Thanks for the superb explanation you have given me... it adds meaning to
the code finally... i can now understand a lot from it. I think this part of
solution is a wonderful thing to develop in automation of routine work.
specially when u want least intervention of the end user. its makes i so
versatile and efficient and so elegant....

i think picking up a workbook..specially a worksheet by user choice is
superb...i didn't know that vba is so strong.

thanks a tons again ...
regards
Monika



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

Application.ScreenUpdating simply turns off the re-painting of the Excel
window when your code makes a change, for speed. It is perfectly okay to
do
this, as long as you re-set at the end, so you see the final sum of
changes.

As to what the code does, here is a quick synopsis
- firstly it checks if the workbook is protected, and exit if so
- then it adds a dialog sheet, which is an old style form, but still
maintained in Excel
- then we have a loop that goes through the data and determines which
items
that fit our criteria, and adds a control to our new dialog sheet for each
required item. In this case, we pick out each worksheet and add an option
button, although I have given other suggestions that check all open
workbooks, and add checkboxes. I added optionbuttons as you can only
activate one sheet per your requirement, and only one option button can be
selected at a time, whereas many checkboxes can be selected
- it then resizes the dialog accordingly to how many controls are on it,
and
adds a title
- then it forces the focus onto the OK button (a bit of magic here, but
obscure)
- then it shows the dialog so that you the user can make your choice
- a loop is run which checks which option is selected, and activates the
sheet associated with that option
- finally, it deletes the dialog

Methinks you apologise too much (that is a joke, not a criticism). Tom
made
a perfectly valid point, and I added to that point. I got this idea from
John Walkenbach, but I did change it including stripping the comments
(partly to assist me making the changes, partly as I explained in a later
posting). Although I wouldn't go so far to say that this is all John's
idea,
these things are rarely one person's work they usually are the culmination
of many inputs, but John refined it, and published a very nice working
solution on his site. I have acknowledged this previously, and it is good
to
acknowledge, although I cannot promise that I will always acknowledge
where
I get these ideas from, but I do try.

I like questions such as you post, this one has led me to create a generic
solution that I can easily adapt to many problems, and I get the feeling
you
are learning lots, so keep it up, you'll get tons of support here.

--

HTH

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

"monika" wrote in message
...
Thank you both for providing help...

I tried the code for Workbook...its exactly what I wanted. I can trap
the
whole filepath in a variable. The worksheet code is also running ... but
its
not so comprehendible ...I tried the link
http://j-walk.com/ss/excel/tips/tip48.htm
to understand the code. but it doesn't explain the concept.

like i cannot make out the meaning of Application.ScreenUpdating
...forall
this i need to study the Microsoft links for objects and all ...

but thanks again for the wonderful prompt solution...!

best regards
Monika
(i hope my posting ques in this group wouldn't become an issue... ;-))


"Tom Ogilvy" wrote in message
...
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

selecting workbook/worksheet from browse button
 

Hi Monika,

Thanks for those kind words, and I am glad that my explanation helped you.

What I like about this solution is that it is simple and looks solid and
professional, it could easily be mistaken for an Excel builtin, which in
essence it is.

Regards

Bob

"monika" wrote in message
...
hi Bob,

Thanks for the superb explanation you have given me... it adds meaning to
the code finally... i can now understand a lot from it. I think this part
of
solution is a wonderful thing to develop in automation of routine work.
specially when u want least intervention of the end user. its makes i so
versatile and efficient and so elegant....

i think picking up a workbook..specially a worksheet by user choice is
superb...i didn't know that vba is so strong.

thanks a tons again ...
regards
Monika



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

Application.ScreenUpdating simply turns off the re-painting of the Excel
window when your code makes a change, for speed. It is perfectly okay to
do
this, as long as you re-set at the end, so you see the final sum of
changes.

As to what the code does, here is a quick synopsis
- firstly it checks if the workbook is protected, and exit if so
- then it adds a dialog sheet, which is an old style form, but still
maintained in Excel
- then we have a loop that goes through the data and determines which
items
that fit our criteria, and adds a control to our new dialog sheet for
each
required item. In this case, we pick out each worksheet and add an
option
button, although I have given other suggestions that check all open
workbooks, and add checkboxes. I added optionbuttons as you can only
activate one sheet per your requirement, and only one option button can
be
selected at a time, whereas many checkboxes can be selected
- it then resizes the dialog accordingly to how many controls are on it,
and
adds a title
- then it forces the focus onto the OK button (a bit of magic here, but
obscure)
- then it shows the dialog so that you the user can make your choice
- a loop is run which checks which option is selected, and activates the
sheet associated with that option
- finally, it deletes the dialog

Methinks you apologise too much (that is a joke, not a criticism). Tom
made
a perfectly valid point, and I added to that point. I got this idea from
John Walkenbach, but I did change it including stripping the comments
(partly to assist me making the changes, partly as I explained in a
later
posting). Although I wouldn't go so far to say that this is all John's
idea,
these things are rarely one person's work they usually are the
culmination
of many inputs, but John refined it, and published a very nice working
solution on his site. I have acknowledged this previously, and it is
good
to
acknowledge, although I cannot promise that I will always acknowledge
where
I get these ideas from, but I do try.

I like questions such as you post, this one has led me to create a
generic
solution that I can easily adapt to many problems, and I get the feeling
you
are learning lots, so keep it up, you'll get tons of support here.

--

HTH

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

"monika" wrote in message
...
Thank you both for providing help...

I tried the code for Workbook...its exactly what I wanted. I can trap
the
whole filepath in a variable. The worksheet code is also running ...
but
its
not so comprehendible ...I tried the link
http://j-walk.com/ss/excel/tips/tip48.htm
to understand the code. but it doesn't explain the concept.

like i cannot make out the meaning of Application.ScreenUpdating
..forall
this i need to study the Microsoft links for objects and all ...

but thanks again for the wonderful prompt solution...!

best regards
Monika
(i hope my posting ques in this group wouldn't become an issue... ;-))


"Tom Ogilvy" wrote in message
...
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