Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge 2 strings and read variable that is created

Posted on 2012-03-29
6
Medium Priority
?
411 Views
Last Modified: 2012-04-03
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

Open in new window


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?
0
Comment
Question by:Jofnn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 37784848
Hi, I built a sample in Excel, but it worked pretty much identically to what you have...

Rob.
ValidationExample.xlsm
0
 
LVL 3

Author Comment

by:Jofnn
ID: 37795687
Thanks Rob - yes, it does pretty much the same thing unfortunately... if it helps, the below declarations/set commands are run:

dim OOO_T_ValErr1
dim OOO_T_ValErr2
dim OOO_T_ValErr3

Open in new window


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"

Open in new window


So if it fails at a specific point, the error message that should appear - is the one assigned to the relevant variable... not the variable name!
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 1000 total points
ID: 37799326
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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Jofnn
ID: 37799527
Thanks Rob, I'll have a look at this when I get in the office.
0
 
LVL 3

Author Closing Comment

by:Jofnn
ID: 37801009
That works perfectly and does exactly as I required!

Thanks Rob!!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37803858
No problem. Thanks for the grade.

Rob.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question