mardi 18 juillet 2023

Excel Macro Enabled Worksheet - How to clear grouped checkboxes in a specific row and column without clearing all checkboxes in worksheet?

Current Issue: I need help understanding how I can clear a specific row and columns in that row, that contain grouped form control checkboxes. I want to be able to clear those checkboxes so that a user sees a copy of the row above, but all the inputs/text are cleared to let them add a new entry in the row - see below for more detail...

Please Note: I've looked online at a number of different bits of code that kind of do something similar, but most seem to uncheck all the checkboxes on a worksheet and I don't have the coding knowledge to write/repurpose that code to suit what I need.

I repurposed someone elses code I found here.

How the code is currently working: The below code is tied to the button 'Add a New Row', when this button is clicked it presents a user prompt asking the user to enter the row number of the new row to add - example of user entering a new row number, to copy row 14 down into row 15

In the newly copied row, columns A, B and D are cleared of text - ready for a user to add a new entry, which is currently working well: showing new row copied from row 14 into row 15 and clearing columns A, B and D

I also want columns C and E in this new row to have all the checkboxes cleared...they are currently just being copied from row 14 down as is with the checkboxes ticked that are selected in row 14. These checkboxes are grouped, e.g. column C checkboxes grouped as one group, column E checkboxes grouped as another group.

Is there a way I can target a specific row/column and clear the checkboxes, and do this without clearing all the checkboxes in other rows?

Important:

  1. I want to try and add this to the code I already have under the Add a New Row button code below.

  2. Row 14 is already populated (as this will be a template), so the user should add from row 15 onward. This may not be the case in other worksheets, so hopefully I can just update the row that I want to use as the template row in other worksheets.

  3. Ideally, if there was a way to update my code to throw an error if a user tries to copy rows 14 or above, that would be better. Currently, if a user selects row 14, it copies my headings row and inserts this again, which I don't want to happen:

showing headings copied again if the user tries to copy row 14

Add a New Row Button code:

Private Sub CommandButton1_Click()

    Dim rowNum As Long
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number of New Row to Add:", _
        Title:="Add New Row", Type:=1)
    Rows(rowNum).Insert Shift:=xlDown
    If Err.Number > 0 Then GoTo errH
    
    Range("A" & rowNum - 1).Resize(, 1).Copy Range("A" & rowNum)
    Range("B" & rowNum - 1).Resize(, 1).Copy Range("B" & rowNum)
    Range("C" & rowNum - 1).Resize(, 1).Copy Range("C" & rowNum)
    Range("D" & rowNum - 1).Resize(, 1).Copy Range("D" & rowNum)
    Range("E" & rowNum - 1).Resize(, 1).Copy Range("E" & rowNum)
    Range("F" & rowNum - 1).Resize(, 1).Copy Range("F" & rowNum)
        
    Range("A" & rowNum - 0).ClearContents
    Range("B" & rowNum - 0).ClearContents
    Range("D" & rowNum - 0).ClearContents

errH:
End Sub

Any help would be greatly appreciated. Thanks

ClearContents doesn't work to target the form control checkboxes, and Clear removes the underlying formatting of a column/row, which I don't want.

I had a look online but kept getting confused with how other's code was working.




Aucun commentaire:

Enregistrer un commentaire