Solved

Merge 2 strings and read variable that is created

Posted on 2012-03-29
6
405 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

772 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