How to set variable with index

[SoulPrisoner]

Hello everyone,

I´m trying to set variable with index, but kinda dunno how and its not working the way I´m trying so, if anyone has an idea how to do it, would be awesome if you could share. My code goes like this:

Public style As String
Public u As Long
Public choise As String
Public order As Long


Private Sub order_cmd_1_Click()

Dim form As String
Dim s As Control
   
For Each s In Me.Controls
    If TypeName(s) = "OptionButton" Then
        If s = True Then
            form = s.Caption
        End If
    End If
Next s

For u = 0 To frm_selection.selection_lbx_1.ListCount - 1
    If frm_selection.selection_lbx_1.Selected(u) Then
        choise(u) = frm_selection.selection_lbx_1.List(u)
        order(u) = frm_order.order_cbx_1.Value
        style(u) = form
    End If
Next u

End Sub

and i need choise, order and style to be indexed due to user´s choise, because than there will be more code running dependant on the number of user´s choise....

to give you more idea what I´m trying to do is, lets say user chooses 4 items, than, he decides the format and order for the 1st choise and therefore i need that choise to be saved to variable with index 1 (because it was the first one) than he skips lets say 2nd and goes straight for 3rd and thefore those variables has to have index 3 and if he realizes and goes to 2nd it goes with 2....etc. i think at this moment u should have the idea what im trying to do here.

this one should be the last issue within my app, than i think i´ll be able to finalize it and hand it over....:-D

Best regards

Soul

[Andy Pope]

you need to change the declaration of the variables so you can use them as arrays

Public style() As String
Public choise() As String
Public order() As Long


Private Sub order_cmd_1_Click()

Dim form As String
Dim s As Control
Dim u As Long
   
For Each s In Me.Controls
    If TypeName(s) = "OptionButton" Then
        If s = True Then
            form = s.Caption
        End If
    End If
Next s

u = frm_selection.selection_lbx_1.ListCount - 1
ReDim choise(0 To u)
ReDim order(0 To u)
ReDim style(0 To u)

For u = 0 To frm_selection.selection_lbx_1.ListCount - 1
    If frm_selection.selection_lbx_1.Selected(u) Then
        choise(u) = frm_selection.selection_lbx_1.List(u)
        order(u) = frm_order.order_cbx_1.Value
        style(u) = form
    End If
Next u

End Sub

[SoulPrisoner]

Hi Andy,

It´s a pleasure to see an answer from you, every time I´m in a dead end regarding VBA and i start searching google I end up on some forum where you´ve solved same (or similar) problem, so I´ve used quite some tips and tricks of yours, thx a lot.

regarding my issue, I have tried what you´ve proposed but its not working, it gives me immidiate error on frm_selection (userform) initialization which states:

"compile error:

Constants, fixed-length strings, arrays, user-defined types and declare statements not allowed as public members of object modules"

I´ve tried to move those variables to below section with dim but than they dont work as desired (just tried to pu msgbox statement with all three collocated together to see if at least that gives the desired result - often when im expecting some value to be outcome i use the msgbox to see if my prediction is actualy true or not, i know i can use debug.print and see that in immidiate window but i prefer msgbox dunno why...;-)...

if it might help i can share the file so u can see the structure of the userforms etc.

EDIT: here is the file

andy sample.xls

[Andy Pope]

To avoid the compile error you can move the declartion of those variables to the Module1 code.
But I'm not sure that is really the best thing to do.

The trouble is I can not see in the project where you actual make use of the 3 arrays.

[SoulPrisoner]

nvm found what was triggering the issue (not getting the proper value) - as you suggested, i´ve moved the public declaration to module, which solved the issue with the error, but then i was getting another error (9) about subscription or whatever and i found that there was another issue with the inner variable (that "u") which has to be arranged on the fly

Private Sub order_cmd_1_Click()

Dim form As String
Dim s As Control
Dim u As Long
Dim y As Long
   
For Each s In Me.Controls
    If TypeName(s) = "OptionButton" Then
        If s = True Then
            form = s.Caption
        End If
    End If
Next s

y = frm_selection.selection_lbx_1.ListCount - 1
ReDim choise(0 To y)
ReDim order(0 To y)
ReDim style(0 To y)

For u = 0 To frm_selection.selection_lbx_1.ListCount - 1
    If frm_selection.selection_lbx_1.Selected(u) Then
        y = u
        choise(y) = frm_selection.selection_lbx_1.List(u)
        order(y) = frm_order.order_cbx_1.Value
        style(y) = form
    End If
Next u

MsgBox choise(y) & "  " & order(y) & "  " & style(y) & "  " & y

End Sub

this now gives the desired values in the msg box, and therefore I belive I´ll be able to use the variables elsewhere

yet again, you´ve said the day for me andy, thx a lot, I hope I´ll be able to finish it up now (i know there will be a lot of coding still but i believe that one wont be as complicated as this userform structure)

also if you´re interested in the complete app, I have no prob to show you after I´m finished, to give you exact idea what was the target....;-) just let me know here or via pm....;-)

once more thx a lot

best regards

Soul

EDIT: oh btw. I belive because the number given to "u" variable above those redims was solid and than few lines below it was moving from 0 to that solid value, that was to me the issue what was triggering vba to fail, cause those variables couldnt pick which one to use - if the solid one few lines above or the one from the "for to" statement....dunno if I explained my train of thoughts or made it more of a mess but i felt i should elaborate on the issue

[SoulPrisoner]

its not there yet, there are few pieces prepared but than i stopped because i realized im not able to feed the proper values, the thing is, as you may have noticed, i need to be able to export the data from the tab to the word and this whole userform structer is, to give as much freedom to the user to choose the order of the columns he wants to export and with which format....so those variables gonna be then implemented in the actual code where the exporting will happen, but for that part there is no code yet, that one will be writen when i have structured the outcome of the userforms

for me, writing the whole code without being able to follow on if it actualy works, is kinda mission impossible, i need to check on what i have done every few steps and then go on.

there will have to be some kinda of transition from the caption name to actual code for the formating since i.e. heading 1 is actualy format no. -2 etc.

for the # of column which to be exported i´m about to write some search function and as for the last part and that is the order, that part is yet to be decided

I know it may look like a complete mess, well, yap sometimes i get lost in it, but I cant do better atm...:-/