Til inspiration UDF hjælpetekst
' Kopier til et modul med en UDF
' Tilføjer Hjælpetekster til UDF'er
' Køres kun engang på en UDF
' Lavet af Jan Hansen
Sub CreateUserForm()
Dim myForm As Object, NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
Dim NewLabel As MSForms.Label, NewTextBox As MSForms.TextBox
Dim X As Integer, Line As Integer
'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
'Create the User Form
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With myForm
.Properties("Caption") = "Hjælpe tekst til UDF"
.Properties("Width") = 340
.Properties("Height") = 260
End With
'Create NewLabel
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Funktions Navn:"
.Top = 6
.Left = 6
.Width = 132
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Funktions Beskrivelse:"
.Top = 30
.Left = 6
.Width = 132
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Arguments Beskrivelse:"
.Top = 72
.Left = 6
.Width = 132
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Argument 1:"
.Top = 90
.Left = 6
.Width = 72
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Argument 2:"
.Top = 90
.Left = 6 + 80
.Width = 72
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Argument 3:"
.Top = 90
.Left = 6 + 80 * 2
.Width = 72
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
Set NewLabel = myForm.Designer.Controls.Add("Forms.Label.1")
With NewLabel
.Caption = "Argument 4:"
.Top = 90
.Left = 6 + 80 * 3
.Width = 72
.Font.Size = 12
.Font.Name = "Tahoma"
.AutoSize = True
End With
'Create NewTextBox
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtFNavn"
.Top = 6
.Left = 160
.Width = 80
.Height = 18
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtFBeskriv"
.Top = 30
.Left = 160
.Width = 162
.Height = 54
.WordWrap = True
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtArg1"
.Top = 110
.Left = 6
.Width = 65
.Height = 75
.WordWrap = False
.MultiLine = True
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtArg2"
.Top = 110
.Left = 6 + 80
.Width = 65
.Height = 75
.WordWrap = False
.MultiLine = True
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtArg3"
.Top = 110
.Left = 6 + 80 * 2
.Width = 65
.Height = 75
.WordWrap = False
.MultiLine = True
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
Set NewTextBox = myForm.Designer.Controls.Add("Forms.Textbox.1")
With NewTextBox
.Name = "TxtArg4"
.Top = 110
.Left = 6 + 80 * 3
.Width = 65
.Height = 75
.WordWrap = False
.MultiLine = True
.Font.Size = 8
.Font.Name = "Tahoma"
.BorderStyle = fmBorderStyleOpaque
.SpecialEffect = fmSpecialEffectSunken
End With
'Create CommandButton Create
Set NewButton = myForm.Designer.Controls.Add("Forms.commandbutton.1")
With NewButton
.Name = "BtnCancel"
.Caption = "Annuller"
.Top = 192
.Left = 168
.Width = 72
.Height = 24
.Font.Size = 8
.Font.Name = "Tahoma"
End With
Set NewButton = myForm.Designer.Controls.Add("Forms.commandbutton.1")
With NewButton
.Name = "BtnOk"
.Caption = "Ok"
.Top = 192
.Left = 246
.Width = 72
.Height = 24
.Font.Size = 8
.Font.Name = "Tahoma"
End With
'add code for Dim Variable
myForm.CodeModule.InsertLines 2, " Dim FuncName As String"
myForm.CodeModule.InsertLines 3, " Dim FuncDesc As String"
myForm.CodeModule.InsertLines 4, " Dim Category As String"
myForm.CodeModule.InsertLines 5, " Dim ArgDesc(1 To 3) As String"
'add code for BntCancel Button
myForm.CodeModule.InsertLines 7, "Private Sub BtnCancel_Click()"
myForm.CodeModule.InsertLines 8, " Unload Me"
myForm.CodeModule.InsertLines 9, "End Sub"
'add code for BntOk Button
myForm.CodeModule.InsertLines 11, "Private Sub BtnOk_Click()"
myForm.CodeModule.InsertLines 12, " IF Not FuncName="""" then HjælpeTekstFunction"
myForm.CodeModule.InsertLines 13, " Unload Me"
myForm.CodeModule.InsertLines 14, "End Sub"
'add code for Sub HjælpeTekstFunction
myForm.CodeModule.InsertLines 16, "Sub HjælpeTekstFunction()"
myForm.CodeModule.InsertLines 17, " Category = 7"
myForm.CodeModule.InsertLines 18, " Application.MacroOptions Macro:=FuncName,Description:=FuncDesc,Category:=Category,ArgumentDescriptions:=ArgDesc"
myForm.CodeModule.InsertLines 19, "End Sub"
'add code for TextBox's
myForm.CodeModule.InsertLines 21, "Private Sub TxtArg1_Change()"
myForm.CodeModule.InsertLines 22, " ArgDesc(1) = TxtArg1"
myForm.CodeModule.InsertLines 23, "End Sub"
myForm.CodeModule.InsertLines 25, "Private Sub TxtArg2_Change()"
myForm.CodeModule.InsertLines 26, " ArgDesc(2) = TxtArg2"
myForm.CodeModule.InsertLines 27, "End Sub"
myForm.CodeModule.InsertLines 28, "Private Sub TxtArg3_Change()"
myForm.CodeModule.InsertLines 29, " ArgDesc(3) = TxtArg3"
myForm.CodeModule.InsertLines 30, "End Sub"
myForm.CodeModule.InsertLines 32, "Private Sub TxtArg4_Change()"
myForm.CodeModule.InsertLines 33, " ArgDesc(4) = TxtArg4"
myForm.CodeModule.InsertLines 34, "End Sub"
myForm.CodeModule.InsertLines 36, "Private Sub TxtFBeskriv_Change()"
myForm.CodeModule.InsertLines 37, " FuncDesc = TxtFBeskriv.Value"
myForm.CodeModule.InsertLines 38, "End Sub"
myForm.CodeModule.InsertLines 40, "Private Sub TxtFNavn_Change()"
myForm.CodeModule.InsertLines 41, " FuncName = TxtFNavn.Value"
myForm.CodeModule.InsertLines 42, "End Sub"
'Show the form
VBA.UserForms.Add(myForm.Name).Show
'Delete the form (Optional)
ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub
Laver en Userform til indtastning af Navnet på din UDF + Hjælpetekster
Jan