VBA control array

    I have a form that launches when a user wants to confirm an order from a customer. It loads a drop-down combo box with our current customer list. When the user selects a customer, a checkbox array is loaded with the items that a customer has ordered. The checkboxes confirm (or un-confirm) that the order has been processed. Everything works fine up to this point. Let's say the user selects a customer, and this customer has 7 orders. 7 checkboxes appear on the form, either checked or unchecked. The problem is: when the user selects another customer from the drop down that only has 3 orders. The 3 checkboxes appear just fine, however, the balance of the previous customer (orders 4-7) also still show on the form. How do I remove the previous checkboxes from the form and prepare for a new recordset (or new checkbox array). I have tried to run backwards through the code that creates the array using Me.Controls.Remove instead of .Add, and this is just not working. Any help would be greatly appreciated.

    Re: VBA control array


    You probably need to implement a clean-up routine that fires just before your code to populate the form with the new checkboxes


    The cleanup routine would essentially delete all the existing checkboxes...


    Post up the code that populates the form at the moment if you like, it will make it easier to explain

    Re: VBA control array


    Re: VBA control array


    Thank you for the warm welcome, Roy. I've visited and scalped this website a thousand times for useful code, but this was my first post. Also, thank you & Will for such a quick response. When I first replied with my code, it posted with a lot of stuff that wasn't relevant to the post. I've since edited it and cleaned it up a little. I hope that helps. I have tried [B][I]many[I][B] different code snippets to remove the checkboxes from the form, but none of them work. It seems as though if you Me.Controls.Add, you should be able to Me.Controls.Remove. Any suggestions?

    Re: VBA control array


    Quote from Zzyzzyth

    I'm sorry, Norie. I didn't think the 2 sites were related. Besides, I wasn't getting an answer.


    Read The Rules


    In your case, Rule 12


    Quote


    12. Do not cross-post. This is when you post the same question on other forums, newsgroups etc. This will be wasting the time of the kind volunteers as they could well be trying solve a problem that has been solved elsewhere. This is like ringing 5 different cab companies, jumping in the first that shows and not caring less about the other 4. If you have cross-posted, please at least supply the URL to the other post(s)


    FYI - ALL Excel Forums are related, in that the questions on pretty much all of them are answered by pretty much the same small group of people... even if the usernames are different,many of the same people will be the ones trying to help you.

    Re: VBA control array


    I'm sorry. I won't do that again. I was getting frustrated and just thought I would search around for another board. I didn't realize I was about to step on any toes! I know this is all volunteer work, and I understand your responses. If anyone is still willing to talk to me....

    Re: VBA control array


    Yeah, it's telling me to use Me.Controls.Remove, which I have. I must not be using the correct reference or name of the control. I've tried (I thought) of every possible combo of .Remove using the index, the class name, the checkgroup. I've tried decreasing the index by 1 and redim'ing each time... Maybe I'm overthinking it at this point.

    Re: VBA control array


    Quote from Zzyzzyth

    I guess I'm not going to get an aswer at all now. I said I was sorry.


    Mate, even geeks in the UK have to sleep sometime.... ;)

    Re: VBA control array


    Well having had the time to play with the example in the link I posted to, it seems to work fine as long as you correctly reference the controls to be removed.


    example of adding and removing


    [vba]
    Option Explicit


    Private Sub CommandButton1_Click()
    'add some checkboxes
    Dim ctl As Control
    Dim i As Integer, j As Integer


    If Len(Me.TextBox1.Text) = 0 Then
    MsgBox "You need to specify the number of checkboxes required": Exit Sub
    Else
    i = CInt(Me.TextBox1.Text)
    If i > 10 Then
    MsgBox "Maximum Checkboxes is 10": Exit Sub
    Else
    For j = 1 To i
    Set ctl = Me.Controls.Add("Forms.CheckBox.1", "Checkbox" & i, True)
    With ctl
    .Top = 15
    .Left = j * 25 + 20
    End With
    Next j
    End If
    End If
    End Sub


    Private Sub CommandButton2_Click()
    'remove ALL checkboxes
    Dim ctl As Control
    Dim i As Integer, j As Integer
    Dim strCtl As String


    For Each ctl In Me.Controls
    strCtl = ctl.Name
    If Left(ctl.Name, 8) = "Checkbox" Then
    Me.Controls.Remove strCtl
    End If
    Next ctl


    End Sub
    [/vba]


    Here's an example file

    Re: VBA control array


    Sorry about the delay guys. Just waking up here. Thanks for the example Will. It works great by itself, but not with my code. When I use the line to delete the controls, I get runtime '444' , "Could not delete the controls. This method cannot be used in this context". Is it because of the array, rather than looping through Me.Controls?

    Re: VBA control array


    Well, it was just an example.


    What I would do is to set up the delete routine as a separate routine in your userform code module i.e.


    [vba]
    Sub delete_checkboxes()
    Dim ctl As Control
    Dim i As Integer, j As Integer
    Dim strCtl As String


    For Each ctl In Me.Controls
    strCtl = ctl.Name
    If Left(ctl.Name, 8) = "Checkbox" Then
    Me.Controls.Remove strCtl
    End If
    Next ctl
    End Sub
    [/vba]


    Then, call this routine at the start of the routing that already effectively creates the new checkboxes..


    i.e.


    [vba]
    Private Sub cboCustomers_Click()
    Dim strSQL As String



    [COLOR="red"]Call delete_checkboxes [/COLOR] 'delete the existing checkboxes first


    strSQL = "SELECT DSDID, CrossRef, Discount " & _
    "FROM [ZipCIN];"

    Set db1 = DBEngine.OpenDatabase(dbDSD)
    Set rs1 = db1.OpenRecordset(strSQL, dbOpenDynaset)

    With rs1
    If Not .EOF Then
    .MoveLast 'populate the recordset
    .MoveFirst
    Do Until .EOF
    Set ctl = Me.Controls.Add("Forms.Checkbox.1")
    ctl.Top = Index * 15 + 40
    ctl.Left = 270
    ctl.Caption = !DSDID
    ReDim Preserve CheckBoxes(Index)
    Set CheckBoxes(Index).CheckGroup = ctl
    If !Verified > 0 Then
    CheckBoxes(Index).CheckGroup.Value = True
    End If
    Index = Index + 1
    .MoveNext
    Loop
    End If
    End With
    [/vba]

    Re: VBA control array


    Same thing. Runtime '444'.


    Another thing I though was interesting, was when I stepped through the code, it was not recognizing Left(ctl.Name, 8) unless I capitalized the "B" in CheckBox. That was strange. Anyway, I put it in a seperate routine, and got the same thing. It must be the array, or possibly a reference to the class module?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login