User Form

  • Thread starter Thread starter Craig Somberg
  • Start date Start date
C

Craig Somberg

WIN2K Excel 2000
===========================

I have a VB form I created that loads and functions perfectly on the
opening of the workbook.

What I need know is a way for the user to invoke the form at will from
the menu or toolbar. How ???

Thanks !!!

Craig
 
Try this:

Sub AddToolbar()
Const cFormName = "MyForm"
Dim ctlTemp As CommandBarControl

On Error Resume Next
With CommandBars.ActiveMenuBar
Set ctlTemp = .Controls(cFormName)
If Err <> 0 Then
Err.Clear
With .Controls.Add(msoControlButton, , ,
..Controls("&Help").Index, True)
.Style = msoButtonCaption
.Caption = cFormName
.OnAction = "RunMyForm"
End With
End If
End With
End Sub

Sub RunMyForm()
MsgBox "ToDo: Running Code Here"
End Sub
 
Create a button on toolbar or on the sheet itself, and add code
"UserForm.Show" substituting the name of your form for UserForm
 
I have these routines setup and I keep getting the error:

The macro 'NameOfWorkBook.xls!ShowExtractorForm' cannot be found.

Any clues ?

This code is in the WorkBook, not a module or worksheet.

Thanks.

Craig


Private Sub Add_E_ToolBar()

Dim cb As CommandBar
Dim mnu As CommandBarControl
Dim iIndex As Integer

Set cb = Application.CommandBars("Worksheet Menu Bar")

' does the menu item exist already?
On Error Resume Next
iIndex = cb.Controls("Craig").Index
If Err <> 0 Then
Err.Clear
Else
Set cb = Nothing
Exit Sub
End If

' if we are here we need to create everything
iIndex = cb.Controls("Help").Index
Set mnu = cb.Controls.Add(Type:=msoControlPopup, Before:=iIndex)

With mnu
.Caption = "Craig"
.OnAction = "ShowExtractorForm"
End With

Set cb = Nothing

End Sub


Private Sub ShowExtractorForm()

frmExtractData.Show

End Sub
 
Back
Top