Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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" wrote in message ... 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" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Switchboard" in Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |