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

Jonas

New Member
Joined
Sep 3, 2002
Messages
2
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Jonas

New Member
Joined
Sep 3, 2002
Messages
2
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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.
 

Forum statistics

Threads
1,144,510
Messages
5,724,786
Members
422,579
Latest member
Lekha mohanty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top