Build form dynamically?

[nodding dino]

I'm not too sure what to search for here - so apologies if it's been discussed a hundred times!

I would like to build a form to display checkboxes next to labels which will take their caption value from a series of rows in a worksheet.

These rows come from a .csv import and as such will vary in number. So (as an example) my .csv may have three rows, in which case, when the form is launched, I would like it to have 3 lables and checkboxes next to them.

I have started by counting the number of rows in the imported data and have then moved to a form which (upon activation) will start a loop for the required count. This is the point at which I have become stuck!

Can anyone give me any ideas? I guess I will need to hold the data in an array and then loop through this to populate the labels...I'm just not sure how to create the labels on the fly...

THANKS!

 

[Richie(UK)]

Hi nd,

See if the info here helps:

http://j-walk.com/ss/excel/tips/tip76.htm

 

[nodding dino]

thanks Richie...do you think this could be butchered to populate/create labels/checkboxes too?

 

[nodding dino]

sorry for dragging this one back up to the top, but having returned to the problem, I'm struggling to intergrate the code...

    Dim Ops(1 To upper) As String
  'Create an array of month names
    w = 0
    For i = 1 To upper
        Ops(i) = datearray(w)
        w = w + 1
    Next i
    UserChoice = GetOption(Ops, 1, "Select a Journey Code")
    MsgBox UserChoice
  'End Sub
  End If

When I execute this to test, I am getting the following error "Constant expression required" - it is spitting this error out on the

Dim Ops(1 To upper) As String

line...

am I being daft?

 

[nodding dino]

more info might help...

where upper is declared like this

Dim upper

and is populated like this

upper = UBound(datearray) + 1

 

[Norie]

You can't dim an array based on a variable.

You could ReDim an array based on a variable.

Can we see the rest of the code and can you explain what you are trying to do?

By the way rather than creating controls on the fly why not use a listbox with the ListStyle property set to fmListStyleOption.

This listbox could be populated when the form is initialized.

 

[nodding dino]

thanks for the reply Norie, I'll try explaining again!

First off...the code in it's entirety...

Private Sub btnjourney_Click()
    
Dim datearray()
Dim i, w, x, y, z
Dim getorderdate
Dim mydate
Dim numrows
Dim rng
Dim exists
Dim upper

getorderdate = frmgetcode.framedate.cmbday.Value & "/" & frmgetcode.framedate.cmbmonth.Value & "/" & frmgetcode.framedate.txtyear.Value
    If Not IsDate(getorderdate) Then
        mydate = 0
        MsgBox "Only dates are allowed, Please try again"
        frmgetcode.framedate.cmbday.Value = ""
        frmgetcode.framedate.cmbmonth.Value = ""
        frmgetcode.framedate.txtyear.Value = ""
    Else:
        mydate = 1
    End If
    
 numrows = Range("A7").CurrentRegion.Rows.Count + 6
 
 exists = 0
 y = 0
    For x = 7 To numrows
        Set rng = Range("A" & x)
        If rng.Text = getorderdate Then
            exists = 1
            ReDim Preserve datearray(y)
            datearray(y) = Range("A" & x).Offset(0, 6).Text
            y = y + 1
        End If
    Next x
    
  If exists = 0 Then
    MsgBox "There are no Journeys entered against the date you selected"
  Else
    upper = UBound(datearray) + 1
    Dim Ops(1 To upper) As String
    w = 0
    For i = 1 To upper
        Ops(i) = datearray(w)
        w = w + 1
    Next i
    UserChoice = GetOption(Ops, 1, "Select a Journey Code")
    MsgBox UserChoice
  End If
 'upper = UBound(datearray)
 'Range("A15").Select
 'For z = 0 To upper
 '   ActiveCell.Value = datearray(z)
 '   ActiveCell.Offset(1, 0).Select
 'Next z
    
    
End Sub

The problem is this...(slightly different to the original post but similar enough to resurrect this thread!)

I have a spreadsheet with n rows.
Each row holds a date and a journey reference.
A button on the spreadsheet calls a userform which allows the user to enter a date using 2 combo boxes and a textbox (day,month,year).
When they hit the OK the code above is called.

I would like to run through all the lines in the spreadsheet and for each one where the date matches that provided in the userform I would like to hold the journey reference in an array. (There may be moe than one matching line)

(all good so far!)

Now, I would like to build userform that lists the array elements (journey refes) as option buttons where the user can select which one they are interested in.

RichieUK suggested using the extra module, but if you can suggest a better (read simpler ) way then please let me know...! I am not overly experienced with form controls, but if you can point me in the right direction I'll give it a go!

I had just sussed where I was going wrong when I noticed you had replied!

Thanks for you help (again) Norie!

 

[Norie]

Like I said why not use a listbox?

You could populate it as required when the button is clicked.

Why are you using 2 arrays, which as far as I can see are exactly the same apart from the name?

What does GetOptions do?

By the way why not use a combobox for the year as well?

 

[nodding dino]

as far as the two arrays go, I had been trying to integrate this and had just been tweaking the code...I've given this up as a bad job though as even when I specify a constant I get an error (the GetOptions calls the code from the link above)

There is no reason why I couldn't use a combo for the year...I had just hijacked a form that already existed to have a play...!

Am taking a proper look at using a listbox now...Thanks for your help norie!