Check Box

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,132
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Last edited:
Upvote 0
Yes, it should always update the last cell in column J irrespective if the user selects checkbox1 or checkbox2 or checkbox3.
 
Upvote 0
Yeah....I'm a little confused as well...is this what you want

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Ok, so try

Code:
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
 
Upvote 0
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.
 
Upvote 0
OK, change to

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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