CheckBox inserting value into specific row

K

KiraJensen

New Member
Joined
May 16, 2018
Messages
8
Hi

I have created a userform in excel, where you can look a specific ID number up and through that update the specific information connected to the number.

My problem is that I can't figure out how to get my checkboxes to update the value in the specific row with it being marked with an X or just leve it emply.

The code I found for this, which works in one of my other userforms, is as follows:

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

If Me.CheckBox1.Value = True Then

ws.Cells(rw, 2).Value = "X"

Else

ws.Cells(rw, 3).Value = ""

End If

I would like to be able to write something like this in my "lookup userform", where if the value is equeal to true then it should insert an "X" else it should leave the cell empty?


Here is my entire code for the userform if it is nessecary for you to help me, I believe the code should be placed somewhere within the Private Sub pSave

Thank you in advance for any help you giv :)


Code: 
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(Val(Me.ComboBox_ID.Value), ws.Columns(1), 0)
    Me.CheckBox_Eldesign.Value = CBool(UCase(ws.Cells(CLng(m), 2).Value) = "X")
    Me.CheckBox_Support.Value = CBool(UCase(ws.Cells(CLng(m), 3).Value) = "X")
    Me.CheckBox_Lidar.Value = CBool(UCase(ws.Cells(CLng(m), 4).Value) = "X")
    Me.CheckBox_Project.Value = CBool(UCase(ws.Cells(CLng(m), 5).Value) = "X")
    Me.CheckBox_Development.Value = CBool(UCase(ws.Cells(CLng(m), 6).Value) = "X")
    Me.CheckBox_Intern.Value = CBool(UCase(ws.Cells(CLng(m), 7).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)

        ' 10 checkboxes 

        'Label_Status.Caption = Sheet1.Cells(i, 12)
        'Label_Description.Caption = Sheet1.Cells(i, 13)
        ComboBox_Platform.Text = Sheet1.Cells(i, 14)
        TextBox_Site.Text = Sheet1.Cells(i, 15)
        ComboBox_Country.Text = Sheet1.Cells(i, 16)
        TextBox_Turbine.Text = Sheet1.Cells(i, 17)
        TextBox_Scope.Text = Sheet1.Cells(i, 18)
        ComboBox_OrderNo.Text = Sheet1.Cells(i, 19)
        ComboBox_RequestingOrg.Text = Sheet1.Cells(i, 20)
        ComboBox_Requester.Text = Sheet1.Cells(i, 21)
        TextBox_CustomerRef.Text = Sheet1.Cells(i, 22)
        Label_TimeRemain.Caption = Sheet1.Cells(i, 23)
        TextBox_TimeBudget.Text = Sheet1.Cells(i, 24)
        Label_TimeActual.Caption = Sheet1.Cells(i, 25)
        ComboBox_TimeType.Text = Sheet1.Cells(i, 26)
        Label_CostRemain.Caption = Sheet1.Cells(i, 27)
        TextBox_CostBudget.Text = Sheet1.Cells(i, 28)
        TextBox_CostActual.Text = Sheet1.Cells(i, 29)
        ComboBox_Responsible.Text = Sheet1.Cells(i, 30)
            'Canceled and Risk is placed differently in the spreadsheet and will therefore be moved
        TextBox_M1Actual.Text = Sheet1.Cells(i, 31)
        TextBox_M2Actual.Text = Sheet1.Cells(i, 32)
        TextBox_M3Actual.Text = Sheet1.Cells(i, 33)
        TextBox_M4Actual.Text = Sheet1.Cells(i, 34)
        TextBox_M5Actual.Text = Sheet1.Cells(i, 35)
        TextBox_M6Actual.Text = Sheet1.Cells(i, 36)
        TextBox_M7Actual.Text = Sheet1.Cells(i, 37)
        TextBox_M2Planned.Text = Sheet1.Cells(i, 38)
        TextBox_M3Planned.Text = Sheet1.Cells(i, 39)
        TextBox_M4Planned.Text = Sheet1.Cells(i, 40)
        TextBox_M5Planned.Text = Sheet1.Cells(i, 41)
        TextBox_M6Planned.Text = Sheet1.Cells(i, 42)
        TextBox_M7Planned.Text = Sheet1.Cells(i, 43)
            'Canceled and risk
        TextBox_Canceled.Text = Sheet1.Cells(i, 44)
        ComboBox_Risk.Text = Sheet1.Cells(i, 45)
        TextBox_CriticalPath.Text = Sheet1.Cells(i, 46)
        TextBox_Comments.Text = Sheet1.Cells(i, 47)
        TextBox_ChangeLog.Text = Sheet1.Cells(i, 48)
        
        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
                'Worksheets("Sheet1").Cells(i, 2).Value = ComboBox_TaskType.Text Replaced by 6 checkboxes instead, there are 4 more available spaces for adding task types
                'Worksheets("Sheet1").Cells(i, 2).Value = CheckBox_Eldesign.Value

                ' 10 CheckBoxes  - THIS IS WHERE I NEED THE CODE FOR DELETING OR INSERTING THE VALUE GIVEN IN THE CHECKBOX

                'Worksheets("Sheet1").Cells(i, 12).Value = Label_Status.Caption
                'Worksheets("Sheet1").Cells(i, 13).Value = Label_Description.Caption
                Worksheets("Sheet1").Cells(i, 14).Value = ComboBox_Platform.Text
                Worksheets("Sheet1").Cells(i, 15).Value = TextBox_Site.Text
                Worksheets("Sheet1").Cells(i, 16).Value = ComboBox_Country.Text
                Worksheets("Sheet1").Cells(i, 17).Value = TextBox_Turbine.Text
                Worksheets("Sheet1").Cells(i, 18).Value = TextBox_Scope.Text
                Worksheets("Sheet1").Cells(i, 19).Value = ComboBox_OrderNo.Text
                Worksheets("Sheet1").Cells(i, 20).Value = ComboBox_RequestingOrg.Text
                Worksheets("Sheet1").Cells(i, 21).Value = ComboBox_Requester.Text
                Worksheets("Sheet1").Cells(i, 23).Value = TextBox_CustomerRef.Text
                'Worksheets("Sheet1").Cells(i, 23).Value = Label_TimeRemain.Caption
                Worksheets("Sheet1").Cells(i, 24).Value = TextBox_TimeBudget.Text
                'Worksheets("Sheet1").Cells(i, 25).Value = Label_TimeActual.Caption
                Worksheets("Sheet1").Cells(i, 26).Value = ComboBox_TimeType.Text
                'Worksheets("Sheet1").Cells(i, 27).Value = Label_CostRemain.Caption
                Worksheets("Sheet1").Cells(i, 28).Value = TextBox_CostBudget.Text
                Worksheets("Sheet1").Cells(i, 29).Value = TextBox_CostActual.Text
                Worksheets("Sheet1").Cells(i, 30).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, 31).Value = TextBox_M1Actual.Text
                Worksheets("Sheet1").Cells(i, 32).Value = TextBox_M2Actual.Text
                Worksheets("Sheet1").Cells(i, 33).Value = TextBox_M3Actual.Text
                Worksheets("Sheet1").Cells(i, 34).Value = TextBox_M4Actual.Text
                Worksheets("Sheet1").Cells(i, 35).Value = TextBox_M5Actual.Text
                Worksheets("Sheet1").Cells(i, 36).Value = TextBox_M6Actual.Text
                Worksheets("Sheet1").Cells(i, 37).Value = TextBox_M7Actual.Text
                Worksheets("Sheet1").Cells(i, 38).Value = TextBox_M2Planned.Text
                Worksheets("Sheet1").Cells(i, 39).Value = TextBox_M3Planned.Text
                Worksheets("Sheet1").Cells(i, 40).Value = TextBox_M4Planned.Text
                Worksheets("Sheet1").Cells(i, 41).Value = TextBox_M5Planned.Text
                Worksheets("Sheet1").Cells(i, 42).Value = TextBox_M6Planned.Text
                Worksheets("Sheet1").Cells(i, 43).Value = TextBox_M7Planned.Text
                    'Canceled and Risk
                Worksheets("Sheet1").Cells(i, 44).Value = TextBox_Canceled.Text
                Worksheets("Sheet1").Cells(i, 45).Value = ComboBox_Risk.Text
                Worksheets("Sheet1").Cells(i, 46).Value = TextBox_CriticalPath.Text
                Worksheets("Sheet1").Cells(i, 47).Value = TextBox_Comments.Text
                Worksheets("Sheet1").Cells(i, 48).Value = TextBox_ChangeLog.Text
                
                
                TextBox_ID = ""
                '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"
End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = (MsgBox("Do you want to close the view project form?", vbYesNo) = vbNo)
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You must log in or register to reply here.

Similar threads

C
  • Solved
Need Help VBA
2
Replies
10
Views
410
Trebor76
T
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
733
Joe4
Joe4
S
  • Question Question
User Form not Loading
Replies
5
Views
150
StevieMP
S
G
  • Question Question
Creating a Word Doc using Excel VBA
Replies
2
Views
265
rikvny02
R
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
159
krunal123
K

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top