Copying an existing row which also copies the checkbox with its code

0 votes

My table must grow by 1 row when I touch a button. but also the code for a checkbox, which must be copied. That doesn't work this way, so how do I do it? The mistake is on the bolded line.

enter image description here

Private Sub CommandButton2_Click()
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim chkBox As CheckBox
'Determine the last row in the active worksheet
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Determine the last column you want to expand
    lastColumn = 7
'Insert a row above the last row
    Rows(lastRow + 1).Insert
'Copy the formulas from the last row to the new row
    Range(Cells(lastRow, 1), Cells(lastRow, lastColumn)).Copy Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, lastColumn))
'Copy the last checkbox with his VBA code
    Set chkBox = ActiveSheet.CheckBoxes(ActiveSheet.CheckBoxes.Count)
    chkBox.Copy
    ActiveSheet.CheckBoxes.Add(chkBox.Left, chkBox.Top + chkBox.Height + 5, chkBox.Width, chkBox.Height).Select
    ActiveSheet.Paste
End Sub
Apr 2, 2023 in Others by narikkadan
• 63,700 points
457 views

1 answer to this question.

0 votes

You can make a new checkbox object based on the old one rather than cloning the existing one. It is not possible to utilise the Copy method on a CheckBox object in VBA. SubcommandButton2 Click, private () dim final As long as dim last, row One Long Dim Column as CheckBox chkBox dark new Dim chkBoxTop As Double ChkBox As CheckBox

    'Determine the last row in the active worksheet
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Determine the last column you want to expand
    lastColumn = 7
    
    'Insert a row above the last row
    Rows(lastRow + 1).Insert
    
    'Copy the formulas from the last row to the new row
    Range(Cells(lastRow, 1), Cells(lastRow, lastColumn)).Copy Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, lastColumn))
    
    'Create a new checkbox based on the last one
    Set chkBox = ActiveSheet.CheckBoxes(ActiveSheet.CheckBoxes.Count)
    chkBoxTop = chkBox.Top + chkBox.Height + 5
    Set newChkBox = ActiveSheet.CheckBoxes.Add(chkBox.Left, chkBoxTop, chkBox.Width, chkBox.Height)
    newChkBox.Caption = chkBox.Caption
    newChkBox.Value = chkBox.Value
    
    'Copy the checkbox code behind it
    newChkBox.OnAction = chkBox.OnAction
    
End Sub

Create a new checkbox based on the last checkbox, set its caption and value to be the same, and copy the code behind it.

answered Apr 2, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

"The underlying connection was closed: An unexpected error occurred on a send." With SSL Certificate

The solution for this for me was ...READ MORE

answered Feb 18, 2022 in Others by Rahul
• 9,670 points
10,632 views
0 votes
1 answer

How to generate the "create table" sql statement for an existing table in postgreSQL

Create a table for a demo: CREATE TABLE ...READ MORE

answered Jun 20, 2022 in Others by nisha
• 2,210 points
355 views
0 votes
0 answers
0 votes
0 answers

can the font type of an Edittext,Radio Button and CheckBox be changed in Android

TextView text = (TextView) layout.findViewById(R.id.text); text.setText(msg); Typeface font = ...READ MORE

Jun 22, 2022 in Others by nisha
• 2,210 points
358 views
0 votes
1 answer

Copying and pasting from one workbook to another doesn't work

Your ranges aren't fully qualified. Excel will make ...READ MORE

answered Feb 18, 2023 in Others by narikkadan
• 63,700 points
319 views
0 votes
1 answer

Copy data with filter applied using Excel VBA

Try this: Private Sub CommandButton1_Click() Dim ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
303 views
0 votes
1 answer

Export multiple worksheets without formula with the ability to select exact sheets and location

Try this: Sub ExportSheets() Dim ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
356 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
958 views
0 votes
1 answer
0 votes
1 answer

Macro VBA code failing to execute a section of the code without an error

You get your code to make a ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
376 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP