Vba previous checkbox

[Ciccio86]

Hi, i need to check if previous checkbox is ticked, i tried like this but didn't work...

Sub PrCHK()
Dim i, o As Integer

o = i - 1

For i = 1 To 6
If Worksheets("Sheet1").OLEObjects("CheckBox" & i).Object.Value = True And Worksheets("Sheet").OLEObjects("CheckBox" & o).Object.Value = False Then
MsgBox "Error"
End If
Next i
End Sub

Can someone help me? Thanks

 

[Ciccio86]

Sub PrCHK()
Dim i As Integer
For i = 1 To 6
If Worksheets("Sheet1").OLEObjects("CheckBox" & x - 1).Object = False Then
MsgBox "Error"
End If
Next i
End Sub

Tried like this still not worked

 

[frabulator]

Welcome to the forum!

Try this. It was adopted from your code.

This will loop through all checkboxes on the active sheet and return a string message box for the checkboxes that were checked. This should be a good starting point for you.

Sub PrCHK()
    Dim i As Integer
    i = 0
    Dim Str As String
    Str = ""
    Dim cb As CheckBox

    For Each cb In ActiveSheet.CheckBoxes
       i = i + 1
        If cb.Value = Checked Then
            Str = Str & "Checkbox " & i & " is checked" & vbNewLine
        End If
    Next
   
    If Str <> "" Then MsgBox (Str)
End Sub

You can read more about this function here if you are interested.

If you find that your question has been answered please mark it as so to help future viewers

I hope this helps!

 

[Ciccio86]

Im using ActiveX checkboxes, but i need that the code check if previous checkbox is ticked and not the next one

Welcome to the forum!

Try this. It was adopted from your code.

This will loop through all checkboxes on the active sheet and return a string message box for the checkboxes that were checked. This should be a good starting point for you.

Sub PrCHK()
    Dim i As Integer
    i = 0
    Dim Str As String
    Str = ""
    Dim cb As CheckBox

    For Each cb In ActiveSheet.CheckBoxes
       i = i + 1
        If cb.Value = Checked Then
            Str = Str & "Checkbox " & i & " is checked" & vbNewLine
        End If
    Next
  
    If Str <> "" Then MsgBox (Str)
End Sub

You can read more about this function here if you are interested.

If you find that your question has been answered please mark it as so to help future viewers

I hope this helps!

 

[frabulator]

i need that the code check if previous checkbox is ticked and not the next one

Isnt that the same thing?
The only difference would be that you would not be checking if the last checkbox is checked.
Please see below. Both produces the same result, except the last checkbox has nothing after it.

Sub CheckWhichIsChecked()
    Dim i As Integer
    i = 0
    Dim str As String
    str = ""
    Dim cb As CheckBox

    For Each cb In ActiveSheet.CheckBoxes
       i = i + 1
        If cb.Value = Checked Then
            str = str & "Checkbox " & i & " is checked" & vbNewLine
        End If
    Next
    
    If str <> "" Then MsgBox (str)
End Sub

Sub CheckWhichIsCheckedBEFORE()
    Dim i As Integer
    i = 0
    Dim str As String
    str = ""
    Dim cb As CheckBox
    
    Dim WasPrev As Boolean
    WasPrev = False
    

    For Each cb In ActiveSheet.CheckBoxes
       i = i + 1
      
        If WasPrev = True Then
            'this is where you do stuff if the previous one was checked
            str = str & "Checkbox " & i - 1 & " is checked" & vbNewLine
            'turn boolean back off
            WasPrev = False
        End If
        
        If cb.Value = Checked Then
            WasPrev = True  'if it is checked the turn on boolean
        End If
        
        
    Next
    
    If str <> "" Then MsgBox (str)

 

[Ciccio86]

Isnt that the same thing?
The only difference would be that you would not be checking if the last checkbox is checked.
Please see below. Both produces the same result, except the last checkbox has nothing after it.

Sub CheckWhichIsCheckedBEFORE()
    Dim i As Integer
    i = 0
    Dim str As String
    str = ""
    Dim cb As CheckBox
   
    Dim WasPrev As Boolean
    WasPrev = False
   

    For Each cb In ActiveSheet.CheckBoxes
       i = i + 1
     
        If WasPrev = True Then
            'this is where you do stuff if the previous one was checked
            str = str & "Checkbox " & i - 1 & " is checked" & vbNewLine
            'turn boolean back off
            WasPrev = False
        End If
       
        If cb.Value = Checked Then
            WasPrev = True  'if it is checked the turn on boolean
        End If
       
       
    Next
   
    If str <> "" Then MsgBox (str)

Thanks for reply but this code don't work

 

[Ciccio86]

Tryng to call OLEObject

 

[frabulator]

Please see the code below. It is exactly the same as above, but I changed the checkbox check to be active X controls.

But, the redundancy is still there. Both codes do exactly the same thing. Why would you need one after the one that is checked?

Sub OLECheckWhichIsChecked()
    Dim i As Integer
    i = 0
    Dim str As String
    str = ""
    Dim cb As Object

    For Each cb In ActiveSheet.OLEObjects
       i = i + 1
        If TypeName(cb.Object) = "CheckBox" Then
            If cb.Object.Value = True Then
                str = str & "Checkbox " & i & " is checked" & vbNewLine
            End If
        End If
        
    Next
    
    If str <> "" Then MsgBox (str)
End Sub

Sub OLECheckWhichIsCheckedBEFORE()
    Dim i As Integer
    i = 0
    Dim str As String
    str = ""
    Dim cb As Object
    
    Dim WasPrev As Boolean
    WasPrev = False
    

    For Each cb In ActiveSheet.OLEObjects
        If TypeName(cb.Object) = "CheckBox" Then
            i = i + 1
            
            If WasPrev = True Then
                 'this is where you do stuff if the previous one was checked
                 str = str & "Checkbox " & i - 1 & " is checked" & vbNewLine
                 'turn boolean back off
                 WasPrev = False
             End If
            
            If TypeName(cb.Object) = "CheckBox" Then
                If cb.Object.Value = True Then
                    WasPrev = True
                End If
            End If
        End If
        
    Next
    
    If str <> "" Then MsgBox (str)
End Sub