About Us

wrote...
I'm not looking for info on *how* to do the 3 things listed below, I'm
just wondering if they are *possible*. Are they?

1. Add a new top-level menu to Excel when a user opens an Excel
template of a given type.

Possible, but requires that VBA code (in or called by both
Workbook_Open and Workbook_Activate event handlers) be in that template
to construct such a menu and that users enable VBA in order for those
event handlers to run.

2. From that top-level menu, have one of the items in it open up a
dialog box whereon a user would enter data into a number of fields.

Possible. Menu items call macros, and macros can display dialogs.

3. After user enters data on the dialog box and submits it, have Excel
connect to an Access database to retrieve data based on the values the
user entered, data that would then be added to a range of cells in the
open spreadsheet.

Possible. The macro that displays the dialog would need to store values
entered in the dialog, then use those entered values to construct SQL
query strings that can be used in ADO or DAO calls to pull other data
from Access.

PS: If you want to tell me exactly *how* to do this, then please have
at it.

You haven't exactly provided much in the way of details. So here's a
very simplified sample. Put the following into a newly inserted general
VBA module.

'---- begin VBA code ----
Option Explicit

Sub foo()
Dim mb As CommandBar, mbc As CommandBarControl

Set mb = Application.CommandBars("Worksheet Menu Bar")

Set mbc = mb.Controls.Add(msoControlPopup, , , 8, True)
mbc.Caption = "foo&bar"
mbc.TooltipText = "fubar the menu!"

Set mb = mbc.CommandBar

Set mbc = mb.Controls.Add(msoControlButton, , , 1, True)
mbc.Caption = "&First do one thing"
mbc.OnAction = "FirstMacro"

Set mbc = mb.Controls.Add(msoControlButton, , , 2, True)
mbc.Caption = "The&n do another"
mbc.OnAction = "AnotherMacro"

Set mbc = mb.Controls.Add(msoControlButton, , , 3, True)
mbc.Caption = "&Kill this stupid menu"
mbc.OnAction = "KillMenu"

End Sub

Sub FirstMacro()
MsgBox "First"
End Sub

Sub AnotherMacro()
MsgBox "Not First"
End Sub

Sub KillMenu()
Application.CommandBars("Worksheet Menu Bar").Reset
End Sub
'---- end VBA code ----

Call foo from the Workbook_Activate event handler and call KillMenu
from the Workbook_Deactivate event handler in the ThisWorkbook class
module.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Display form from an VB application in Excel	kuhni	Excel Discussion (Misc queries)	0	August 10th 05 06:04 PM
EXCEL Hyperlink to Charts Questions.. URGENT.. =(	kaci	Excel Discussion (Misc queries)	4	August 1st 05 02:34 PM
Typical excel interviewing questions	Interview	Excel Discussion (Misc queries)	3	July 14th 05 11:10 PM
excel 2003 questions	Wiley C	Excel Discussion (Misc queries)	0	May 19th 05 11:19 PM
Help, Urgent Excel Formulas are not calculating	maashoff	Excel Discussion (Misc queries)	1	May 3rd 05 12:25 AM