Need Answers to Urgent Questions on Excel! Hurry, oh, please hurry!

[steverob]

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.

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.

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.

-Steve

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

 

[Harlan Grove]

(e-mail address removed) 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.