vba - Userform controlled variables within a macro -

morning guys,

i have ran small roadblock project. i'm new vba , trying best 'learn doing' cannot seem head around macro/userform interactions.

i have userform 1 textbox , 9 checkboxes. supposed show userform, allow user dictate sheet name, , (from list of 9 users) select active or not (true or false).

in main sub, have a

allocator.show 

command, may have guessed, allocator userform name.

then i've sort of been trying things don't know how right rest of userform code is;

private sub cbgo_click()  unload allocator  end sub  private sub cboxalison_click()  if me.cboxalison.value = true     alisonyn = true         else     alisonyn = false end if  end sub  private sub cboxbeverly_click()  if me.cboxbeverly.value = true     bevelyyn = true         else     bevelyyn = false end if  end sub  private sub cboxcallum_click()  if me.cboxcallum.value = true     callumyn = true         else     callumyn = false end if  end sub  private sub cboxellen_click()  if me.cboxellen.value = true     ellenyn = true         else     ellenyn = false end if  end sub  private sub cboxgeoff_click()  if me.cboxgeoff.value = true     geoffyn = true         else     geoffyn = false end if  end sub  private sub cboxjames_click()  if me.cboxjames.value = true     jamesyn = true         else     jamesyn = false end if  end sub  private sub cboxlouise_click()  if me.cboxlouise.value = true     louiseyn = true         else     louiseyn = false end if  end sub  private sub cboxmick_click()  if me.cboxmick.value = true     mickyn = true         else     mickyn = false end if  end sub  private sub cboxtammy_click()  if me.cboxtammy.value = true     tammyyn = true         else     tammyyn = false end if  end sub  private sub tbrpname_change()  end sub  private sub userform_initialize()  dim geoffyn, tammyyn, callumyn, jamesyn, mickyn, alisonyn, beverlyyn, louiseyn, ellenyn boolean dim rp_name string  me.cboxgeoff.value = true   me.cboxtammy.value = true   me.cboxcallum.value = true   me.cboxjames.value = true   me.cboxmick.value = false   me.cboxalison.value = false   me.cboxbeverly.value = false   me.cboxlouise.value = false   me.cboxellen.value = false  me.tbrpname = ""  end sub 

all of named user variables (xxxxyn) public in main module.

these variables want pull main macro true or false following user checking desired boxes, along name string, , continue running original macro.

any appreciated, seem taking myself round in circles @ moment!

ps if helps, userform looks this;

userform

cheers,

callum

you wrote "all of named user variables (xxxxyn) public in main module." see them declared in userform's sub userform_initialize, too:

private sub userform_initialize()  dim geoffyn, tammyyn, callumyn, jamesyn, mickyn, alisonyn, beverlyyn, louiseyn, ellenyn boolean dim rp_name stringn ... 

even if declared same variables public in module, userform variables hide public namsakes userform setting not "seen" in other modules

so you'd better remove userform dimming statement of "namesakes" , leave public one

moreover in such declaration statement used, every single variable not explicitly associated specific type implicitly associated variant type

so in main module should use "dimming" statement follows:

public geoffyn boolean, tammyyn boolean, callumyn boolean, jamesyn boolean, mickyn boolean, alisonyn boolean, beverlyyn boolean, louiseyn boolean, ellenyn boolean 

---

---

but should above going, nevertheless i'd recommend switch "class" approach use of dictionary object, follows

in allocator code pane place following code

option explicit  dim chkboxes() chkbx_class 'array of type "chkbx_class" define in class module  private sub userform_initialize() dim ncontrols integer, integer dim namesarray variant, cbinivalues variant  ufinit = true  namesarray = array("geoff", "tammy", "callum", "james", "mick", "alison", "beverly", "louise", "ellen") '<== set here names associated every checkbox cbinivalues = array(true, true, true, true, false, false, false, false, false) '<== set here initial values of checkboxes  ncontrols = ubound(namesarray) + 1 '<== retrieve number of checkboxes you're going consider in form redim chkboxes(1 ncontrols) chkbx_class 'redim "chkbx_class" array = 1 ncontrols      set chkboxes(i) = new chkbx_class 'initialize new instance of 'chkboxclass' class , store in array i-th position     chkboxes(i)         set .chkbox = me.controls("checkbox" & i) 'assign correct checkbox control "chkbox" property         .name = namesarray(i - 1)  ' assign name property of checkbox          .chkbox.value = cbinivalues(i - 1) 'set checkbox correct initial value          me.controls("label" & i) = .name ' set corresponding label caption           dealersdict.add .name, .chkbox.value ' fill dictionary initial pair of dealer-name/checkbox-value     end  next  me.tbrpname.text = ""  ufinit = false  end sub   private sub cbgo_click()  me.hide  end sub 

---

add "class module" project

either clicking insert-> class module in vba ide main ribbon menu

or right-clicking anywhere in vba ide project window , selecting insert -> class module in subsequent sub-menus

---

expand "class module" node in project window

if don't see project window can open clicking view-> project window in main ribbon menu, or press "ctrl+r"

---

select new class added (it should "class1" or likes) , change name "chkbx_class" in property window "name" textbox

if don't see property window can open clicking view-> property window in main ribbon menu or press "f4"

---

in class module code pane place following

option explicit  'declare class properties: associated in every instance of class. public withevents chkbox msforms.checkbox ' "chkbox" property of class of type checkbox. it's associated events public name string  ' events associated chkbox class property sub chkbox_click()     if not ufinit dealersdict.item(me.name) = me.chkbox.value ' set dictionary pair of dealer-name/checkbox-value end sub 

---

edit main sub module follows

option explicit  public dealersdict new scripting.dictionary public ufinit boolean  sub main() myval = "io" dim mykey variant  allocator.show unload allocator  each mykey in dealersdict     msgbox mykey & ": " & dealersdict(mykey) next mykey  end sub 

---

create reference microsoft scripting runtime library use dictionaries.

this done choosing tools➜references command in visual basic editor (vbe) pops dialog box in listbox find "microsoft scripting runtime" put check mark next , press ok.

---

run main sub

whenever need retrieve boolean value associated given name have use

mybool = dealersdict(name) 

where name can be:

• a string literal wanted name ("alison", "mick" , ..)

• a string variable value stores wanted name, somewhere in code may have typed:

  dim name string name = "mick"

---

such approach gives lot of flexibility, since have to:

• set names , initial boolean values in 2 arrays (namesarray , cbinivalues) in userform_initialize

• make sure have checkboxes named after "checkbox1", "checkbox2", , on have labels named after "label1", "label2", , on

• make sure "checkboxx" aligned "labelx"

• make sure namesarray , cbinivalues have same items number labels , checkboxes