| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete range on another sheet when checkbox is unchecked
I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete range on another sheet when checkbox is unchecked
Maybe...
Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy _ Worksheets("OrderForm").Range("A1") else worksheets("Orderform").range("a1").resize(1,6).cl earcontents End If End Sub Robb27 wrote: I have an order form. Users check a box next to an item and it copies the item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete range on another sheet when checkbox is unchecked
Dave,
That works, but I was hoping it would delete the entire row. That way it would bump the other rows below it up one row. I tried changing the "clearcontents" to deleterow, but that didn't work. Thanks Dave Robb "Dave Peterson" wrote: Maybe... Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy _ Worksheets("OrderForm").Range("A1") else worksheets("Orderform").range("a1").resize(1,6).cl earcontents End If End Sub Robb27 wrote: I have an order form. Users check a box next to an item and it copies the item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete range on another sheet when checkbox is unchecked
change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents to worksheets("Orderform").range("a1").entirerow.dele te Robb27 wrote: Dave, That works, but I was hoping it would delete the entire row. That way it would bump the other rows below it up one row. I tried changing the "clearcontents" to deleterow, but that didn't work. Thanks Dave Robb "Dave Peterson" wrote: Maybe... Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy _ Worksheets("OrderForm").Range("A1") else worksheets("Orderform").range("a1").resize(1,6).cl earcontents End If End Sub Robb27 wrote: I have an order form. Users check a box next to an item and it copies the item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete range on another sheet when checkbox is unchecked
Dave,
That worked beautifully, for the first row on the new sheet, but...something I didn't think of is happening. Ok: When it deletes the row and moves all the other rows up one...it changes the range for the other checkboxes below the one we just deleted. Now, the remove code you gave me works...but doesn't work because when the row moved up, it's physical location changed in the sheet, now the code points to a different row (one below). Can it check to see where it is before deleting and then delete the row? I hope that makes sense. Here is the code I'm using: Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A3:F3").Copy Worksheets("OrderForm").Range("A2") Else Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete End If End Sub Private Sub CheckBox2_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A4:F4").Copy Worksheets("OrderForm").Range("A3") Else Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete End If End Sub Private Sub CheckBox3_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A5:F5").Copy Worksheets("OrderForm").Range("A4") Else Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete End If End Sub Thanks Dave. I hope I'm painting the picture properly. Robb "Dave Peterson" wrote: change this: worksheets("Orderform").range("a1").resize(1,6).cl earcontents to worksheets("Orderform").range("a1").entirerow.dele te Robb27 wrote: Dave, That works, but I was hoping it would delete the entire row. That way it would bump the other rows below it up one row. I tried changing the "clearcontents" to deleterow, but that didn't work. Thanks Dave Robb "Dave Peterson" wrote: Maybe... Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy _ Worksheets("OrderForm").Range("A1") else worksheets("Orderform").range("a1").resize(1,6).cl earcontents End If End Sub Robb27 wrote: I have an order form. Users check a box next to an item and it copies the item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Delete range on another sheet when checkbox is unchecked
I'm confused.
It sure looks like you really want to use .clearcontents. That way the row will stay, but will be empty. Robb27 wrote: Dave, That worked beautifully, for the first row on the new sheet, but...something I didn't think of is happening. Ok: When it deletes the row and moves all the other rows up one...it changes the range for the other checkboxes below the one we just deleted. Now, the remove code you gave me works...but doesn't work because when the row moved up, it's physical location changed in the sheet, now the code points to a different row (one below). Can it check to see where it is before deleting and then delete the row? I hope that makes sense. Here is the code I'm using: Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A3:F3").Copy Worksheets("OrderForm").Range("A2") Else Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete End If End Sub Private Sub CheckBox2_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A4:F4").Copy Worksheets("OrderForm").Range("A3") Else Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete End If End Sub Private Sub CheckBox3_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A5:F5").Copy Worksheets("OrderForm").Range("A4") Else Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete End If End Sub Thanks Dave. I hope I'm painting the picture properly. Robb "Dave Peterson" wrote: change this: worksheets("Orderform").range("a1").resize(1,6).cl earcontents to worksheets("Orderform").range("a1").entirerow.dele te Robb27 wrote: Dave, That works, but I was hoping it would delete the entire row. That way it would bump the other rows below it up one row. I tried changing the "clearcontents" to deleterow, but that didn't work. Thanks Dave Robb "Dave Peterson" wrote: Maybe... Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy _ Worksheets("OrderForm").Range("A1") else worksheets("Orderform").range("a1").resize(1,6).cl earcontents End If End Sub Robb27 wrote: I have an order form. Users check a box next to an item and it copies the item to another page (the form). I want them to be able to uncheck the box and have it "undo" what the checkmark does. (deletes the item order for that item). Here is the code I'm using to move the item to the order form. Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Worksheets("PartsSheet1").Range("A2:F2").Copy Worksheets("OrderForm").Range("A1") End If End Sub Thank you. Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
| How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
| How to copy records containing a specific date range to new sheet? | Excel Worksheet Functions | |||
| not delete worksheets from names in a range | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Hybrid Mode
