Check Box

[vmjan02]

I have multi Check box's on userform, then below code is adding the record in 1 cell only, its not working as required.

The code to search the lastrow in column J and once the user select the checkbox1 the label caption should be the cell(lastrow), if the user selects checkbox2 then label caption should be in the cell (lastrow) and so on.




Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1

If Me.CheckBox1.Enabled = True Then ws.Range("J" & LastRow).Offset(0, 9).Value = Me.Label11.Caption
If Me.CheckBox2.Enabled = True Then ws.Range("J" & LastRow).Offset(0, 9).Value = Me.Label12.Caption

 

[6StringJazzer]

The code to search the lastrow in column J and once the user select the checkbox1 the label caption should be the cell(lastrow), if the user selects checkbox2 then label caption should be in the cell (lastrow) and so on.

Your code does exactly what your description says you want to do. That is, you find the last row in column J and update that cell depending on what checkbox is checked. It sounds like you are saying that your description is not really want you want.

If checkbox1 is checked, do you want to update the last cell in column J--then if checkbox2 is checked, update the last cell after that? If that's what you mean you have to determine the last row each time. But you are not changing column J, you are changing column T. So the last row of J will not change. So I don't understand what you are trying to do.

 

[vmjan02]

Yes, it should always update the last cell in column J irrespective if the user selects checkbox1 or checkbox2 or checkbox3.

 

[Michael M]

Yeah....I'm a little confused as well...is this what you want

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1

If Me.CheckBox1.Enabled = True Then ws.Range("J" & LastRow).Value = Me.Label11.Caption
If Me.CheckBox2.Enabled = True Then ws.Range("J" & LastRow + 1).Value = Me.Label12.Caption

 

[vmjan02]

yes its correct, but as the user have not checked(not selected) the checkbox2, then also the code gives the label12 caption.

If user have checked(selected) only checkbox1 then the last empty cell should capture the caption11 only
If user have checked(selected) only checkbox2 then the last empty cell should capture the caption12 only

Any idea

 

[Michael M]

Ok, so try

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
If CheckBox1.value = True Then ws.Range("J" & LastRow).Value = Me.Label11.Caption
If CheckBox2.value = True Then ws.Range("J" & LastRow + 1).Value = Me.Label12.Caption

 

[vmjan02]

Perfect, this works well, but if the user selects only checkbox2 and not one then ("J" & LastRow + 1) don't work.

if the user selects both checkbox1 and checkbox2 then code (("J" & LastRow + 1)) works.

but if user selects only checkbox2 then the code (("J" & LastRow + 1)) add the value in lastlblank cell +1 and not in lastblank cell

How will it work

If user selects checkbox 1 and checkbox2 then ("J" & LastRow + 1) this is fine
but if user selects only checkbox2 then code to do this ("J" & LastRow).value should enter the value in lastblank cell.


Idea is
if user selects checkbox1 and checkbox2 then the code should do the enters 1 below the other J5 & J6 (considering J5 lastblank cell)

If user selects checkbox1 or checkbox2 then the code should do the enter 1 J5 (considering J5 lastblank cell)

J5 and J6 is just an eg.

any input.

 

[Michael M]

OK, change to

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
LastRow = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
If CheckBox1.value = True Then ws.Range("J" & LastRow).Value = Me.Label11.Caption
If CheckBox2.value = True Then ws.Range("J" & LastRow).Value = Me.Label12.Caption

 

[vmjan02]

Michael,

The code will only satisfy if the user selects checkbox1 or checkbox2 and it will not capture the value of checkbox1 caption if user selects checkbox1 and checkbox2 as it will only capture the caption of checkbox2

I have already tried this code, but no luck.


If you want you can try in your system you will get an idea.
If CheckBox1.value = True Then ws.Range("J" & LastRow).Value = Me.Label11.Caption
If CheckBox2.value = True Then ws.Range("J" & LastRow).Value = Me.Label12.Caption

 

[vmjan02]

Just for understanding I am using this below code, and I have 32 checkbox so looking out for an alternative. Or otherwise I have to do 32 if statements. I hope now I am clear as what is needed.

If Me.CheckBox1.Value = True Then
Call lastrecord
ActiveCell.Value = Me.Label11.Caption
End If


If Me.CheckBox2.Value = True Then
Call lastrecord
ActiveCell.Value = Me.Label12.Caption
End If




If Me.CheckBox3.Value = True Then
Call lastrecord
ActiveCell.Value = Me.Label13.Caption
End If

Sub lastrecord()
ActiveSheet.Range("J5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End Sub