Hi - interesting one (and possibly easy to solve)...
I am writing some vba for a custom outlook form - and as part of the validation process the code runs through a number of checks, ticking box's here and there... once it's checked the box's it will create a custom error message advising the user the parts they haven't done correctly.
The (cut-down) code is as follows
Sub ValidateTop_2 if (_ VAL_T_01.value and _ VAL_T_02.value and _ VAL_T_03.value and _ VAL_T_04.value and _ VAL_T_05.value and _ VAL_T_06.value and _ VAL_T_07.value _ ) then T_PASS.value = true else T_PASS.value = false OOO_T_ErrCode = "TOP SECTION ERRORS" for x = 1 to 7 set valbox = MP.Controls("VAL_T_0" & x) valerr = "OOO_T_ValErr" & x if valbox.value = false then OOO_T_ErrCode = OOO_T_ErrCode & chr(10) & valerr else end if next end if MsgBox(OOO_T_ErrCode)End Sub
In the 'for loop', this checks to see if the user has passed each step (populated via a previous sub) - if they have T_PASS is ticked. if they fail, an error message is generated with custom "you haven't completed x" strings (OOO_T_ValErr1, OOO_T_ValErr2 etc)
As it loops through, it sets the VAL_T_01/VAL_T_02 etc variable absolutely fine and reads the relevant field as it needs to. But I'm trying to get it to loop through the error message variables, but it returns as a new string...
Any ideas how to merge the two and read the specific variable it refers to?
OK, I think I see what you're doing. I think what you're looking for is the equivalent of the Eval function in VBScript. but sadly, that doesn't exist on VBA, but you can use a Dictionary object for keys and pairs. The code below should do what you're after.
Regards,
Rob.
Private Sub CommandButton1_Click() Set dctErrors = CreateObject("Scripting.Dictionary") dctErrors.Add "OOO_T_ValErr1", "Full name of mailbox owner has not been provided" dctErrors.Add "OOO_T_ValErr2", "NT Username of mailbox owner has not been provided" dctErrors.Add "OOO_T_ValErr3", "Requestor's name has not been provided" If ( _ CheckBox1.Value And _ CheckBox2.Value _ ) Then T_Pass.Value = True Else T_Pass.Value = False OOO_T_ErrCode = "TOP SECTION ERRORS" For x = 1 To 2 Set valbox = UserForm1.Controls("CheckBox" & x) valerr = "OOO_T_ValErr" & x If valbox.Value = False Then OOO_T_ErrCode = OOO_T_ErrCode & Chr(10) & dctErrors(valerr) Else End If Next End If MsgBox (OOO_T_ErrCode)End Sub
OOO_T_ValErr1 = " - Full name of mailbox owner has not been provided"OOO_T_ValErr2 = " - NT Username of mailbox owner has not been provided"OOO_T_ValErr3 = " - Requestor's name has not been provided"
Regards,
Rob.
Open in new window