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
Solved

Merge 2 strings and read variable that is created

Posted on 2012-03-29
6
406 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
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…

856 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