Custom Button
 

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 :)

Custom Button
 

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

Custom Button
 

And one of your macros is going to look like:

sub ShowMyForm()
myForm.show 'userform1.show '????
exit sub



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