Loop through Comboboxes VBA -

i stuck @ can not find correct syntac for. code working on looks this:

public function initializecolors() 'initialize colors setting them of webpage: 'http://dmcritchie.mvps.org/excel/colors.htm 'example. black first color '0(since black first index selection) + 1 = 1 color(0) = 1    'black color(1) = 3    'red color(2) = 4    'green color(3) = 5    'blue color(4) = 7    'magenta color(5) = 9    'brown  'study each combobox of colors , if color 'is selected, initialize directly indexvariable select case userform2.combobox7.listindex     case 0         colorcomboboxindex(0) = color(0)     case 1         colorcomboboxindex(0) = color(1)     case 2         colorcomboboxindex(0) = color(2)     case 3         colorcomboboxindex(0) = color(3)     case 4         colorcomboboxindex(0) = color(4)     case 5         colorcomboboxindex(0) = color(5) end select  select case userform2.combobox8.listindex     case 0         colorcomboboxindex(1) = color(0)     case 1         colorcomboboxindex(1) = color(1)     case 2         colorcomboboxindex(1) = color(2)     case 3         colorcomboboxindex(1) = color(3)     case 4         colorcomboboxindex(1) = color(4)     case 5         colorcomboboxindex(1) = color(5) end select  select case userform2.combobox9.listindex     case 0         colorcomboboxindex(2) = color(0)     case 1         colorcomboboxindex(2) = color(1)     case 2         colorcomboboxindex(2) = color(2)     case 3         colorcomboboxindex(2) = color(3)     case 4         colorcomboboxindex(2) = color(4)     case 5         colorcomboboxindex(2) = color(5) end select     select case userform2.combobox10.listindex     case 0         colorcomboboxindex(3) = color(0)     case 1         colorcomboboxindex(3) = color(1)     case 2         colorcomboboxindex(3) = color(2)     case 3         colorcomboboxindex(3) = color(3)     case 4         colorcomboboxindex(3) = color(4)     case 5         colorcomboboxindex(3) = color(5) end select  select case userform2.combobox11.listindex     case 0         colorcomboboxindex(4) = color(0)     case 1         colorcomboboxindex(4) = color(1)     case 2         colorcomboboxindex(4) = color(2)     case 3         colorcomboboxindex(4) = color(3)     case 4         colorcomboboxindex(4) = color(4)     case 5         colorcomboboxindex(4) = color(5) end select  select case userform2.combobox12.listindex     case 0         colorcomboboxindex(5) = color(0)     case 1         colorcomboboxindex(5) = color(1)     case 2         colorcomboboxindex(5) = color(2)     case 3         colorcomboboxindex(5) = color(3)     case 4         colorcomboboxindex(5) = color(4)     case 5         colorcomboboxindex(5) = color(5) end select end function 

these values applied code below sets value of color

 ...for j = 7 12         if colorcomboboxindex(j) > -1         ...               .border.colorindex = colorcomboboxindex(j)         .... 

it functions this. not need debug. syntax of how make first code block loop. in art of:

for j = 0 5    select case userform2.combobox(j).listindex   

i know syntax not exist , have tried luck me.controls had compile errors.

any kind of appriciated.
kind regards,
mario

p.s color() , colocombobox() variables declared public somewhere else

the syntax looking is

me.controls.item("combobox" & j) 

but leaving user controls @ random default names bad style. give them appropriate names right start, references them in vba code can meaningful.

here more refined approach: in userform contains color combo boxes, edit properties , name them colorbox_0 through colorbox_4. then, in code userform, add this:

option explicit  private const color_box_count integer = 4  ' that's 5, count 0  private sub userform_initialize()     dim cmb combobox, integer      ' prepare color combo boxes actual rgb color codes , names     = 0 color_box_count         set cmb = me.controls.item("colorbox_" & i)         cmb.clear         cmb.columncount = 2         cmb.columnheads = false         cmb.columnwidths = "0;"         cmb.additem "000000": cmb.column(1, 0) = "black"         cmb.additem "ff0000": cmb.column(1, 1) = "red"         cmb.additem "00ff00": cmb.column(1, 2) = "green"         cmb.additem "0000ff": cmb.column(1, 3) = "blue"         cmb.additem "ff00ff": cmb.column(1, 4) = "magenta"         cmb.additem "7c2927": cmb.column(1, 5) = "brown"         cmb.matchrequired = true         cmb.value = cmb.list(0)  ' pre-select first entry     next end sub  public function getselectedcolors() long()     dim cmb combobox, integer     dim result(color_box_count) long      = 0 color_box_count         set cmb = me.controls.item("colorbox_" & i)         if isnull(cmb.value)           result(i) = -1         else           result(i) = getcolor(cmb.value)         end if     next      getselectedcolors = result end function 

note how getselectedcolors() returns array of colors.

there helper function convert rgb color codes number (colors long values in vba, if use color in way, setting backcolor of control, can use value straight-away):

function getcolor(rgb variant) long     if len(rgb) = 6 , isnumeric("&h" & rgb)         getcolor = clng("&h" & right(rgb, 2) & mid(rgb, 3, 2) & left(rgb, 2))     end if end function 

with this, don't need magic constants (1 = black, 3 = red) anymore, userform bootstraps on start , global variables gone well, thing.

the convention made color value of -1 means user has not selected item in combobox. should not happen comboboxes start first entry pre-selected.

now can selected colors directly

private sub testbutton_click()     dim colors() long      colors = me.getselectedcolors      ' them' end sub 

or maybe

private sub colorbox_1_change()     colorlabel_1.backcolor = getcolor(colorbox_1.value) end sub