Checkbox problem

[dragavs]

my problem is checkboxes didnt tick,

'checkbox for slipsheets    Dim checkboxes As CheckBox    If Range("EU5").Value = "PALETIZED" Then        checkboxes("Check Box 36").Value = xlOn        Else        checkboxes("Check Box 36").Value = xlOff    End If    If Range("EU5").Value = "LOOSE-LOADED" Then        checkboxes("Check Box 71").Value = xlOn        Else        checkboxes("Check Box 71").Value = xlOff    End If    If Range("EU5").Value = "UNITIZED" Then        checkboxes("Check Box 72").Value = xlOn        Else        checkboxes("Check Box 72").Value = xlOff    End If    If Range("EU5").Value = "PLASTIC SLIPSHEETS" Then        checkboxes("Check Box 73").Value = xlOn        checkboxes("Check Box 37").Value = xlOn        Else        checkboxes("Check Box 73").Value = xlOff        checkboxes("Check Box 37").Value = xlOff    End If    If Range("EU5").Value = "FIBER SLIPSHEETS" Then        checkboxes("Check Box 73").Value = xlOn        checkboxes("Check Box 37").Value = xlOn        Else        checkboxes("Check Box 73").Value = xlOff        checkboxes("Check Box 37").Value = xlOff    End If        'next    Range("eu6").Select    Application.CutCopyMode = False    Selection.Copy    Range("eu5").Select    activesheet.Paste    Range("eu6").Select    Selection.Delete Shift:=xlUp

 

[saltkev]

Hi

Drop this code in to your code page. If you need to run it under a command button remove the "Isect Code", You will need to alter the code to suit your setup. I have altered your EU5 ranges need changing back. I have addressed the Checkbox prob only not whats going on after.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("sheet1").Activate ' Change to Suit

ActiveSheet.CheckBox36.Value = False
ActiveSheet.CheckBox37.Value = False
ActiveSheet.CheckBox71.Value = False
ActiveSheet.CheckBox72.Value = False
ActiveSheet.CheckBox73.Value = False

If Intersect(ActiveCell, Range("E1:e10")) Is Nothing Then  ' Alter Range To Suite
        
    Else
  
'checkbox for slipsheets
If Range("E5").Value = "PALETIZED" Then
ActiveSheet.CheckBox36.Value = True
Else
ActiveSheet.CheckBox36.Value = False
End If
If Range("E5").Value = "LOOSE-LOADED" Then
ActiveSheet.CheckBox71.Value = True
Else
ActiveSheet.CheckBox71.Value = False
End If
If Range("E5").Value = "UNITIZED" Then
ActiveSheet.CheckBox72.Value = True
Else
ActiveSheet.CheckBox72.Value = False
End If
If Range("E5").Value = "PLASTIC SLIPSHEETS" Or Range("E5").Value = "FIBER SLIPSHEETS" Then
ActiveSheet.CheckBox73.Value = True
ActiveSheet.CheckBox37.Value = True
Else
ActiveSheet.CheckBox73.Value = False
ActiveSheet.CheckBox37.Value = False
End If

'next
'Range("e6").Select
'Application.CutCopyMode = False
'Selection.Range("e5").Copy
'ActiveCell.Offset(1, 0).Select
'ActiveSheet.Paste
'Range("e6").Select
'Selection.Delete Shift:=xlUp
 
    End If
 
 
End Sub

Regards

Kev

 

[dmt32]

Kev,
looking at code suggests OP is using CheckBoxes from Forms toolbox.

Dave

 

[saltkev]

OK

If that is the case just need to do a find on "Activesheet" and replace "My_UserForm_name"

regards

Kev

 

[dragavs]

thank you for your codes @saltkev but i already submit my code and i use the cell link to my checkboxes. however i tried your code and it also didnt work because i think i use check box(form control).

 

[saltkev]

send me a copy of your workbook containing dummy data and I will have a look


kevin.eames@airbus.com

 

[dmt32]

Hi,
try this code & see if does what you want.

Sub SetCheckboxes()
    Dim Arr As Variant, ChkBxArr As Variant
    Dim Action As Integer, i As Integer
    Dim adj1 As Integer, adj2 As Integer


    Arr = Array("PALETIZED", "LOOSE-LOADED", "UNITIZED", "PLASTIC SLIPSHEETS", "FIBER SLIPSHEETS")
    ChkBxArr = Array("Check Box 36", "Check Box 71", "Check Box 72", "Check Box 73", "Check Box 37")


    On Error Resume Next
    With Worksheets(1)
        Action = Application.Match(.Range("EU5").Value, Arr, False)
        
        For i = LBound(ChkBxArr) To UBound(ChkBxArr)
            .checkboxes(ChkBxArr(i)).Value = xlOff
        Next i


        adj1 = IIf(Action < 5, -1, -2)
        adj2 = IIf(Action = 4, 0, -1)
        
        .checkboxes(ChkBxArr(Action + adj1)).Value = xlOn
        .checkboxes(ChkBxArr(Action + adj2)).Value = xlOn


    End With
    On Error GoTo 0
End Sub

I have assumed you are running code from standard module but can be placed in event procedure if required & that your checkboxes are on Sheet(1) - change if required.

Hope Helpful

Dave