Hi all

I'm trying to build a floating control box with several buttons on MS
Excel. Originally, I was able to build macro buttons on the first
sheet of my workbook. Let's refer to the first sheet as 'main'. The
other sheets we can call sheet1, sheet2, etc. up to sheet20.

On the 'main' worksheet I created 20 buttons corresponding to the 20
sheets. So anyone who wants to view a sheet, they will click on the
command button for that sheet to view it. However, what I really
wanted was a floating control box with all 20 command buttons present
in it. This way there will always be one active sheet in the workbook
and the rest will be hidden. Right now, the 'main' sheet is always
active (because the command buttons are on it) and the sheet following
it would be the chosen sheet/s to be viewed.

Help!
Thanks

PS: if there is better way to do it other than using command buttons,
your advise would be appreciated.

I am no expert but a simple solution is to create a custom toolbar with all
your macro assigned with the buttons on it.

"mcm" wrote in message
...
Hi all

I'm trying to build a floating control box with several buttons on MS
Excel. Originally, I was able to build macro buttons on the first
sheet of my workbook. Let's refer to the first sheet as 'main'. The
other sheets we can call sheet1, sheet2, etc. up to sheet20.

On the 'main' worksheet I created 20 buttons corresponding to the 20
sheets. So anyone who wants to view a sheet, they will click on the
command button for that sheet to view it. However, what I really
wanted was a floating control box with all 20 command buttons present
in it. This way there will always be one active sheet in the workbook
and the rest will be hidden. Right now, the 'main' sheet is always
active (because the command buttons are on it) and the sheet following
it would be the chosen sheet/s to be viewed.

Help!
Thanks

PS: if there is better way to do it other than using command buttons,
your advise would be appreciated.

Hi

You could add a right click menu option to your workbook,
called 'Main'. Then add all the sheets 'sheet 1' 'sheet
2' etc below that, when the user click the sheet name it
takes them to the sheet.

You can easily hide the menu option when you switch to
another workbook



-----Original Message-----
Hi all

I'm trying to build a floating control box with several
buttons on MS
Excel. Originally, I was able to build macro buttons on
the first
sheet of my workbook. Let's refer to the first sheet
as 'main'. The
other sheets we can call sheet1, sheet2, etc. up to
sheet20.

On the 'main' worksheet I created 20 buttons
corresponding to the 20
sheets. So anyone who wants to view a sheet, they will
click on the
command button for that sheet to view it. However, what
I really
wanted was a floating control box with all 20 command
buttons present
in it. This way there will always be one active sheet
in the workbook
and the rest will be hidden. Right now, the 'main'
sheet is always
active (because the command buttons are on it) and the
sheet following
it would be the chosen sheet/s to be viewed.

Help!
Thanks

PS: if there is better way to do it other than using
command buttons,
your advise would be appreciated.
.

jon-jon

Sorry mate, your suggestion won't work for my purpose. The workbook
i'm building the macro on is read by 10 other people. I wanted to
build a floating control box so they would just have to click on a
button that corresponds to the sheet they want to view.

With my current macro, these control buttons are on a 'main' sheet
which remains active (unhidden) all the time. The buttons I've done
merely hides and unhides any worksheet for viewing. a floating control
box would eliminate the need for a 'main' sheet to be always active
(unhidden) as only the chosen worksheet/s will be the only ones
unhidden on screen.

Hi

I have a floating control box solution.

Go to Visual Basic Editor and create a UserForm and a button on it for
each Sheet. (Lets name the UserForm frmSelectSheet)
In each button's OnClick Event type in:

Worksheets("SheetName").Activate

or if it a Chart sheet substitute 'Worksheets' with 'Charts'

Then double-click on 'ThisWorkBook' and select its OnOpen Event and
type in:

Load frmSelectSheet

That's it!

Tom

Hi,

If I understand it correctly your control button is an embedded object to
the 'main' sheet. If you hide the 'main' sheet the button will no longer
visible. So what you want is button that will always be visible even if the
'main' sheet is hidden. Custom toolbars will work fine for this but you
have to attached this toolbars to your workbook to make it available to
other users.

If this doesn't suit your needs maybe the best way is for you to create a
userform. I can not explain it detailed to you but I wish that could give
you an idea where to start.

Pardon me if I just wasted your time I am just trying to help to the best I
can.

Goodluck!

Jon-jon

"mcm" wrote in message
...
jon-jon

Sorry mate, your suggestion won't work for my purpose. The workbook
i'm building the macro on is read by 10 other people. I wanted to
build a floating control box so they would just have to click on a
button that corresponds to the sheet they want to view.

With my current macro, these control buttons are on a 'main' sheet
which remains active (unhidden) all the time. The buttons I've done
merely hides and unhides any worksheet for viewing. a floating control
box would eliminate the need for a 'main' sheet to be always active
(unhidden) as only the chosen worksheet/s will be the only ones
unhidden on screen.

tomhaddock wrote:
*Hi

I have a floating control box solution.

Go to Visual Basic Editor and create a UserForm and a button on it
for each Sheet. (Lets name the UserForm frmSelectSheet)
In each button's OnClick Event type in:

Worksheets("SheetName").Activate

or if it a Chart sheet substitute 'Worksheets' with 'Charts'

Then double-click on 'ThisWorkBook' and select its OnOpen Event and
type in:

Load frmSelectSheet
frmSelectSheet.Show False

If you want you can prevent the floating control from being closed by
adding the following in the UserForm QueryClose Event:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

If CloseMode < 1 Then Cancel = 1

End Sub

That's it!

Tom *

I'll try this Tom and keep you posted. Cheers mate.

JON-JON wrote:
*Hi,

If I understand it correctly your control button is an embedded
object to
the 'main' sheet. If you hide the 'main' sheet the button will no
longer
visible. So what you want is button that will always be visible even
if the
'main' sheet is hidden. Custom toolbars will work fine for this but
you
have to attached this toolbars to your workbook to make it available
to
other users.

If this doesn't suit your needs maybe the best way is for you to
create a
userform. I can not explain it detailed to you but I wish that could
give
you an idea where to start.

Pardon me if I just wasted your time I am just trying to help to the
best I
can.

Goodluck!

Jon-jon
. * [/color]

Jon-jon, don't sweat it mate. Everything's cool. I appreciate your
suggestion. Who knows maybe you can help me with another vb problem in
the future.

cheers