lundi 3 avril 2017

Excel - If control true Then loop non sequential variables

A little back story. I originally had 2 checkboxes that were used in an IF THEN statement. Depending on which ones were true, variables were assigned and a FOR NEXT loop was run with those variables as the For "S = Startnumber to EndNumber." These variables were always sequential, in that they were always 1 to 2, 1 to 1, or 2 to 2.

 If ActiveSheet.OLEObjects("Checkbox61").Object.Value = True And ActiveSheet.OLEObjects("Checkbox62").Object.Value = False then
   Startnumber = 1
   Endnumber = 2
            ElseIf ActiveSheet.OLEObjects("Checkbox61").Object.Value = True And ActiveSheet.OLEObjects("Checkbox62").Object.Value = False Then
                Startnumber = 1
                Endnumber = 1
                    ElseIf ActiveSheet.OLEObjects("Checkbox61").Object.Value = False And ActiveSheet.OLEObjects("Checkbox62").Object.Value = True Then
                        Startnumber = 2
                        Endnumber = 2   

the S variable would then be assigned to a string, so if the loop was
For s = 1 to 2 Then the loop would first send a string for Shift = 1 and then Shift = 2.

For s = Startnumber To Endnumber ' Shift range

 For i = 1 To 56 ' Numbers attached to Checkbox (Checkbox(X) to Checkbox(Y))


  If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = True Then
    Dept = ActiveSheet.OLEObjects("CheckBox" & i).Object.Caption

     Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.Moveto 18, 46
     Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.SendKeys (s)                      'Shift
     Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

Do

However, I've recently been asked to add a third checkbox to the mix, causing my brain to implode.

So, now the question is that if I have two checkboxes true and 1 false, how do I only run the loop for those variables. For example.

If my checkboxes are as such

Checkbox61 = 1st shift (1)
Checkbox62 = 2nd shift (2)
Checkbox63 = 3rd shift (3)

Shift Example

So, say Checkbox61 and Checkbox63 are True and Checkbox62 is false that causes my For S = Startnumber to Endnumber to become non sequential.

I only want 1 and 3 to be sent as Shift, however any combination of checkboxes can be selected.

Shy of repeating a buttload of code for each IF statement, I was hoping there was a way to Loop through specific variables depending on either the If statement or the value of the three checkboxes.

Maybe:

For each checkbox in object.frame("Shift")    

If checkbox61 = true then
 s = 1
elseif checkbox62 = true then
 s = 2
elseif checkbox63 = true then
 s = 3
end if

next checkbox

I realize this isn't correct code, but it was just an idea.

I've thought about using CASE, but I don't think that'll work since once the condition is met the SELECT CASE option is over. Any help would be greatly appreciated!




Aucun commentaire:

Enregistrer un commentaire