Making a userform checkbox view a chechmark if value = "X" in works sheet

[KiraJensen]

Hi

I'm trying to make a checkbox within a userform that can view the checkbox as checked off, depeding on there being a "X" ind the B column of the worksheet.

I found this code:

Private Sub Userform_Activate()
If [B1] = "X" Then
Me.Checkbox1 = True
Else
Me.Checkbox1=False
End If
End Sub

The code works just fine, but I'm in need of it being dynamic, so I can look up if there is a X in the entire B column, depending on which line in B column I'm looking up?

For me to look up the individual columns i have autogenerated ID Numbers in column A, where i have created a dynamic dropdown menu with all these numbers in.

Is it possible to make the userform look at the value in ComboBox_ID, for the userform to know which line it has to look at and set the checkbox 1 = true / False depending on there being a "X" in column B without me having to write the code staded above for the individual rows in column B?

Hope someone is able to help me with this due to me having 92 rows of information, and there being constantly added information to the worksheet.


Thanks in advance!
- Kira

 

[Fluff]

Where in the sheet will the ComboBox_ID value be found? and will it be the only instance of that value in the column?

 

[dmt32]

Hi,
give following a try & see if does what you want

Place in your userforms code page

Private Sub ComboBox_ID_Change()
    Dim m As Variant
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    m = Application.Match(Me.ComboBox_ID.Text, ws.Columns(1), 0)
    Me.CheckBox1.Value = CBool(UCase(ws.Cells(CLng(m), 2).Value) = "X")
End Sub

Change sheet name in RED as required


Dave

 

[KiraJensen]

The ComboBox_ID is placed within a Userform, where the user is able to look up the ID and see the information connected to that ID.

So all the ID numbers are individual, they start with no 10001 and currently go all the way up to 10256.

 

[KiraJensen]

Hi Dave

I tried puttin it into my ComboBox_ID_Cange, but it doesn't seem to do anything although I'm searching for specific ID numbers which has an X in column 2

 

[dmt32]

Hi Dave

I tried puttin it into my ComboBox_ID_Cange, but it doesn't seem to do anything although I'm searching for specific ID numbers which has an X in column 2

Hi,
I just assumed you values were text

try this update

Private Sub ComboBox_ID_Change()
    Dim m As Variant
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    m = Application.Match(Val(Me.ComboBox_ID.Value), ws.Columns(1), 0)
    Me.CheckBox1.Value = CBool(UCase(ws.Cells(CLng(m), 2).Value) = "X")
End Sub

Change sheet name as required

Dave

 

[KiraJensen]

Hi,

Thank you for your continnued help.

The code you send to me, still doesn't seem to work?

The other code i have for my checkboxes needed to be put into an click event, do you maybe think that this is the problem here as well?

 

[Fluff]

Try running it from a Click event & see if that works.
If it doesn't then do you get any error messages?

 

[dmt32]

did you change the sheet name in the code?

If this is not problem then post all your forms code would be helpful

Dave

 

[KiraJensen]

I don't get any error messages no, I have now tried placing it in the ComboBox_ID_Change evnet and the CommandButton_Search_Click event and it still doesn't seam to do anything?

Here is all my code for the specific userform if it helps:

Dim blnSearch As Boolean
Dim totRows As Long, i As Long




Private Sub CommandButton_Close_Click()
    Unload Me
End Sub



Private Sub CommandButton_Search_Click()

blnSearch = True

If ComboBox_ID = "" Then
    MsgBox "Select an ID number"
End If



Dim m As Variant
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
    m = Application.Match(Me.ComboBox_ID.Value, ws.Columns(1), 0)
    Me.CheckBox_Eldesign.Value = CBool(UCase(ws.Cells(CLng(m), 2).Value) = "X")


Dim totRows As Long, i As Long


'Unlocking the sheet
Sheets("Sheet1").Unprotect Password:="********"

totRows = Worksheets("Sheet1").Range("A3").CurrentRegion.Rows.Count
For i = 2 To totRows
    If Trim(Sheet1.Cells(i, 1)) = Trim(ComboBox_ID.Text) Then
        TextBox_ID.Text = Sheet1.Cells(i, 1)
        'Replaced by 10 checkboxes, which I'm currently trying to progam
        'Label_Status.Caption = Sheet1.Cells(i, 11)
        'Label_Description.Caption = Sheet1.Cells(i, 12)
        ComboBox_Platform.Text = Sheet1.Cells(i, 13)
        TextBox_Site.Text = Sheet1.Cells(i, 14)
        ComboBox_Country.Text = Sheet1.Cells(i, 15)
        TextBox_Turbine.Text = Sheet1.Cells(i, 16)
        TextBox_Scope.Text = Sheet1.Cells(i, 17)
        ComboBox_OrderNo.Text = Sheet1.Cells(i, 18)
        ComboBox_RequestingOrg.Text = Sheet1.Cells(i, 19)
        ComboBox_Requester.Text = Sheet1.Cells(i, 20)
        TextBox_CustomerRef.Text = Sheet1.Cells(i, 21)
        Label_TimeRemain.Caption = Sheet1.Cells(i, 22)
        TextBox_TimeBudget.Text = Sheet1.Cells(i, 23)
        Label_TimeActual.Caption = Sheet1.Cells(i, 24)
        ComboBox_TimeType.Text = Sheet1.Cells(i, 25)
        Label_CostRemain.Caption = Sheet1.Cells(i, 26)
        TextBox_CostBudget.Text = Sheet1.Cells(i, 27)
        TextBox_CostActual.Text = Sheet1.Cells(i, 28)
        ComboBox_Responsible.Text = Sheet1.Cells(i, 29)
            'Canceled and Risk is placed differently in the spreadsheet and will therefore be moved
        TextBox_M1Actual.Text = Sheet1.Cells(i, 30)
        TextBox_M2Actual.Text = Sheet1.Cells(i, 31)
        TextBox_M3Actual.Text = Sheet1.Cells(i, 32)
        TextBox_M4Actual.Text = Sheet1.Cells(i, 33)
        TextBox_M5Actual.Text = Sheet1.Cells(i, 34)
        TextBox_M6Actual.Text = Sheet1.Cells(i, 35)
        TextBox_M7Actual.Text = Sheet1.Cells(i, 36)
        TextBox_M2Planned.Text = Sheet1.Cells(i, 37)
        TextBox_M3Planned.Text = Sheet1.Cells(i, 38)
        TextBox_M4Planned.Text = Sheet1.Cells(i, 39)
        TextBox_M5Planned.Text = Sheet1.Cells(i, 40)
        TextBox_M6Planned.Text = Sheet1.Cells(i, 41)
        TextBox_M7Planned.Text = Sheet1.Cells(i, 42)
            'Canceled and risk moved here
        TextBox_Canceled.Text = Sheet1.Cells(i, 43)
        ComboBox_Risk.Text = Sheet1.Cells(i, 44)
        TextBox_CriticalPath.Text = Sheet1.Cells(i, 45)
        TextBox_Comments.Text = Sheet1.Cells(i, 46)
        TextBox_ChangeLog.Text = Sheet1.Cells(i, 47)
        
        Exit For
    End If
Next i


'Locking the sheet again
Sheets("Sheet1").Protect Password:="*********"
                
End Sub



Private Sub CommandButton_Save_Click()



Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")


'Making the "save" button save the details which has been changed
    If ComboBox_ID.Text = "" Then
        MsgBox "Select an ID number", vbCritical, "Save"
        ComboBox_ID.SetFocus
        Exit Sub
    End If
Call pSave
End Sub



Private Sub pSave()


Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
    If blnSearch = True Then
        
        'Unlocking the sheet
        Sheets("Sheet1").Unprotect Password:="**********"
    
        totRows = Worksheets("Sheet1").Range("A3").CurrentRegion.Rows.Count
        
        For i = 2 To totRows
            If Trim(Worksheets("Sheet1").Cells(i, 1)) = Trim(ComboBox_ID.Text) Then
                Worksheets("Sheet1").Cells(i, 1).Value = TextBox_ID.Text
                'The 10 checkboxes I'm trying to program        
                'Worksheets("Sheet1").Cells(i, 11).Value = Label_Status.Caption
                'Worksheets("Sheet1").Cells(i, 12).Value = Label_Description.Caption
                Worksheets("Sheet1").Cells(i, 13).Value = ComboBox_Platform.Text
                Worksheets("Sheet1").Cells(i, 14).Value = TextBox_Site.Text
                Worksheets("Sheet1").Cells(i, 15).Value = ComboBox_Country.Text
                Worksheets("Sheet1").Cells(i, 16).Value = TextBox_Turbine.Text
                Worksheets("Sheet1").Cells(i, 17).Value = TextBox_Scope.Text
                Worksheets("Sheet1").Cells(i, 18).Value = ComboBox_OrderNo.Text
                Worksheets("Sheet1").Cells(i, 19).Value = ComboBox_RequestingOrg.Text
                Worksheets("Sheet1").Cells(i, 20).Value = ComboBox_Requester.Text
                Worksheets("Sheet1").Cells(i, 21).Value = TextBox_CustomerRef.Text
                'Worksheets("Sheet1").Cells(i, 22).Value = Label_TimeRemain.Caption
                Worksheets("Sheet1").Cells(i, 23).Value = TextBox_TimeBudget.Text
                'Worksheets("Sheet1").Cells(i, 24).Value = Label_TimeActual.Caption
                Worksheets("Sheet1").Cells(i, 25).Value = ComboBox_TimeType.Text
                'Worksheets("Sheet1").Cells(i, 26).Value = Label_CostRemain.Caption
                Worksheets("Sheet1").Cells(i, 27).Value = TextBox_CostBudget.Text
                Worksheets("Sheet1").Cells(i, 28).Value = TextBox_CostActual.Text
                Worksheets("Sheet1").Cells(i, 29).Value = ComboBox_Responsible.Text
                    'Canceled and Risk will be written later in the code due to them having another possision in the spreadsheet
                Worksheets("Sheet1").Cells(i, 30).Value = TextBox_M1Actual.Text
                Worksheets("Sheet1").Cells(i, 31).Value = TextBox_M2Actual.Text
                Worksheets("Sheet1").Cells(i, 32).Value = TextBox_M3Actual.Text
                Worksheets("Sheet1").Cells(i, 33).Value = TextBox_M4Actual.Text
                Worksheets("Sheet1").Cells(i, 34).Value = TextBox_M5Actual.Text
                Worksheets("Sheet1").Cells(i, 35).Value = TextBox_M6Actual.Text
                Worksheets("Sheet1").Cells(i, 36).Value = TextBox_M7Actual.Text
                Worksheets("Sheet1").Cells(i, 37).Value = TextBox_M2Planned.Text
                Worksheets("Sheet1").Cells(i, 38).Value = TextBox_M3Planned.Text
                Worksheets("Sheet1").Cells(i, 39).Value = TextBox_M4Planned.Text
                Worksheets("Sheet1").Cells(i, 40).Value = TextBox_M5Planned.Text
                Worksheets("Sheet1").Cells(i, 41).Value = TextBox_M6Planned.Text
                Worksheets("Sheet1").Cells(i, 42).Value = TextBox_M7Planned.Text
                    'Canceled and Risk
                Worksheets("Sheet1").Cells(i, 43).Value = TextBox_Canceled.Text
                Worksheets("Sheet1").Cells(i, 44).Value = ComboBox_Risk.Text
                Worksheets("Sheet1").Cells(i, 45).Value = TextBox_CriticalPath.Text
                Worksheets("Sheet1").Cells(i, 46).Value = TextBox_Comments.Text
                Worksheets("Sheet1").Cells(i, 47).Value = TextBox_ChangeLog.Text
                
                
                TextBox_ID = ""
                'ComboBox_TaskType = "" - replaced with 10 checkboxes
                'Label_Status = ""
                Label_Description = ""
                ComboBox_Platform = ""
                TextBox_Site = ""
                ComboBox_Country = ""
                TextBox_Turbine = ""
                TextBox_Scope = ""
                ComboBox_OrderNo = ""
                ComboBox_RequestingOrg = ""
                ComboBox_Requester = ""
                TextBox_CustomerRef = ""
                Label_TimeRemain = ""
                TextBox_TimeBudget = ""
                Label_TimeActual = ""
                ComboBox_TimeType = ""
                Label_CostRemain = ""
                TextBox_CostBudget = ""
                TextBox_CostActual = ""
                ComboBox_Responsible = ""
                    'Canceled and Risk will be written later
                TextBox_M1Actual = ""
                TextBox_M2Actual = ""
                TextBox_M3Actual = ""
                TextBox_M4Actual = ""
                TextBox_M5Actual = ""
                TextBox_M6Actual = ""
                TextBox_M7Actual = ""
                TextBox_M2Planned = ""
                TextBox_M3Planned = ""
                TextBox_M4Planned = ""
                TextBox_M5Planned = ""
                TextBox_M6Planned = ""
                TextBox_M7Planned = ""
                    'Canceled and Risk
                TextBox_Canceled = ""
                ComboBox_Risk = ""
                TextBox_CriticalPath = ""
                TextBox_Comments = ""
                TextBox_ChangeLog = ""
                
            Exit For
        End If
    Next i
End If

    MsgBox "Your data was saved"
    
'Locking the sheet again
Sheets("Sheet1").Protect Password:="*********"

End Sub



Private Sub TextBox_ID_Change()
    TextBox_ID.Enabled = False
End Sub



Private Sub TextBox_M1Actual_Change()
    TextBox_M1Actual.Enabled = False
End Sub



Private Sub UserForm_Initialize()
'Determening what is put into the combobox ID from the sheet "Sheet1"
    Dim LastRow As Long
    Dim qCell As Range
        With Worksheets("Sheet1")
            For i = 1 To LastRow
                With .Columnt(i)
                LastRow = Lookup.Cells(Rows.Count, i).End(xlUp).Row
                    With Range(Cells(1, i), Cells(LastRow, i))
                    Range(Cells(1, i), Cells(LastRow, i)).Select
                    Selection.CreateName Top:=True
                    End With
                End With
            Next i
        End With
        
        Me.ComboBox_ID.RowSource = "ID"
'Determening what is put into the comboboxes (drop down menues)from the sheet "LookUp"
Dim LastColumn As Long
Dim aCell As Range
    With Worksheets("LookUp")
        For i = 1 To LastColumn
            With .Columns(i)
            LastRow = Lookup.Cells(Rows.Count, i).End(xlUp).Row
                With Range(Cells(1, i), Cells(LastRow, i))
                Range(Cells(1, i), Cells(LastRow, i)).Select
                Selection.CreateName Top:=True
                End With
            End With
        Next i
    End With
    
    Me.ComboBox_Platform.RowSource = "Platform"
    Me.ComboBox_Country.RowSource = "Country"
    Me.ComboBox_RequestingOrg.RowSource = "Requesting_Org"
    Me.ComboBox_Requester.RowSource = "Requester"
    Me.ComboBox_Responsible.RowSource = "Responsible"
    Me.ComboBox_Risk.RowSource = "Risk"
    Me.ComboBox_TimeType.RowSource = "Time_Type"
    
'Determening what is put into the combobox order no. from the sheet "Order_no"
Dim lr As Long
Dim bCell As Range
    With Worksheets("Order_no")
        For i = 1 To lr
            With .Columns(i)
            lr = Order_no.Cells(Rows.Count, i).End(xlUp).Row
                With Range(Cells(1, i), Cells(lr, i))
                Range(Cells(1, i), Cells(lr, i)).Select
                Selection.CreateName Top:=True
                End With
            End With
        Next i
    End With
    
    Me.ComboBox_OrderNo.RowSource = "Order_no"