Deleting a textbox control at runtime

G

Grant Williams

Hi folks

I have a form with a combo. After the combo is updated,
the event goes off and retrieves a list of values that I
used to create several textboxes at runtime. The number
of textboxes created varies depending on the selection
made by the combo.

However, when I go to change the combo selection, I need
to remove all the textboxes I've created and regenerate a
new list of textboxes whether that be more or less than
the previous selection.

When I try and remove the textboxes tho, I get a "Runtime
Error: '444' Could not delete the controls. This method
cant be used in this context.

Any info is much appreciated. (below is the code)

'*********************************************************
Sub ClearTemporaryControls()
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.multiPageKPIs.Pages.item(1)
For i = 0 To objPage.Controls.Count - 1
Set ctrl = objPage.Controls.item(i)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
objPage.Controls.Remove ctrl.Name
End If
Next i

End Sub
'*********************************************************
 
B

Bob Phillips

As an alternative to deleting them, why don't you just hide and unhide them
as required?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

The only change I made to your code was in the set ctrl line. Since the
control index shifts down each time you delete a control, I always removed
the zeroeth control (if you have controls that don't meet the criteria, you
will have to adjust for that). Anyway, it worked fine for me in this simple
userform with xl2000

Sub AddTemporaryControls()
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.multiPageKPIs.Pages.Item(1)
tp = Me.multiPageKPIs.Top
lf = Me.multiPageKPIs.Left
For i = 1 To 3
Set ctrl = objPage.Controls.Add("Forms.TextBox.1", SEARCH_STRING & i,
True)
ctrl.Top = tp + 5 + (i - 1) * 20
ctrl.Left = lf + 5
ctrl.Height = 15
ctrl.Width = 25
Next i

End Sub

Sub ClearTemporaryControls()
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.multiPageKPIs.Pages.Item(1)
For i = 0 To objPage.Controls.Count - 1
' change made here
Set ctrl = objPage.Controls.Item(0)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
objPage.Controls.Remove ctrl.Name
End If
Next i

End Sub

Private Sub CommandButton1_Click()
AddTemporaryControls
End Sub

Private Sub CommandButton2_Click()
ClearTemporaryControls
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

All code was in the Userform code module.
 
G

Grant Williams

Thanks for that fellas

Actually the problem may be a little more involved.
Heres what happens exactly.

The combo box has a list of stored procedures (from an SQL Server 2000
database). When I select a stored procedure, I would like to show each
parameters name and value in a textbox (1 for name and 1 for value).
Each parameters name and value textbox forms a row (kind of) and for the
next parameter(s), I increment the Top property of the name and value
textbox so that the new controls sit below the previous ones. (do you
get that - they kind of look like a grid in the end)

If the stored procedure has 10 parameters, I would generate 10 rows (x 2
textboxes) giving a total of 20 textboxes.

However, if I've made the wrong selection in the combo and re-chose a
new stored procedure, I need to remove these 20 textboxes that I've
created (at runtime) and recreate a set of new textboxes which will
depend on the number of parameters that the newly selected stored
procedure contains. (possibly 0).

This is why i've created the sub ClearTemporaryControls which can be
called immediately after selecting the stored procedure but before the
new controls are created.

One thing you did say Tom was that you always deleted the zeroth indexed
control. Is this a rule when deleting controls at runtime or just good
practice. Also, if you do delete a control, is the index count
decremented also and do all controls after the deleted control have
their indexes decremented likewise?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Bob Phillips

Even with this 'extra' complexity. does Tom's solution not work okay? His
point about deleting the zeroeth instance is not a best practice but just a
technique that can work well as when a control is deleted, all the others
will shunt down, so the first instance becomes the zeroeth, the second
becomes first, etc. This allows to loop through the controls with a For Each
construct. If you didn't use this technique, you would need to loop
backwards. The problem arises if there are other controls on the page,
because as soon as one of these becomes the zeroeth instance, it never gets
past it (it is always the zeroeth instance from then on).

Hiding/unhding would work just as well here as long as you know the maximum
number. Here is some code with examples of 3 and 5 boxes

Sub HideTextBoxes()
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.MultiPageKPIs.Pages.Item(1)
For i = 0 To objPage.Controls.Count - 1
' change made here
Set ctrl = objPage.Controls.Item(i)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
ctrl.Visible = False
End If
Next i

End Sub

Sub ShowTextBoxes(num As Long)
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.MultiPageKPIs.Pages.Item(1)
For i = 0 To objPage.Controls.Count - 1
' change made here
Set ctrl = objPage.Controls.Item(i)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
If CLng(Mid(ctrl.Name, 14, 99)) <= num Then
ctrl.Visible = True
End If
End If
Next i

End Sub


Private Sub CommandButton1_Click()
HideTextBoxes
ShowTextBoxes 3
End Sub

Private Sub CommandButton2_Click()
HideTextBoxes
ShowTextBoxes 5
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Grant Williams

Bob/Tom et al
thanks very much for that. Actually, in the end, I ended up hiding the
previously created controls. What I did was give the current array of
controls the name of the current Stored Procedure like:
Set objLabelParamName = objPage.Controls.Add("Forms.Label.1", "TMP_" &
strSprocName & "_NAME_" & i, Visible)

where strSprocName was defined as the value from combo. This was the
perfect solution as all other controls are removed when the form is
closed anyway as controls created at runtime are only temporary.

I appreciate the time and advice fellas. Hoping I can contribute
somehow in the future to the group.

Grant Williams


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Bob Phillips

Wise move IMO, it gives you more control and flexibility.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please HLEP: whats wrong with my code!? Adding textbox as runtime 3
Can I detect what Control the cursor is at in a form? 6
Validate Text in Textbox to Ensure Text is a Valid Price Format 1
Control Selection & Resizing at runtime 6
Need help developing a Excel VBA Macro to Vaildate data entered intoa textbox 5
Adding controls to a userform at runtime 3
Dynamic userform at runtime 1
RunTime Error 3070 8

Top