Copying rows that have combo boxes so that the new combo box

[Jonas]

I am making a form and have run into some problems.

There are several rows in the form, and the user has the possibility to add rows if he wants to (with a macro). Some rows have combo boxes or check boxes in them, and problems occur when these are copied. All contents and formatting works ok, but the new combo box is an exact copy of the original box. That is, when you copy the formula C2+D2 one row down it will change to C3+D3, but the combo box will still have the SAME cell link as the original combo box.

So what I´m asking is how to make the new control unique (the new control has the same name as the original one) and how to make its cell link to be the same as the original's, except one row below.

 

[Jonas]

Hmmm, maybe this was impossible to do.

So I´ll ask a simpler question instead:

How do you hide a control box (like a button or a combo box), so that its hidden/visible state depends on the visible/hidden state of a certain row?

 

[Jim North]

Found this posted elsewhere:
This assumes that:
- You are using an ActiveX combo box (from the Control ToolBox, not
the Forms toolbar). If not, I suggest changing to to one;
- The ComboBox is located on sheet2, and is named ComboBox1,
- The Check box is located on the same sheet and is named
CheckBox1.

Change references accordingly to suit your needs.

(Note that in real life I'd use sheet codenames instead of sheet names
(which the user can change), but that's a subject for another thread.)

Private Sub ComboBox1_Change()
If ActiveWorkbook.Sheets("Sheet2").ComboBox1.Value = 1 Then
ActiveWorkbook.Sheets("Sheet2").CheckBox1.Visible = False
ElseIf ActiveWorkbook.Sheets("Sheet2").ComboBox1.Value = 2 Then
ActiveWorkbook.Sheets("Sheet2").CheckBox1.Visible = True
End If

End Sub

---------------------------------------------------------
Hank Scorpio
apolloXVIII@ozemail.com.au Change XVIII to 18 for real address.