Here's a shell that I keep when I want to add a custom menubar:
In a general module:
Option Explicit
Sub create_menubar()
Dim i As Long
Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant
Call remove_menubar
mac_names = Array("mac1", _
"mac2", _
"mac3")
cap_names = Array("caption 1", _
"caption 2", _
"caption 3")
tip_text = Array("tip 1", _
"tip 2", _
"tip 3")
With Application.CommandBars.Add
.Name = "Test99"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating
For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub
Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Test99").Delete
On Error GoTo 0
End Sub
Under Thisworkbook:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub
Private Sub Workbook_Open()
Call create_menubar
End Sub
====
The Mac_names, cap_names, and tip_text are set up for 3 elements. But just
delete/add from each of these and the code will loop through them (even if
there's just one) to add buttons to a temporary toolbar.
(make sure you have the same number of elements for each array.)
Malvina wrote:
Hello all,
I created a macro in excel, where my user will have to
select client name and a client id from the form. I
created my form from the VBAProject, by adding the form to
my project. Most off the code I have on the command
button on my form. When the client is selected the user
will click on the command button, and then the code does
it's functions. (this is just an explanation of the
procedure)
I am trying to create an .xla file. I would like to have
a custom button that will access my form from the vba
project. I didn't have any macros assigned for these
purposes, but I have a form that does all the functions.
All I am trying to do is, when I click on my custom button
on the menu bar, I would like my form to show in the
application (worksheet) view. I can't find any way to do
it. Any suggestions will be appreciated. This is simple,
but I got stuck here.
Thanks :)
--
Dave Peterson