Prev: Macros Menu in Excel 2007
Next: Pasting Variable cell info into a macro
From: vezerid on 18 May 2010 11:12
I try to build a form dynamically from tabular data. A metadata table
is used, with the target of building a form that will browse a table,
record by record, and to also add new records (modeled after Access'
single forms). Part of the info is the data type of the field.

So I want to generate, for non-text fields specific checks. For
example, for fields specified as number, I want to ensure a number has
been entered. I want to do this in the Exit event procedure of
controls.

Everything works in the following Sub, except the .CreateEventProc

Sub LoadTestForm()
Dim codmod As VBIDE.CodeModule
Set codmod =
ThisWorkbook.VBProject.VBComponents("TestForm").CodeModule

Dim txb As MSForms.TextBox
Set metadatastart = Range("MetaDataStart")
numfields = metadatastart.CurrentRegion.Rows.Count
i = 0
While metadatastart.Offset(i, 0) <> ""
field = metadatastart.Offset(i, 0).Value
ctrlName = "txb" & Replace(field, " ", "")
topline = 10 + 20 * i
Set lbl = TestForm.Controls.Add("Forms.Label.1")
lbl.Top = topline
lbl.Left = 10
lbl.Width = 55
lbl.Caption = field & ":"

Set txb = TestForm.Controls.Add("Forms.Textbox.1")
txb.Name = ctrlName
txb.Top = topline
txb.Left = 60
If metadatastart.Offset(i, 1) = "Number" Then
txb.TextAlign = fmTextAlignRight
With codmod
LineNumber = .CreateEventProc("Exit", ctrlName) ''' *****
Problem line: Event handler is invalid
LineNumber = LineNumber + 1
.InsertLines LineNumber, "If not isnumeric (" & ctrlName &
".value) then"
LineNumber = LineNumber + 1
.InsertLines LineNumber, "MsgBox" & Chr(34) & "Field " &
field & " must be a number"
LineNumber = LineNumber + 1
.InsertLines LineNumber, ctrlName & ".setfocus"
LineNumber = LineNumber + 1
.InsertLines LineNumber, ctrlName & ".selstart = 0"
LineNumber = LineNumber + 1
.InsertLines LineNumber, ctrlName & ".sellength = len(" &
ctrlName & ")"
LineNumber = LineNumber + 1
.InsertLines LineNumber, "End If"
End With


End If

i = i + 1
Wend
TestForm.Show
End Sub

What is missing? It seems automated code generation does not work for
event procedures on dynamically created controls. I can imagine a
workaround, with preexisting controls, but I don't like this idea,
because I want to be able to handle other controls also, depending on
the field (e.g. radio buttons, listboxes).

Is what I am attempting at all possible? Search did not help. Others
seemed to have the problem but so far I could not find a thread with a
decent reply.

TIA
 | 
Pages: 1
Prev: Macros Menu in Excel 2007
Next: Pasting Variable cell info into a macro