User Form

[ermis1975]

I have 3-4 modules and I want to make a user form with buttons to press and activate. I can make the user form but how I can make it to run?

[blackie42]

You can write code in the _click event or call another macro e.g

Private Sub CommandButton3_Click()
clearform2
End Sub

[mikerickson]

Are you looking for the syntax
Userform1.Show

[ermis1975]

Thanks but how I can call run the program?.. Like makro in excel?
I want to open the file (user form) imediately from my pc .

[mikerickson]

The line I posted would go in an Excel macro. For example,
Sub ShowUF()
UserForm1.Show
End Sub
That macro could be called various ways, including being called from a Workbook_Open event or a Workbook_Activate event.

[Tinbendr]

This is how I do it.

This creates a Menu item next to the Help.

In ThisDocument module put the following.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
Private Sub Workbook_Open()
Call CreateMenu
End Sub


In Module1(or whereever you have the code you want to run) put the following.

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Sort TO's").Delete
End Sub
Sub CreateMenu()
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
'delete the menu if it already exists
Call DeleteMenu

'find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
'add menu to the end
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, temporary:=True)
Else
'add the menu before help
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, before:=HelpMenu.Index, _
temporary:=True)
End If
'Add Caption
NewMenu.Caption = "&Sort TO's"
'First Menu item
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Sort by &Due Date"
.FaceId = 162
.OnAction = "SortByDueDate"'This is the Sub that wil run when clicked.
End With

'Second Menu item
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Sort by &TO"
.FaceId = 590
.OnAction = "SortByTO"
End With
End Sub

This way, you can exclude an additional userform.

Hope this helps!

[ermis1975]

thanks its nice. Can I remove menu buttons from top excel window? (file edit etc.) and replace them with my own menu?! (

[Bob Phillips]

Application.CommandBars(1).Controls("File").Visible = False

[ermis1975]

Application.CommandBars(1).Controls("File").Visible = False


After running this appl the "file" etc desapears from excel! How I can reset to the "true" I have delete all menus from excel!

[Bob Phillips]

In the immediate window of the VBIDE type

Application.Commandbars(1).reset