fixing code link two checkboxes with dynamic in column works only one row

leap out

Board Regular
Joined
Dec 4, 2020
Messages
114
Office Version
  1. 2016
  2. 2010
hi experts
I hope some body fixing my codes I have two checkboxes when I check 1 it adds only from row2 in column e and if I checkbox2 it replaces the value in row2 in column e it should add in row3,4...and so on
VBA Code:
Private Sub CheckBox1_Click()
Dim lr As Integer

lr = Range("a" & Rows.Count).End(xlUp).Row + 1
If CheckBox1.Value = True Then
CheckBox2.Value = False
Range("k1").Value = Range("k1").Value + 1
Range("e" & lr) = "paid invoice" & Range("k1").Value
End If
End Sub

Private Sub CheckBox2_Click()
Dim lr1 As Integer

lr1 = Range("a" & Rows.Count).End(xlUp).Row + 1
If CheckBox2.Value = True Then
CheckBox1.Value = False
Range("k2").Value = Range("k2").Value + 1
Range("e" & lr1) = "unpaid invoice" & Range("k2").Value
End If
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,519
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try changing following line of your code to look at column E & see if this solves your problem

Rich (BB code):
lr = Range("e" & Rows.Count).End(xlUp).Row + 1

Dave
 
Solution

leap out

Board Regular
Joined
Dec 4, 2020
Messages
114
Office Version
  1. 2016
  2. 2010

ADVERTISEMENT

hi dmt again
actually I mod my code but it gives me error I no know if you can help what I want if I select checkbox1 and the column g is filling values then add in column e and checkbox2 is unselected this works but when I select checkbox2 and column f is filling value it gives me error in this line
VBA Code:
If CheckBox2.Value = True And Range("F" & lr) <> "" Then
this is the final code
Code:
Private Sub CheckBox1_Click()
Dim lr As Integer

lr = Range("E" & Rows.Count).End(xlUp).Row + 1
If CheckBox1.Value = True And Range("G" & lr) <> "" Then
CheckBox2.Value = False
Range("k1").Value = Range("k1").Value + 1
Range("e" & lr) = "paid invoice" & Range("k1").Value
End If
End Sub

Private Sub CheckBox2_Click()
Dim lr1 As Integer

lr1 = Range("E" & Rows.Count).End(xlUp).Row + 1
If CheckBox2.Value = True And Range("F" & lr) <> "" Then
CheckBox1.Value = False
Range("k2").Value = Range("k2").Value + 1
Range("e" & lr1) = "unpaid invoice" & Range("k2").Value
End If
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,519
Office Version
  1. 2019
Platform
  1. Windows
Hi,
looking at your code you have not entered your variable name lr1 correctly in the Range

Rich (BB code):
lr1 = Range("E" & Rows.Count).End(xlUp).Row + 1
If CheckBox2.Value = True And Range("F" & lr) <> "" Then

You can overcome such errors by Declaring Option Explicit at the TOP of your code page & then the compiler will inform you of missing variables.

Dave
 

leap out

Board Regular
Joined
Dec 4, 2020
Messages
114
Office Version
  1. 2016
  2. 2010
ahh sorry I lost my focus I try learning how can I write the code
thanks again for your help ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

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
Top