Hide a command box when row is hidden

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I have a worksheet where I’m attempting to do a couple of things –

    • Use checkboxes to allow the user to choose varying styles of phones to configure. When they check a box, this will unhide rows within the worksheet specific to the selection they made. This functionality is in row 5 of the attached and is working.
    • When the checkbox is unchecked I need it to hide the rows (this works) along with the command button within that row (this is not working). If I group the command button with the picture, then it will hide/unhide with the row, but the functionality of the command button stops working. Is there a way to hide the command button when the corresponding checkbox is unchecked?
    • When a user selects the command button, they are prompted to enter a numerical QTY and based on that QTY, copy cells E10:I24 and paste it as many times as the QTY they entered - starting in cell K10. On the attached, you can see how it should look if the user entered 4. This part I can’t figure out – the code I used was from another worksheet that had similar functionality - but row based. This needs to be column based and row dependent on the various items..

    Thanks for any help/guidance

  • Code
    Private Sub CheckBox1_Click()
        [27:46].EntireRow.Hidden = Not CheckBox1
        Me.CommandButton2.Visible = Me.CheckBox1
    End Sub
    Private Sub CheckBox2_Click()
            [7:26].EntireRow.Hidden = Not CheckBox2
    Me.CommandButton1.Visible = Me.CheckBox2
    End Sub
  • Copy your template

  • Thank you, Dave. I inserted both codes and it sort of worked. Here is what I’m experiencing –

    See the attached –

    When I save/close the file and then reopen – all the commandbuttons are stacked down around row 147 instead of above the various pictures where they were – and I only see the commandbuttons when I have developer mode turned on. If Developer Mode is off I don’t see any command buttons.

    When I move the command buttons back to the right locations and click on the 9608 Command Button – I get the pop-up message to add a value – for testing I entered 3 and there were 3 templates copied over. I then reselected the 9608 command button and entered 2 – as if I was adding 2 more templates after the fact – in this scenario I don’t get any additional templates added.


  • Yea, I thought that after about 3 hours of beating my head against the wall - thanks for helping. The one thing I noticed is when I click on the picture (which like) and add a few templates - and then go back a add a few more - the original ones added disappear. I need them to stay -

  • Changed so cells don't get cleared.

  • The revised code works perfectly – thanks.

    However, I think I broke it – I cleaned up the formatting and removed some rows – I went into the code line “Set rng = .Range("E20:I34")” and updated the cell range - now it only copies 1 template (regardless of the QTY I enter) and pastes it into row 10 column AG. I think the error may lie in the line “rng.Copy .Range("XFD10").End(xlToLeft).Offset(0, 5)” – I don’t understand the XFD10.

    I need the same behavior for all the other phone types – which I can copy/paste from what you provided and update the appropriate lines.


  • Thanks and it's always the simplest things - :)

    One more question – when I enter a QTY for templates needed I get 1 more than requested. This is simply due to the template being used – my thought is to hide the template columns E:I so the user will always have a clean template to copy from. Then when they enter 2 – they only see 2. When I hide these columns and then run the MACRO I get a run time error 1004.

  • Hi Dave,

    I’m hoping you could take a quick look at the attached and figure out why one copy/paste macros aren’t working like all the others. Cells E:I contain the cells that I want copied when a user selects the picture for that phone type – the cells are pasted starting in column K and continue out depending on how many templates the user requests. In the attached, I selected 2 for each of the phone types. In rows 69:83 the macro is pasting the template starting in column J and not column K like all the others. I’ve been through the code with my limited knowledge, and the macro appears to be the same (except for the row numbers), but the output is not the same.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!