Skip to main content

Checking selection of the CheckBox and TextBox in Idea with loop

 
Hi all,
 
In my code i am using a dialog box with number of Checkboxes and Textboxes. To verify wheter the user checked any CB or provided string with TB i use the following code respectively:
 
                             If dlg2.CheckBox1 = 1 Then
                                               UserSpecificWord(0)= "error"       
                                            Else 
                                                UserSpecificWord(0) ="@@@@@+++++@@@@@¬¬¬¬¬1"
                                           End If 
 
                                          If  dlg2.TextBox1 <> "" Then 
                                                   UserSpecificWord(9) = dlg2.TextBox1 
                                          Else 
                                                   UserSpecificWord(9) = "@@@@@+++++@@@@@¬¬¬¬¬10"
                                          End If
 
1st Question
     Is there a way to loop though the dialog box object element (CB,TB , Radio button etc.) in IDEA similar to VBA excel?
2nd Question
   I use strange kind of string '@@@@@+++++@@@@@¬¬¬¬¬10' (differing by number for each of my TB and CB) to assing any value to varaible UserSpecificWord() as use it then in the following formula in appeding field funciton:
"@CompIf(@Isini(""" & UserSpecificWord(0) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(0) & " "",@Isini(""" & UserSpecificWord(1) & """," &  UserChoice(8) & "),"" " & UserSpecificWord(1) & " "",@Isini(""" & UserSpecificWord(2) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(2) & " "",@Isini(""" & UserSpecificWord(3) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(3) & " "",@Isini(""" & UserSpecificWord(4) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(4) & " "",@Isini(""" & UserSpecificWord(5) & """," &  UserChoice(8) & "),"" " & UserSpecificWord(5) & " "",@Isini(""" & UserSpecificWord(6) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(6) & " "",@Isini(""" & UserSpecificWord(7) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(7) & " "",@Isini(""" & UserSpecificWord(8) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(8) & " "",@Isini(""" & UserSpecificWord(9) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(9) & " "",@Isini(""" & UserSpecificWord(10) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(10) & " "",@Isini(""" & UserSpecificWord(11) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(11) & " "",@Isini(""" & UserSpecificWord(12) & """," &  UserChoice(8)  & "),"" " & UserSpecificWord(12) & " "",1,"""")"
 
I have noticed that for any given block of UserSpecificWord(1,2,3) strings given if there is a break (0 value) in the sequence of UserSpecificWord up to the last UserSpecificWord(12) then the CompIF funciton will only acknowledge the initial string before the break happening and ignore the string at the end of the function, i.e:
UserSpecificWord(0) = "money"
UserSpecificWord(1) = "transfer"
UserSpecificWord(2-10) = "" (nothing)
UserSpecificWord(11) = 'litigation'
UserSpecificWord(12) = 'crime'
In the above example the words litigation and crime wont be searched for.
Any idea how to overcome that without resorting to ridiculous string assignment?
 
Many thanks
 

Brian Element Fri, 05/24/2019 - 07:48

Hi bluecox,

For question number 1 unfortunately there is no short way that I know of.  If the item name is within double quotes then you can use a loop to populate but when you are trying to extract the information you are accessing the object directly so there is no way that I know of to be able to get it through a loop.  If you figure one out please let me know as I have tried from time to time over the years to figure out a way.

For question 2, the reason you are never getting litigation or crime is the way that the compif function works.  It is based on the order of your items, so when you run it will first test if money is in the field, if so it doesn't test anything else as it has found it.  When it tests "" (nothing), for whatever reason, the compif always returns true so when it tests the third item it returns true and stops there and will return a blank (nothing).  To get around this problem you need to test if a word is present and if not then not include it in your equation.  Here is an example that I did that hopefully will help you out.  You will also see I did a loop to make the writing of the equation easier.


Option Explicit

Sub Main
	Dim eqn As String
	Dim UserSpecificWord(12) As String
	Dim UserChoice(8) As String
	Dim i As Integer
	
	UserChoice(8) = "MY_FIELD"
	UserSpecificWord(0) = "money"
	UserSpecificWord(1) = "transfer"
	UserSpecificWord(11) = "litigation"
	UserSpecificWord(12) = "crime"
	
	eqn = "@CompIf("  'add the beginning of the equation
	For i = 0 To UBound(UserSpecificWord)
		If UserSpecificWord(i) <> "" Then 'test to make sure that a user has entered a word, if not skip it.
			eqn = eqn & " @Isini(""" & UserSpecificWord(i) & """, " & UserChoice(8) & "), """ & UserSpecificWord(i)  & ""","
		End If
	Next i
	eqn = eqn & " 1, """")" 'add the ending of the equation
	MsgBox eqn
End Sub

bluecox Tue, 05/28/2019 - 07:38

Thank you Brian, I believe your code will take me into the right direction to improve my (it is working now but with your suggestion it will be more efficient and smarter).
With regards to my first question , the fundamental problem here could be the fact that in Idea the collection of objects is treated differently than in VB - checkboxed , listboxes etc. are considered to be a dialog controls but i havent found any indication that all of them fall into one group of clearly defined objects so using loop for ... next would be easily possible.
I might be wrong with my assumption so happy to hear from other users who tried to tackle it already. 
Anyway i will explore it further and will come back with any solution i might find to share here.
 
Many thanks

bluecox Tue, 05/28/2019 - 07:38

Thank you Brian,
I believe your code will take me into the right direction to improve my existing code (it is working now but with your suggestion it should be more efficient and smarter).
 
With regards to my first question , the fundamental problem here could be the fact that in Idea the collection of objects is treated differently than in VB - checkboxes,textboxes , listboxes etc. are considered to be a dialog controls but i havent found any indication that all of them fall into one group of clearly defined objects so using loop for ... next to loop through the group (collection) of the elements of the same higher category would be easily possible.
I might be wrong with my assumption so happy to hear from other users who tried to tackle it already. 
Anyway i will explore it further and will come back to share it here with any solution that I might find.
 
Many thanks

Brian Element Wed, 05/29/2019 - 07:52

In reply to by bluecox

Hi bluecox,

I believe you are correct.  IDEAScript is based on an older version of VBA so what we see in Excel is an update of IDEAScript.  IDEA has decided, and I agree, that they will keep IDEAScript the same as there are lots of scripts out there but as of V10.3 scripts can now be created in Python which is a much more robust langauge then VBA currently is.  So they will keep the old language to support current scripts but the future of scripts will be with Python.

Brian

bluecox Wed, 07/10/2019 - 05:42

Hi Brian,
Just wanted to say a big thank you for your suggestion and code. After a few adjustments it works like a charm.