Hide a command box when row is hidden

mike_calc · Sep 24th 2020

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

davesexcel · Sep 24th 2020

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

davesexcel · Sep 24th 2020

Copy your template

Code

Private Sub CommandButton1_Click()
Dim rng As Range
'How many Templates Command Button.  Allows users to choose # of Templates.
 Set rng = Range("E10:I24")
 With Application
 .ScreenUpdating = False
 .EnableEvents = False
 End With
 Dim numrow As String, MG As Range, NR As Range, lRow As Long
 numrow = Application.InputBox("How many 9608 Phone templates are needed?", "9608 Templates", , , , , , 1)
 If numrow = "" Then Exit Sub
 If IsNumeric(numrow) Then
 For x = 1 To numrow
 If numrow > 1 Then
 rng.Copy rng.Offset(, 6 * x)
 End If
  Next
 End If
 EnableEvents = True


End Sub

Display More

mike_calc · Sep 24th 2020

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.

Thanks

davesexcel · Sep 24th 2020

Ya activeX controls suck when hiding and unhiding rows.

Here is attached, I have removed the command buttons and gave a couple options using shapes,button from forms control and actually assigning the macro to the phone.

Click the button,Shape or phone.

mike_calc · Sep 24th 2020

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 -

davesexcel · Sep 25th 2020

Changed so cells don't get cleared.

Code

Sub CopyTmplt()
Dim rng As Range, Ap As Range
Dim sh As Worksheet
Dim Num As Variant
Set sh = ActiveSheet


With sh
Set rng = .Range("E10:I24")
With Application
.ScreenUpdating = True
.EnableEvents = False
End With
rng.Select
Num = Application.InputBox("How many 9608 Phone templates are needed?", "9608 Templates")
If Num = "" Then Exit Sub
If IsNumeric(Num) Then
For x = 1 To Num
If Num > 0 Then
rng.Copy .Range("XFD10").End(xlToLeft).Offset(0, 5)
End If
Next
End If
EnableEvents = True
End With
End Sub

Display More

mike_calc · Sep 25th 2020

Thank you again - I'll run through it in a bit and let you know. Much appreciated

mike_calc · Sep 25th 2020

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

davesexcel · Sep 25th 2020

You have it copying e20:I34 but the range should be E21.

E20 is blank, so the code will keep pasting in the same cell.

mike_calc · Sep 25th 2020

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.

davesexcel · Sep 25th 2020

I don't get that result, but if you are getting 1 more than you want, you can always subtract 1 from `num`, you will have to make sure x does not equal zero though . Probably changing

if num>1

You don't want

for x=1 to 0

mike_calc · Oct 21st 2020

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.

davesexcel · Oct 21st 2020

cells E69:F69 need to be merged.

mike_calc · Oct 22nd 2020

Thanks - I looked at everything for over an hour and never thought to see if those cells were merged. Thanks Dave -

Hide a command box when row is hidden

Participate now!

Share