VBA CheckBox in UserForm to activate two sets of code 2007

[Excelnoub]

I have a UserForm with Testboxes in it and I have the code to make this work perfect. I need an addon to this code but am lost (Section of the code...where to add it and how)


In my UserForm I have a CommandButton1 that would take all information inserted and paste it to the next available row in my Worksheet.


Here is part of my UserForm CommandButton1_Click() code:


[pre]<br />
Private Sub CommandButton1_Click()<br />
Dim rNextCl As Range<br />
Dim NextRw As Long<br />
...<br />
If CheckBox1 Then<br />
AddTo = "/A"<br />
Else: AddTo = ""<br />
End If</p>
<p>With Sheet1<br />
NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1</p>
<p> .Cells(NextRw, 1).Value = Me.TextBox1.Value & AddTo<br />
.Cells(NextRw, 2).Value = Me.TextBox2.Value<br />
.Cells(NextRw, 3).Value = Me.TextBox3.Value<br />
.Cells(NextRw, 4).Value = Me.TextBox4.Value<br />
.Cells(NextRw, 11).Value = Me.TextBox5.Value<br />
.Cells(NextRw, 12).Value = Me.TextBox6.Value<br />
.Cells(NextRw, 5).Value = Me.ComboBox1.Value<br />
.Cells(NextRw, 6).Value = Me.ComboBox2.Value<br />
End With<br />
Unload Me<br />
End Sub<br />
[/pre]


If you follow part of this code, when I hit my commandbutton it will take all the information and add it to my row. If the checkbox = true then it will add a "/A" to my Textbox1 that is located in my Column A Whatever row.


I need to add to this code.


If the checkbox is selected Add a "/A" to that Textbox but also Offset the entire row that will be inserted but add a "/B" to the offseted row in my, what will be the next available row, row underneath.


So in a picture... If checkbox is selected

My example is on my Row 5 but this could also be whatever row, all depends what is my next available row in my sheet when I call my userform...


A5 = Test/A

Next row

A6 = Test/B


The code above does the /A but I need help with the /B part...


Please help

 

[Jordan]

Hopefully I've understood your question correctly. If the checkbox is selected, you want ".../A" in the first column and information in the following columns in the next available row, AND you want the next row AFTER THAT to start with ".../B"? In this case, you could make a second AddTo type variable and append this code to the last line of your With statement:

.Cells(NextRw + 1, 1).Value = Me.TextBox1.Value & AddToB

Where AddToB is assigned above. You may need another IF/THEN around this line if it should only happen when the checkbox is selected - I wasn't clear on that part. If you do that, you probably don't need another AddTo variable since you could just concatenate "/B"

 

[Excelnoub]

Uhmmm .... not quiet


When the userform "Add" Button is selected, so my CommandButton_Click() within my userform, and the Checkbox1 is NOT checked then run as normal. So this will not run the AddTo code.


When the userform "Add" Button is selected, so my CommandButton_Click() withing my userform, and the Checkbox1 IS checked then:


Add "/A" to the Textbox1.Value and add a new row with the information (Same as the row "/A")that will be offset (Sorry about the Next Row comment I ment to say the next row under the "/A")


So if you check the box it will do the following


Test/A Test 2013/04/12

Test/B Test 2013/04/12


If it's not checked then


Test Test 2013/04/12


In the above view I only inserted column A,B and C but as you can see kind of hard making a table.

 

[Jordan]

Well, my answer hasn't changed very much. I would rewrite to look something like this:

[pre]

With Sheet1
NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(NextRw, 2).Value = Me.TextBox2.Value
.Cells(NextRw, 3).Value = Me.TextBox3.Value
.Cells(NextRw, 4).Value = Me.TextBox4.Value
.Cells(NextRw, 11).Value = Me.TextBox5.Value
.Cells(NextRw, 12).Value = Me.TextBox6.Value
.Cells(NextRw, 5).Value = Me.ComboBox1.Value
.Cells(NextRw, 6).Value = Me.ComboBox2.Value

If CheckBox1 Then
'For Test A
.Cells(NextRw, 1).Value = Me.TextBox1.Value & "/A"
'For Test B
.Cells(NextRw + 1, 1).Value = Me.TextBox1.Value & "/B"

.Cells(NextRw + 1, 2).Value = Me.TextBox2.Value
.Cells(NextRw + 1, 3).Value = Me.TextBox3.Value
.Cells(NextRw + 1, 4).Value = Me.TextBox4.Value
.Cells(NextRw + 1, 11).Value = Me.TextBox5.Value
.Cells(NextRw + 1, 12).Value = Me.TextBox6.Value
.Cells(NextRw + 1, 5).Value = Me.ComboBox1.Value
.Cells(NextRw + 1, 6).Value = Me.ComboBox2.Value
Else
.Cells(NextRw, 1).Value = Me.TextBox1.Value
End If

End With

Unload Me
End Sub

[/pre]

If I'm still not getting it, perhaps one of the ninjas will step in. Either way, it may be a good idea to upload you file so that we can better understand your problem.