Adding Checkbox If then Code

[Boots6]

Hello,

I have a a Form in a chart that allows you to add values. Currently it automatically adds a blank row after the new values are entered in the chart. Here is the code:

addslab macro
'

'
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.TextBox1
For i = 1 To k
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -86).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -61).Range("A1").FormulaR1C1 = "=feet(RC[62])*12"
ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "SLAB"
ActiveCell.Offset(1, 0).Range("A1").Select
Next i


ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

End Sub

I've just added check boxes on the form next to each category, "addslab" being one, and I want to make it so if the check box is checked, it will not add a row. If it is unchecked, it will add a row. Do I write this code under each check box's sub or can I add it in with each category's macro somehow? Thanks!

 

[Andrzejek]

addslab macro

    Dim i As Long
    i = 1[blue]
If your check box is NOT checked Then[/blue]
    For i = 1 To AddHorizontalItemForm.TextBox1
    ActiveCell.Rows("1:1").EntireRow.Copy
    ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
    ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
    ActiveCell.Offset(0, -86).Range("A1").FormulaR1C1 = "=RC[89]"
    ActiveCell.Offset(0, -61).Range("A1").FormulaR1C1 = "=feet(RC[62])*12"
    ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "SLAB"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    
    
    ActiveCell.Rows("1:1").EntireRow.Copy
    ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False[blue]
End If[/blue]
      
End Sub

Have fun.

---- Andy

 

[Boots6]

I am trying to get this to work, but I don't think I'm understanding. I have a form with several different Text Boxes - the example that follows is one for "add thick slab."

Sub addslabthk()
Application.ScreenUpdating = False
'
'addslabthk macro
'

'
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.TextBox2
For i = 1 To k
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -67).Range("A1").FormulaR1C1 = "=RC[70]"
ActiveCell.Offset(0, -61).Range("A1").FormulaR1C1 = "=feet(RC[62])*12"
ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "slab Thick"
ActiveCell.Offset(1, 0).Range("A1").Select
Next i

ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

End Sub

I've put check boxes next to each Text Box. If checked, I want there to be no row inserted between that category and the next, if it is not checkmarked, I want it to include the extra row.
Here is what I kind of came up with, but regardless if it is checked or not, it is leaving out the row. Ideas?


Sub addslab()
Application.ScreenUpdating = False
'
'addslab macro
'
'
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.TextBox1
For i = 1 To k
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -86).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -61).Range("A1").FormulaR1C1 = "=feet(RC[62])*12"
ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "SLAB"
ActiveCell.Offset(1, 0).Range("A1").Select
Next i



End Sub
Private Sub CheckBox1_Click()
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.CheckBox1
For i = 1 To k
If CheckBox1.Value = False Then
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
If CheckBox1.Value = True Then
Next i

End Sub

 

[SkipVought]

Hi,

Currently it automatically adds a blank row after the new values are entered in the chart.

I do not understand. You cannot enter values in a chart, at least that's not a usual method of assigning data to a series.

Please explain.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

On the Excel, there is a button that says "Add Item." When pressed, a form comes up with 15 different categories of types of concrete, i.e., Slab, Thick Slab, Beam....etc. Next to each category are text boxes to input the number of each type you would like to add. Here is the code (2 in a row):

Sub addslabthk()
Application.ScreenUpdating = False
'
'addslabthk macro
'

'
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.TextBox2
For i = 1 To k
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -67).Range("A1").FormulaR1C1 = "=RC[70]"
ActiveCell.Offset(0, -61).Range("A1").FormulaR1C1 = "=feet(RC[62])*12"
ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "slab Thick"
ActiveCell.Offset(1, 0).Range("A1").Select
Next i

ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

End Sub

Sub addslabmisc()
Application.ScreenUpdating = False
'
'addslabmisc macro
'

'
Dim i, k As Long
i = 1
k = AddHorizontalItemForm.TextBox3
For i = 1 To k
ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(0, -88).Range("A1").FormulaR1C1 = "=RC[89]"
ActiveCell.Offset(0, -67).Range("A1").FormulaR1C1 = "=RC[70]"
ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "SLAB MISC"
ActiveCell.Offset(1, 0).Range("A1").Select
Next i

ActiveCell.Rows("1:1").EntireRow.Copy
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
_______________________________________________________________

As you see at the end of every entry it adds a row in between the next entry. I need to insert checkboxes into the form so we can check the box indicating if we would like a row inserted between each category or not. Make sense? I'm trying to attach a picture of the screen so you can see what the chart and the form look like but it won't let me. Thanks,

 

[Andrzejek]

Is that what you are after?

   ...
    Next i
[blue]
If CheckBox1.Value = False Then[/blue]
    ActiveCell.Rows("1:1").EntireRow.Copy
    ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False[blue]
End If[/blue]

End Sub

Have fun.

---- Andy

 

[Boots6]

I tried that before, except I put the "If CheckBox1.Value = True" also. I want to try without the "True" now, but when I try to run it, it highlights "CheckBox1" and says "Variable Not Defined

 

[Boots6]

It does it when I make it a new macro directly after the first and use "Private Sub CheckBox1_Click() then enter that formula, but then it does not leave a space between objects on the chart when the checkmark is not selected.

 

[Andrzejek]

it highlights "CheckBox1" and says "Variable Not Defined"

What is the name of your CheckBox? If you named it something different, you need to place its name in the code.

I want to try without the "True" now

You can do it. Just:
[tt]
If chkYourCheckBoxNameHere.Value Then[/tt]

and use "Private Sub CheckBox1_Click() then enter that formula

Because that's the wrong place to put your formula.

You do not need anything in CheckBox1_Click event, you just need to check if your checkbox is checked or not before you place an empty row in your code.

Have fun.

---- Andy

 

[Boots6]

I got it! Thank you!!