Solved

Merge 2 strings and read variable that is created

Posted on 2012-03-29
6
404 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
  • 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 250 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
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…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

912 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now