Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom macro and custom button | New Users to Excel | |||
Custom button for VLookups | Excel Discussion (Misc queries) | |||
2003 - 2007 custom macro and custom button restore. | Excel Discussion (Misc queries) | |||
Macro/Custom Button | Excel Discussion (Misc queries) | |||
Macro and custom Button | Excel Discussion (Misc queries) |