"Switchboard" in Excel

[Guest]

Office Pro XP and 2003. I have written some VBA code in Outlook, Excel, etc.
that perform various functions. Is there a way to set up a "switchboard"
type interface like that of Access that would call these various macros and
routines. At present I have some command buttons in an Excel workbook that
do these things, but honestly it looks dorky. Any help would be appreciated.
Thanks and God bless.

 

[Tom Ogilvy]

You could put up a modeless userform

userform1.Show vbModeless

and have your buttons on that


If you don't need as elaborate of a layout, you could make a floating
toolbar.

 

[Guest]

Nothing Built into Excel but you can create a userform in VBA which is
essentially all that Access is doing for you Via a Wizard. If all you are
looking to do is to display different sheets and hide others that is really
easy code.

HTH

 

[Guest]

I do this quit often with the following manner:

Select Cells A1 to AA100 ( the 2nd cell needs to be "off screen" when
viewed by the largest monitor available and depends on the column width and
row height you are using)
Then select a Background Color for these cells. This should also hide the
grid lines.

I then arrange my macro buttons as desired and use the drawing tool to make
a large rectangle with the title of my application
and another smaller rectangle with the Words: Main Menu
I center these rectangles and place them near the top of the screen.

I then add vba code

Sheets("MainMenu").ScrollArea="A1"

which prevents a user from scrolling to unformatted cells

 

[Guest]

Dear Jim:

"you can create a userform in VBA"

Can you give me a code example? Also, when you say "in VBA," do you mean in
the VBA code behind an Excel sheet or some standalone VBA envirnment? Thanks.

 

[Guest]

Dear Tom:

How would I set up this "user form"? Is this something I do in the VBA
behind an Excel sheet? As you can tell, I am not at the level you are. I
need more information and detail if you can give it. Thanks and God bless.

You could put up a modeless userform

userform1.Show vbModeless

and have your buttons on that


If you don't need as elaborate of a layout, you could make a floating
toolbar.

 

[Guest]

Dear Gocush:

Sheets("MainMenu").ScrollArea="A1"

Does this mean that the main sheet needs to be named "MainMenu"? Would I
then have my sub menus on other sheets and have the Main Menu activate those
sheets when selected from the main menu? Thanks for more information.

 

[Guest]

Open the Excel Spreadsheet that you want to add a userfor too. To get into
the VBA you can either choose Tools ->Macro -> Visual Basic Editor or just
Alt+F11

You now get The VBE (Visual Basic Environment)

Are you at all familiar with Visual Basic???

The spreadsheet that you have should be open in the right hand window. (3
sheets and a ThisWorkbook Object. Right click in there and select add User
Form.

A blank for will open up that you can add buttons, check boxes, labels...

HTH

 

[Tom Ogilvy]

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.
http://j-walk.com/ss/excel/tips/tip84.htm

applicable to Excel 97 and later

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp



--
Regards,
Tom Ogilvy

Dear Tom:

How would I set up this "user form"? Is this something I do in the VBA
behind an Excel sheet? As you can tell, I am not at the level you are. I
need more information and detail if you can give it. Thanks and God bless.

 

[Guest]

I have a Sheet with the Name (in the TAB at the bottom) MainMenu
In the Visual Basic Editor I select the module for ThisWorkbook
which is just below the Sheets objects
In ThisWorkbook module I have the following code:

Private Sub Workbook_Open()
Sheets("MainMenu").Activate
End Sub

Will immediately activate my MainMenu sheet when the workbook is opened.
On this sheet is where I have the setup previously described:

Range A1:AA100 are one solid color.
I have a Title for my workbook
and a cell or drawing object with the words: "Main Menu"
Below this I have Macro Buttons
Each macro button is assgned to a macro
Some of these macros simply activate another sheet.
Others will activate a sheet then perform various tasks.
You said you already have macro with buttons, so just place these buttons
(or make new buttons) on the MainMenu sheet. You likely will have to add a
line at the start of your macros to activate a certain sheet, then continue
with your remianing code.

To get back to the MainMenu sheet you have a couple of options:
1. If a macro just performs an operation like copying/pasting something and
then you want to immediately return the use to the MainMenu, you can enter a
line of code at the end of the macro: Sheets("MainMenu").Activate before
End Sub

2. If you want the user to continue viewing/interacting with another sheet
when your macro ends, then don't reactivate the MainMenu sheet. The user can
get back to the MainMenu by clicking on that TAB at the bottom of the screen,
OR you can have a Button on each sheet that says "Return to Main Menu" The
macro behind each of these buttons would be

Sub GoToMainMenu()
Sheets("MainMenu").Activate
End Sub
Hope this helps

 

[Guest]

Thanks Jim. I am getting fairly comfortable with VBA and have used it
extensively (although hackingly) with Access. Thanks. I will try your tips.
God bless.

 

[Guest]

Thank you Tom for your time and attention. God bless.

 

[Guest]

Thank you Gocush. Will try your suggestions. God bless.