Please help with menu bar

[ruslannurijev]

Hi there Gurus!

I have surfed the net searching how to add Menu like "File", "Tools", "View" etc.
But all i have found is how to make a tools bar for the developer to use.

Mey be anybody can help me with that. Where to start from?

What contril type is it and where to declare it.

Thanks in advance!

Ruslan.

[westconn1]

the menu is commandbar

there are examples in the tutorial for adding commandbars and commandbar buttons etc

Dim cb As CommandBar
For Each cb In Application.CommandBars
    Debug.Print cb.Name
Next

[RobDog888]

Check my section on "Menu's and Toolbars"

http://www.vbforums.com/showthread.php?t=358585

[Siddharth Rout]

Hi

Do you want to add menus to Excel Toolbar/Commandbar or a Userform?

If it is Toolbar/Commandbar then follow Rob's Tutorial, else if it is the latter then follow the link mentioned below...

http://www.vbforums.com/showthread.php?t=531789

[RobDog888]

For UserForm I have one too just a bit farther down my FAQ
http://www.vbforums.com/showthread.php?t=402050

[Siddharth Rout]

@ruslannurijev: Now you have 3 options Take your pick...

One for the Worksheet menu Bar
One for the Userform menu which imitates the Worksheet menu Bar in Userform
One for the Context Menu in the Userform

[RobDog888]

The VB Forums Search feature is better then Google

[Siddharth Rout]

Absolutely Rob. I am with you on that. The VBF has a dearth of knowledge hidden it it's threads...

[ruslannurijev]

Thank all of You guys/ladies!

Sorry for my late reply (was out of town).

I want to add menu on a user form.
I have tried to do some macros, but without experience i failed.
I don't even know where to type code or where to declare my new menu
I agree with you that VBF is better than GOOGLE search, but surfing VB forum did not give expected result, it seems that i was looking in wrong place.
I haven't yet checked the links that you guys posted here, so i am going to do that right now. Meanwhile you could advice me in creating menus and sub menus.

Best regards,
Ruslan.

[Siddharth Rout]

Meanwhile you could advice me in creating menus and sub menus.

Ruslan... But we already did....

Take your time and go through every links that Rob and I gave. Understand then and then try your code...

It's like you need to understand the recipe of "how to make a Pizza". We have given you recipes for different pizza types. Simply select the one that you want and understand it's recipe and then try it... If you get stuck simply ask

[RobDog888]

If you get stuck, please post up your questions and concerned code.

[ruslannurijev]

Hi there!

I feel so stupid because i ckecked your links and i still can't get it!!!

In my FormLoad event i suppose to create a variable that refers to CommandBars collection, like:

Dim myCBar as CommandBars
Set myCBar = CommandBars.Add("Menu1", msoBarBottom)

then i make it visible:

myCBar.Visible = True

Well, after that my App gives errors, like:
"Type mismatch" or other erorrs

Really guys, i think i get things wrong about CommandBar(s) collection.

My logic is that first of all i make an object of CommandBar(s), then i make menu items on it like "File" or "Edit".

At the moment i can't figure it out how should i do it.
What does With block do?
What does For each block do?

If you guys, could just paste here sample code of doing menu like "File".
Then i would study your example and maybe this would help me understand the logic of creating menus on UserForms/Application forms.

Thank You in advance,
Ruslan.

[Siddharth Rout]

Ruslan

Which type of menu do you want to make?

The Worksheet menu Bar OR
The Userform menu which imitates the Worksheet menu Bar in Userform OR
The Context Menu in the Userform (which you get when you right click)

[ruslannurijev]

The Userform menu which imitates the Worksheet menu Bar in Userform
not the context menu

Ruslan

[Siddharth Rout]

ok... What are the options that you want in the menu...

[ruslannurijev]

1 Menu "Options", that has submenus "Location" and "Group".
2 Menu "Export", that has submenu "Export document".

It must a drop-down list like in Internet Explorer "File" etc.


Thanks,

Ruslan.

[Siddharth Rout]

Fine... Give me sometime. I am in the office at the moment. I will post a sample by the end of today

[ruslannurijev]

tanks man

Ruslan

[Siddharth Rout]

Hi Ruslan

I have made a basic change to show you how it works...

You will have to actually understand the code and see how it works...

Also I have attached a sample file that I created (A new logic which I just dreamed of )... It is not perfect but will give you an idea of an alternate solution...

[ruslannurijev]

Hey, Thanks KoolSid, i will study that code.
But actually i needed that code for Access 2000.
If your code is the same for Access then no problem, i will check it.

Thanks a lot!

PS!
if i have any questions, can i post em here?

Best regards,
Ruslan.

[Siddharth Rout]

No the 1st one is not for Access

It is for Excel... i.e why you need to specify very clearly at the beginning of the post what application you are working with... But then I guess I am equally at fault that I didn't ask you....

Try and experiment with the 2nd idea for Access...

[ruslannurijev]

Ok, i'll try!

Thanks

[ruslannurijev]

Hi there!

I have figured out the logic of creating menus(commandbar) for user forms in Access2000 but it still not working.

my code follows:

Private Sub Form_Load()
Dim myMenu As CommandBar
Dim myFileMenu As CommandBarButton

Set myMenu = CommandBars.Add("myMenuName", msoBarFloating)

Set myFileMenu = myMenu.Controls.Add(msoControlButton)
With myFileMenu
    .Caption = "File"
    .Style = msoButtonCaption
End With

myMenu.Visible = True
End Sub

i get an error: "Type mismatch"
and "Invalid procedure call or argument"
both these errors i get on this line:

Set myMenu = CommandBars.Add("myMenuName", msoBarFloating)

Any suggestions?

Regards,
Ruslan.

[Siddharth Rout]

Hi I don't have access at the moment in Office. Will check this later on in the evening

[ruslannurijev]

Hi there guys!

Thanks for your help, but i was not just sitting and waiting for you to write me correct code.
I managed to get my commandbar working.
The problem was that if you once create commandbar "MyCommandBar" and run it, it is ok. But if you run it again then you get a "Invalid procedure call or argument" error. This is because command bar property name "MyCommandBar" already exists after first boot, and when you run app twice then code tries to create already created commandbar "MyCommandBar" and gives error.
All you have to do is delete commandbar "MyCommandBar" every time app starts.

Application.CommandBars("MyCommandBar").Delete

place this code before Set block.

So, i got my code working like this:

Dim myMenuBar As CommandBar

Application.CommandBars("MyCommandBar").Delete

Set myMenuBar = CommandBars.Add(Name:="MyCommandBar", Position:=msoBarTop)

myMenuBar.Visible = True

I quite pleased with that, but 1 problem still bothers me...
This command bar appears among other application menus, HOW CAN I GET THIS COMMAND BAR TO MY USERFORM?!?

Thanks in advance,
Ruslan.

[ruslannurijev]

Hi!

I read in internet that in access2000 u can't programmatically add CommandBars to UseForm. U can only add it to access application command bars, where other menus are such as File or Edit.

Is it true?

Thanks,
Ruslan.