"Switchboard" in Excel

[Chaplain Doug]

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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

[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.

--
Regards,
Tom Ogilvy

"Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
news:D53FA1B9-6458-4742-8B50-99F4847E46C0@microsoft.com...
> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

[Jim Thomlinson]

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

"Chaplain Doug" wrote:

> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

[gocush]

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

"Chaplain Doug" wrote:

> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

[Chaplain Doug]

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.

"Jim Thomlinson" wrote:

> 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
>
> "Chaplain Doug" wrote:
>
> > 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.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org

[Chaplain Doug]

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.

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
> news:D53FA1B9-6458-4742-8B50-99F4847E46C0@microsoft.com...
> > 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.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org
>
>
>

[Chaplain Doug]

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.

"gocush" wrote:

> 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
>
> "Chaplain Doug" wrote:
>
> > 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.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org

[Jim Thomlinson]

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

"Chaplain Doug" wrote:

> 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.
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > "Chaplain Doug" wrote:
> >
> > > 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.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org

[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...FormsPartI.asp
Part II
http://msdn.microsoft.com/library/en...ormsPartII.asp



--
Regards,
Tom Ogilvy

"Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
news:6E0E97BC-040D-436E-94D9-A5B935914C28@microsoft.com...
> 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.
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in
message
> > news:D53FA1B9-6458-4742-8B50-99F4847E46C0@microsoft.com...
> > > 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.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org
> >
> >
> >

[gocush]

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




"Chaplain Doug" wrote:

> 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.
>
> "gocush" wrote:
>
> > 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
> >
> > "Chaplain Doug" wrote:
> >
> > > 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.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org

[Chaplain Doug]

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.

"Jim Thomlinson" wrote:

> 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
>
> "Chaplain Doug" wrote:
>
> > 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.
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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
> > >
> > > "Chaplain Doug" wrote:
> > >
> > > > 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.
> > > > --
> > > > Dr. Doug Pruiett
> > > > Good News Jail & Prison Ministry
> > > > www.goodnewsjail.org

[Chaplain Doug]

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

"Tom Ogilvy" wrote:

> 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...FormsPartI.asp
> Part II
> http://msdn.microsoft.com/library/en...ormsPartII.asp
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in message
> news:6E0E97BC-040D-436E-94D9-A5B935914C28@microsoft.com...
> > 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.
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote in
> message
> > > news:D53FA1B9-6458-4742-8B50-99F4847E46C0@microsoft.com...
> > > > 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.
> > > > --
> > > > Dr. Doug Pruiett
> > > > Good News Jail & Prison Ministry
> > > > www.goodnewsjail.org
> > >
> > >
> > >
>
>
>

[Chaplain Doug]

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

"gocush" wrote:

> 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
>
>
>
>
> "Chaplain Doug" wrote:
>
> > 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.
> >
> > "gocush" wrote:
> >
> > > 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
> > >
> > > "Chaplain Doug" wrote:
> > >
> > > > 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.
> > > > --
> > > > Dr. Doug Pruiett
> > > > Good News Jail & Prison Ministry
> > > > www.goodnewsjail.org