5 Replies Latest reply on Jul 1, 2016 3:05 AM by Mikeconnors

    Multi-state multiple objects using VBA

    Mikeconnors

      Hi All,

       

      I currently face an issue where I would like to multi-state 6 buttons on the screen. Where I am having trouble is that multiple tags are linked to each button, some of which are strings. I have attempted to write a Do While loop where it constantly checks the values of each button but I can't get it to work.

       

      I have used an If Else Then to try and trigger it however in that case, once a condition is found to be true, it will execute the corresponding code and not evaluate the conditions any further where i need it to continue evaluating. Does anyone know a way (preferably simpler than what I have) to be able to check statements and If it it true, make a button flash however continue to check other buttons? below is what I had written to try and explain a little better what I am trying to achieve. As most people can probably gather, when one of the ER

      tag conditions is met, the rest of them aren't checked.

      Private Sub ErrorStatus()
      'if the statement below is false, Eastern Ridge button will flash
      
      Call Define_Tags
      Flash = True
      
      Do While Flash = True
      
             If ERtagvalueAllInterfaceStat <> "OK" Or ERtagvalueAllBuffStat <> "OK" Or ERtagvalueLBS <> "OK" Or ERtagvalueCompRat < 20 Or ERtagvalueLowestArchiveSpace < 20 Or ERtagvalueBadValueRate > 20 Or ERtagvalueHighestInterface > 60 Then
                buttonEasternRidge.FillColor = RGB(150, 0, 0)
      
             ElseIf JBtagvalueAllInterfaceStat <> "OK" Or JBtagvalueAllBuffStat <> "OK" Or JBtagvalueLBS <> "OK" Or JBtagvalueCompRat < 20 Or JBtagvalueLowestArchiveSpace < 20 Or JBtagvalueBadValueRate > 20 Or JBtagvalueHighestInterface > 60 Then
                buttonJimblebar.FillColor = RGB(150, 0, 0)
      
             ElseIf WBtagvalueAllInterfaceStat <> "OK" Or WBtagvalueAllBuffStat <> "OK" Or WBtagvalueLBS <> "OK" Or WBtagvalueCompRat < 20 Or WBtagvalueLowestArchiveSpace < 20 Or WBtagvalueBadValueRate > 20 Or WBtagvalueHighestInterface > 60 Then
                ButtonWhaleback.FillColor = RGB(150, 0, 0)
      
             ElseIf YNtagvalueAllInterfaceStat <> "OK" Or YNtagvalueAllBuffStat <> "OK" Or YNtagvalueLBS <> "OK" Or YNtagvalueCompRat < 20 Or YNtagvalueLowestArchiveSpace < 20 Or YNtagvalueBadValueRate > 20 Or YNtagvalueHighestInterface > 60 Then
                ButtonYandi.FillColor = RGB(150, 0, 0)
      
             ElseIf PTtagvalueAllInterfaceStat <> "OK" Or PTtagvalueAllBuffStat <> "OK" Or PTtagvalueLBS <> "OK" Or PTtagvalueCompRat < 20 Or PTtagvalueLowestArchiveSpace < 20 Or PTtagvalueBadValueRate > 20 Or PTtagvalueHighestInterface > 60 Then
                ButtonPort.FillColor = RGB(150, 0, 0)
      
             ElseIf PEtagvalueLBS <> "OK" Or PTtagvalueLowestArchiveSpace < 20 Then
                ButtonPerth.FillColor = RGB(150, 0, 0)
      
             Else: buttonEasternRidge.FillColor = RGB(84, 188, 97)
                   buttonJimblebar.FillColor = RGB(84, 188, 97)
                   ButtonPerth.FillColor = RGB(84, 188, 97)
                   ButtonWhaleback.FillColor = RGB(84, 188, 97)
                   ButtonYandi.FillColor = RGB(84, 188, 97)
                   ButtonPort.FillColor = RGB(84, 188, 97)
             End If
             Sleep (300)
             DoEvents
      Loop
      End Sub
      

       

      I hope that makes sense, any other questions let me know,

       

      Thanks!

       

      Michael

        • Re: Multi-state multiple objects using VBA
          John Messinger

          You could simply change the ElseIf statements to If statements, so that each becomes a self contained If-Then block. This would ensure that each block is subsequently evaluated. The current layout of If-Then-ElseIf only allows for the first condition that is evaluated as True to be executed. As for your final Else clause, you could probably move that to the very top of the While loop, effectively setting the 'default' button values first, then evaluating to see if they should be altered to fit your multistate conditions. Alternatively, each line in that final Else clause could become the Else condition of each If-Then block - evaluate the underlying tags, and either set the button FillColor to the required multistate condition, or set it to the default value. See the following two layout options. In both instances, each clause gets evaluated in sequence, and each button is updated accordingly.

           

          Option 1:

          Private Sub ErrorStatus()
          'if the statement below is false, Eastern Ridge button will flash
            
          Call Define_Tags
          Flash = True
            
          Do While Flash = True
                 buttonEasternRidge.FillColor = RGB(84, 188, 97)
                 buttonJimblebar.FillColor = RGB(84, 188, 97)
                 buttonPerth.FillColor = RGB(84, 188, 97)
                 buttonWhaleback.FillColor = RGB(84, 188, 97)
                 buttonYandi.FillColor = RGB(84, 188, 97)
                 buttonPort.FillColor = RGB(84, 188, 97)
                       
                 If ERtagvalueAllInterfaceStat <> "OK" Or ERtagvalueAllBuffStat <> "OK" Or ERtagvalueLBS <> "OK" Or ERtagvalueCompRat < 20 Or ERtagvalueLowestArchiveSpace < 20 Or ERtagvalueBadValueRate > 20 Or ERtagvalueHighestInterface > 60 Then
                    buttonEasternRidge.FillColor = RGB(150, 0, 0)
                 End If
                    
            
                 If JBtagvalueAllInterfaceStat <> "OK" Or JBtagvalueAllBuffStat <> "OK" Or JBtagvalueLBS <> "OK" Or JBtagvalueCompRat < 20 Or JBtagvalueLowestArchiveSpace < 20 Or JBtagvalueBadValueRate > 20 Or JBtagvalueHighestInterface > 60 Then
                    buttonJimblebar.FillColor = RGB(150, 0, 0)
                 End If
                    
            
                 If WBtagvalueAllInterfaceStat <> "OK" Or WBtagvalueAllBuffStat <> "OK" Or WBtagvalueLBS <> "OK" Or WBtagvalueCompRat < 20 Or WBtagvalueLowestArchiveSpace < 20 Or WBtagvalueBadValueRate > 20 Or WBtagvalueHighestInterface > 60 Then
                    buttonWhaleback.FillColor = RGB(150, 0, 0)
                 End If
            
                 If YNtagvalueAllInterfaceStat <> "OK" Or YNtagvalueAllBuffStat <> "OK" Or YNtagvalueLBS <> "OK" Or YNtagvalueCompRat < 20 Or YNtagvalueLowestArchiveSpace < 20 Or YNtagvalueBadValueRate > 20 Or YNtagvalueHighestInterface > 60 Then
                    buttonYandi.FillColor = RGB(150, 0, 0)
                 End If
                 
                 If PTtagvalueAllInterfaceStat <> "OK" Or PTtagvalueAllBuffStat <> "OK" Or PTtagvalueLBS <> "OK" Or PTtagvalueCompRat < 20 Or PTtagvalueLowestArchiveSpace < 20 Or PTtagvalueBadValueRate > 20 Or PTtagvalueHighestInterface > 60 Then
                    buttonPort.FillColor = RGB(150, 0, 0)
                 End If
                 
                 If PEtagvalueLBS <> "OK" Or PTtagvalueLowestArchiveSpace < 20 Then
                    buttonPerth.FillColor = RGB(150, 0, 0)
                 End If
                 Sleep (300)
                 DoEvents
          Loop
          End Sub
          

           

          Option 2:

          Private Sub ErrorStatus()
          'if the statement below is false, Eastern Ridge button will flash
            
          Call Define_Tags
          Flash = True
            
          Do While Flash = True
                 If ERtagvalueAllInterfaceStat <> "OK" Or ERtagvalueAllBuffStat <> "OK" Or ERtagvalueLBS <> "OK" Or ERtagvalueCompRat < 20 Or ERtagvalueLowestArchiveSpace < 20 Or ERtagvalueBadValueRate > 20 Or ERtagvalueHighestInterface > 60 Then
                    buttonEasternRidge.FillColor = RGB(150, 0, 0)
                 Else
                      buttonEasternRidge.FillColor = RGB(84, 188, 97)
                 End If
                    
            
                 If JBtagvalueAllInterfaceStat <> "OK" Or JBtagvalueAllBuffStat <> "OK" Or JBtagvalueLBS <> "OK" Or JBtagvalueCompRat < 20 Or JBtagvalueLowestArchiveSpace < 20 Or JBtagvalueBadValueRate > 20 Or JBtagvalueHighestInterface > 60 Then
                    buttonJimblebar.FillColor = RGB(150, 0, 0)
                 Else
                      buttonJimblebar.FillColor = RGB(84, 188, 97)
                 End If
                    
            
                 If WBtagvalueAllInterfaceStat <> "OK" Or WBtagvalueAllBuffStat <> "OK" Or WBtagvalueLBS <> "OK" Or WBtagvalueCompRat < 20 Or WBtagvalueLowestArchiveSpace < 20 Or WBtagvalueBadValueRate > 20 Or WBtagvalueHighestInterface > 60 Then
                    buttonWhaleback.FillColor = RGB(150, 0, 0)
                 Else
                      buttonWhaleback.FillColor = RGB(84, 188, 97)
                 End If
            
                 If YNtagvalueAllInterfaceStat <> "OK" Or YNtagvalueAllBuffStat <> "OK" Or YNtagvalueLBS <> "OK" Or YNtagvalueCompRat < 20 Or YNtagvalueLowestArchiveSpace < 20 Or YNtagvalueBadValueRate > 20 Or YNtagvalueHighestInterface > 60 Then
                    buttonYandi.FillColor = RGB(150, 0, 0)
                 Else
                      buttonYandi.FillColor = RGB(84, 188, 97)
                 End If
                 
                 If PTtagvalueAllInterfaceStat <> "OK" Or PTtagvalueAllBuffStat <> "OK" Or PTtagvalueLBS <> "OK" Or PTtagvalueCompRat < 20 Or PTtagvalueLowestArchiveSpace < 20 Or PTtagvalueBadValueRate > 20 Or PTtagvalueHighestInterface > 60 Then
                    buttonPort.FillColor = RGB(150, 0, 0)
                 Else
                      buttonPort.FillColor = RGB(84, 188, 97)
                 End If
                 
                 If PEtagvalueLBS <> "OK" Or PTtagvalueLowestArchiveSpace < 20 Then
                    buttonPerth.FillColor = RGB(150, 0, 0)
                 Else
                      buttonPerth.FillColor = RGB(84, 188, 97)
                 End If
                 Sleep (300)
                 DoEvents
          Loop
          End Sub
          
            • Re: Multi-state multiple objects using VBA
              Mikeconnors

              Hi John,

               

              Thanks for the reply, I should have made myself a little clearer. I forgot to mention that I would like the multi-state to be flashing. The idea I had in pseudo code goes something like

               

              button = Black

               

              If Button = Black and <statements> are True

              button = Red

               

              Else Button = Black

              loop

               

              this way every alternating time through the loop the button would change from red to black. I realise I haven't done this In my code because I'm not sure how, Is it possible?

                • Re: Multi-state multiple objects using VBA
                  John Messinger

                  Hi Michael,

                   

                  Yes, this should be possible. You will need to add an additional test to your If condition to create the toggle effect, same as the code example here. In this case, it becomes an AND condition.

                   

                  I've modified your code (and refactored to improve readability for the sake of this example) showing the kind of approach you might take. As I seem to be be a bit challenged tonight with syntax highlighting for the entire code sample, I've attached it separately to this post. basically, it checks for the required tag conditions and whether the button fill colour RGB value is set to (84, 188, 97), then sets the fill colour based on the result of the AND operation. This should give you the toggle effect you are looking for.

                   

                  Regards,

                  John

                  1 of 1 people found this helpful
              • Re: Multi-state multiple objects using VBA
                jhim

                Hi Michael

                 

                It looks like your code skips all the comparison below once an ElseIf statement is satisfied.

                How about changing each "ElseIF" to just "IF"? would that work?

                for example,

                If ERtagvalueAllInterfaceStat <> "OK" Or ERtagvalueAllBuffStat <> "OK" Or ERtagvalueLBS <> "OK" Or ERtagvalueCompRat < 20 Or ERtagvalueLowestArchiveSpace < 20 Or ERtagvalueBadValueRate > 20 Or ERtagvalueHighestInterface > 60 Then buttonEasternRidge.FillColor = RGB(150, 0, 0)

                Else buttonEasternRidge.FillColor = RGB(84, 188, 97)

                End If

                 

                If JBtagvalueAllInterfaceStat <> "OK" Or JBtagvalueAllBuffStat <> "OK" Or JBtagvalueLBS <> "OK" Or JBtagvalueCompRat < 20 Or JBtagvalueLowestArchiveSpace < 20 Or JBtagvalueBadValueRate > 20 Or JBtagvalueHighestInterface > 60 Then buttonJimblebar.FillColor = RGB(150, 0, 0)

                Else buttonJimblebar.FillColor = RGB(84, 188, 97)

                End If

                 

                 

                And so on.

                Since the loop will check the IF statements individually, it will check all the statements regardless of one satisfying the condition or not.