Solved Can i make a shortcut menu with levels ? (1 Viewer)

[VBANewBie :)]

Hello guys , I can do a standard shortcut menu with commandbars method but can i make a shortcut with levels like windows using VBA ?

Thanks in Advance .

 

[theDBguy]

Hi. I think you can. If I remember correctly, look into creating a "popup" menu.

 

[VBANewBie :)]

Hi. I think you can. If I remember correctly, look into creating a "popup" menu.

I already looked , Didn’t find what i’m looking for .
Just found commandbars which i already using .

 

[theDBguy]

I already looked , Didn’t find what i’m looking for .
Just found commandbars which i already using .

Can you post a sample db? We can try to modify it to demo the concept to you.

 

[VBANewBie :)]

something is wrong with the site

 

[VBANewBie :)]

Can you post a sample db? We can try to modify it to demo the concept to you.

Thanks , Here is the sample DB with shortcut menu .

 

[theDBguy]

Thanks , Here is the sample DB with shortcut menu .

Hi. Thanks. I am having a hard time downloading your file from work, so I will have to give it another try when I get home this afternoon.

 

[VBANewBie :)]

Hi. Thanks. I am having a hard time downloading your file from work, so I will have to give it another try when I get home this afternoon.

No problem bro thanks for your help , i will be waiting .

 

[theDBguy]

No problem bro thanks for your help , i will be waiting .

Hi. Check out the attached.

 

[MajP]

I did this I while back and it actually worked real well, and always forget to incorporate. This actually works better than cascading comboxes. In this example I create a command bar with two levels Product Categories and Products. I do it dynamically when the form loads, so the command bar is the same a control like a combobox. This command bar then filters the subform.

Here is the code

Public Sub createProductCommandBar()
  Const conBarName = "cbProducts"
 
  Dim rsCat As DAO.Recordset
  Dim rsProducts As DAO.Recordset
  Dim rsOrders As DAO.Recordset
  Dim strSql As String
  Dim catCaption As String
  Dim catValue As Long
  Dim prodCaption As String
  Dim prodValue As Long
  Dim cbCat As Office.CommandBar
  Dim cbCatCtrl As Office.CommandBarControl
  Dim cbProdCtl As Office.CommandBarControl
  Dim ctl As CommandBarControl
  Dim cb As CommandBar
 
 
  Set rsCat = CurrentDb.OpenRecordset("qryCategories", dbReadOnly)
  If isCommandBar(conBarName) Then
    Application.CommandBars(conBarName).Delete
  End If
  Set cbCat = CommandBars.Add(conBarName, msoBarPopup, False, False)
 
  Do While Not rsCat.EOF
    catCaption = rsCat!CategoryName
    catValue = rsCat!CategoryID
    strSql = "Select * from qryProducts where CategoryID = " & catValue
    Set cbCatCtrl = cbCat.Controls.Add(msoControlPopup)
    cbCatCtrl.caption = catCaption
    
    Set rsProducts = CurrentDb.OpenRecordset(strSql, dbReadOnly)
    Do While Not rsProducts.EOF
      Set cbProdCtl = cbCatCtrl.Controls.Add()
      prodCaption = rsProducts!ProductName
      prodValue = rsProducts!productID
      cbProdCtl.caption = prodCaption
      cbProdCtl.Tag = prodValue
      cbProdCtl.OnAction = "subFilterOrders"
      rsProducts.MoveNext
    Loop
    rsCat.MoveNext
  Loop
End Sub

Ignoring the recordset stuff the important code is

Public Sub createProductCommandBar()
  Const conBarName = "cbProducts"
 
  Dim catCaption As String
  Dim catValue As Long
  Dim prodCaption As String
  Dim prodValue As Long

  Dim cbCat As Office.CommandBar
  Dim cbCatCtrl As Office.CommandBarControl
  Dim cbProdCtl As Office.CommandBarControl
  Dim ctl As CommandBarControl
  Dim cb As CommandBar
 
 
 'Check if a command bar exists and delete it
  If isCommandBar(conBarName) Then
    Application.CommandBars(conBarName).Delete
  End If

'Create a new command bar as a popup command bar
  Set cbCat = CommandBars.Add(conBarName, msoBarPopup, False, False)
 
'This is the confusing part.  You add a control pop up to the command bar
'So each item on the menu is basically its own control 
    Set cbCatCtrl = cbCat.Controls.Add(msoControlPopup)
  'Set the caption   
 cbCatCtrl.caption = catCaption
 
'Now add the second level
 Set cbProdCtl = cbCatCtrl.Controls.Add()
      prodCaption = rsProducts!ProductName
      prodValue = rsProducts!productID
      cbProdCtl.caption = prodCaption
      cbProdCtl.Tag = prodValue
     'Add the procedure to run when the value is selected 
     cbProdCtl.OnAction = "subFilterOrders"
    
End Sub

So when you select an item from the commandbar it has an OnAction button and the value of the command bar is that selected button.

Public Sub subFilterOrders()
  Dim cbCtl As CommandBarControl
  Dim strSql As String
  '
   Set cbCtl = CommandBars.ActionControl
    strSql = "Select * from qryOrders where ProductID = " & CInt(cbCtl.Tag)
    Forms("frmDemo").subOrders.Form.RecordSource = strSql
End Sub

 

[VBANewBie :)]

Hi. Check out the attached.

Perfect , Exactly what i’m looking for , Thank you so much .
I will try to modify it if i faced a problem i will get back to you . Thanks again

 

[VBANewBie :)]

Ignoring the recordset stuff the important code is

Thanks for your reply , You already lost me there
Could you please make a demo db to look at , It will be easier for me to understand it with example .
Thank you so much

 

[VBANewBie :)]

Hi. Check out the attached.

Hello Again i tried to modify your code as i understood and had some problems , Here is my mod

Public Function MyShortcutMenu()
'thedbguy@gmail.com
'2/14/2022

Dim cbar As CommandBar
Dim btn As CommandBarControl
Dim btn2, btn3, btn4 As CommandBarButton
Set cbar = CommandBars.Add("MyPopup", msoBarPopup)
Set btn = cbar.Controls.Add(msoControlPopup)

With btn
    .Caption = "Level 1"
    Set btn2 = .Controls.Add(msoControlButton)
    btn2.Caption = "First Command"
    btn2.OnAction = "Test"
    Set btn3 = .Controls.Add(msoControlButton)
    btn3.Caption = "Second Command"
    btn3.OnAction = "Test"
    Set btn4 = .Controls.Add(msoControlButton)
    btn3.Caption = "Third Command"
    btn3.OnAction = "Test"
End With


Set cbar = Nothing

End Function

1-The code have no "Level 2" and still get it in the shortcut menu i don’t know where it came from ? And if i changed level1 caption still get Level1 ?!
2-I added three more btn to the menu with the same principal but it don’t show up in the popup level 2 menu .
3-I tried to add onAction to btn2 but it doesn’t do anything at all .

Then i Tried this

If isCommandBar(cbar) Then
    Application.CommandBars(cbar).Delete
    End If

Also didn’t do the trick.

What i need to understand is how to add options to the second menu and how to specify an action for it .
Please be patient with me , Waiting for your reply , Thanks alot .

 

[MajP]

Could you please make a demo db to look at , It will be easier for me to understand it with example .

Oops. I am sorry, I forgot to attach the DB.

 

[VBANewBie :)]

Oops. I am sorry, I forgot to attach the DB.

Thank you so much for your help.

 

[MajP]

@VBANewBie :),
Can you do a mock up? Provide a demo form, and show where you want the menu attached. Then write down the Menu and choices you would like to see and the actions you want to have.

 

[VBANewBie :)]

Can you do a mock up? Provide a demo form, and show where you want the menu attached. Then write down the Menu and choices you would like to see and the actions you want to have.

Ok i will wait for me , Thanks

 

[VBANewBie :)]

Can you do a mock up?

Hello , Here is the demo you asked for , In the attached db you will find a form with shortcut menu contains the following :

• Open Form : I need a sub menu from this containing a list of forms in my db with captions , When i press a form name it opens.
• Open Report : The same idea as above .
• Open Query : The Same.
• You may add an option for standard command like save as and a sub menu with number of formats (For Reports for example)

The previous is just an example for me to know how the idea work and try to do my own ideas .
Thanks bro .

 

[theDBguy]

Hello , Here is the demo you asked for , In the attached db you will find a form with shortcut menu contains the following :

• Open Form : I need a sub menu from this containing a list of forms in my db with captions , When i press a form name it opens.
• Open Report : The same idea as above .
• Open Query : The Same.
• You may add an option for standard command like save as and a sub menu with number of formats (For Reports for example)

The previous is just an example for me to know how the idea work and try to do my own ideas .
Thanks bro .

Sounds like an interesting project. I'd like to see how it turns out.

 

[MajP]

Hello , Here is the demo you asked for , In the attached db you will find a form with shortcut menu contains the following :

• Open Form : I need a sub menu from this containing a list of forms in my db with captions , When i press a form name it opens.
• Open Report : The same idea as above .
• Open Query : The Same.
• You may add an option for standard command like save as and a sub menu with number of formats (For Reports for example)

The previous is just an example for me to know how the idea work and try to do my own ideas .
Thanks bro .

I will do a demo that is generic. It will build a command bar that opens forms, reports, and queries.

Form
----Form 1
--- Form 2
----Form N
Report
---- report 1
---- report N
Query
---- Query 1
---- Query 2

Should be pretty easy and reusable in any database.