Userform code

C

cruyff73

New Member
Joined
Oct 7, 2016
Messages
29
Hi,

I've created a userform to help data entry over multiple sheets. Which sheets are populated are chosen by ticking check boxes. The problem I have is my code copies the information to all sheets, regardless of whether the check box is ticked or not. How do I stop this happening?

this is my code (I'm learning so this has been cobbled together and possibly long winded anyway)

Private Sub Add_Click()
'Copy input values to sheet.
Dim iRow As Long
Dim ws As Worksheet
Set ws1 = Worksheets("PR")
Set WS2 = Worksheets("TLS")
Set WS3 = Worksheets("VI MOD 1")
Set ws11 = Worksheets("VI MOD 2")
Set ws4 = Worksheets("V55 KEYING")
Set ws5 = Worksheets("V55 FULL")
Set ws6 = Worksheets("V62")
Set ws7 = Worksheets("CVT")
Set ws12 = Worksheets("KFI")
Set ws8 = Worksheets("TRIAGE")
Set ws10 = Worksheets("Drivers PRNREN")
Set ws9 = Worksheets("VI Trainers")

iRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow2 = WS2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow3 = WS3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow4 = ws4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow5 = ws5.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow6 = ws6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow7 = ws7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow8 = ws8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow9 = ws9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow10 = ws10.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow11 = ws11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow12 = ws12.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

If Me.PR.Value = True Then Set ws = ThisWorkbook.Sheets("PR")
With ws
ws1.Cells(iRow1, 1).Value = Me.NameBox.Value
ws1.Cells(iRow1, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.TLS.Value = True Then Set ws = ThisWorkbook.Sheets("TLS")
With ws
WS2.Cells(iRow2, 1).Value = Me.NameBox.Value
WS2.Cells(iRow2, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.VIMOD1.Value = True Then Set ws = ThisWorkbook.Sheets("VI MOD 1")
With ws
WS3.Cells(iRow3, 1).Value = Me.NameBox.Value
WS3.Cells(iRow3, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.VIMOD2 = True Then Set ws = ThisWorkbook.Sheets("VI MOD 2")
With ws
ws11.Cells(iRow11, 1).Value = Me.NameBox.Value
ws11.Cells(iRow11, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.V55KEY.Value = True Then Set ws = ThisWorkbook.Sheets("V55 KEYING")
With ws
ws4.Cells(iRow4, 1).Value = Me.NameBox.Value
ws4.Cells(iRow4, 2).Value = Me.TextBox1.Value
Me.Hide
End With

If Me.V55Full.Value = True Then Set ws = ThisWorkbook.Sheets("V55 FULL")
With ws
ws5.Cells(iRow5, 1).Value = Me.NameBox.Value
ws5.Cells(iRow5, 2).Value = Me.TextBox1.Value
Me.Hide
End With

If Me.V62.Value = True Then Set ws = ThisWorkbook.Sheets("V62")
With ws
ws6.Cells(iRow6, 1).Value = Me.NameBox.Value
ws6.Cells(iRow6, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.CVT.Value = True Then Set ws = ThisWorkbook.Sheets("CVT")
With ws
ws7.Cells(iRow7, 1).Value = Me.NameBox.Value
ws7.Cells(iRow7, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.KFI.Value = True Then Set ws = ThisWorkbook.Sheets("KFI")
With ws
ws12.Cells(iRow12, 1).Value = Me.NameBox.Value
ws12.Cells(iRow12, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.Triage.Value = True Then Set ws = ThisWorkbook.Sheets("TRIAGE")
With ws
ws8.Cells(iRow8, 1).Value = Me.NameBox.Value
ws8.Cells(iRow8, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.DRIVERSPRNREN.Value = True Then Set ws = ThisWorkbook.Sheets("DRIVERS PRNREN")
With ws
ws10.Cells(iRow10, 1).Value = Me.NameBox.Value
ws10.Cells(iRow10, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.VITrainers.Value = True Then Set ws = ThisWorkbook.Sheets("VI TRAINERS")
With ws
ws9.Cells(iRow9, 1).Value = Me.NameBox.Value
ws9.Cells(iRow9, 2).Value = Me.TextBox1.Value
Me.Hide
End With

'Clear input controls.
Me.NameBox.Value = ""
Me.TextBox1.Value = ""
End Sub


Much appreciated
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Re: Userform code help

Try rewriting all your if statements like this
Code: 
If Me.PR.Value = True Then
    Set ws = ThisWorkbook.Sheets("PR")
    With ws
        .Cells(iRow1, 1).Value = Me.NameBox.Value
        .Cells(iRow1, 2).Value = Me.TextBox1.Value
        Me.Hide
    End With
ElseIf Me.TLS.Value = True Then
    Set ws = ThisWorkbook.Sheets("TLS")
    With ws
        .Cells(iRow2, 1).Value = Me.NameBox.Value
        .Cells(iRow2, 2).Value = Me.TextBox1.Value
        Me.Hide
    End With
ElseIf etc
'
'
'
End If
'Clear input controls.
Me.NameBox.Value = ""
Me.TextBox1.Value = ""
End Sub
 
Upvote 0
Re: Userform code help

cruyff73 said:
Hi,

I've created a userform to help data entry over multiple sheets. Which sheets are populated are chosen by ticking check boxes. The problem I have is my code copies the information to all sheets, regardless of whether the check box is ticked or not. How do I stop this happening?

this is my code (I'm learning so this has been cobbled together and possibly long winded anyway)

Private Sub Add_Click()
'Copy input values to sheet.
Dim iRow As Long
Dim ws As Worksheet
Set ws1 = Worksheets("PR")
Set WS2 = Worksheets("TLS")
Set WS3 = Worksheets("VI MOD 1")
Set ws11 = Worksheets("VI MOD 2")
Set ws4 = Worksheets("V55 KEYING")
Set ws5 = Worksheets("V55 FULL")
Set ws6 = Worksheets("V62")
Set ws7 = Worksheets("CVT")
Set ws12 = Worksheets("KFI")
Set ws8 = Worksheets("TRIAGE")
Set ws10 = Worksheets("Drivers PRNREN")
Set ws9 = Worksheets("VI Trainers")

iRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow2 = WS2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow3 = WS3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow4 = ws4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow5 = ws5.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow6 = ws6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow7 = ws7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow8 = ws8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow9 = ws9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow10 = ws10.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow11 = ws11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iRow12 = ws12.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

If Me.PR.Value = True Then Set ws = ThisWorkbook.Sheets("PR")
With ws
ws1.Cells(iRow1, 1).Value = Me.NameBox.Value
ws1.Cells(iRow1, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.TLS.Value = True Then Set ws = ThisWorkbook.Sheets("TLS")
With ws
WS2.Cells(iRow2, 1).Value = Me.NameBox.Value
WS2.Cells(iRow2, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.VIMOD1.Value = True Then Set ws = ThisWorkbook.Sheets("VI MOD 1")
With ws
WS3.Cells(iRow3, 1).Value = Me.NameBox.Value
WS3.Cells(iRow3, 2).Value = Me.TextBox1.Value
Me.Hide

End With
If Me.VIMOD2 = True Then Set ws = ThisWorkbook.Sheets("VI MOD 2")
With ws
ws11.Cells(iRow11, 1).Value = Me.NameBox.Value
ws11.Cells(iRow11, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.V55KEY.Value = True Then Set ws = ThisWorkbook.Sheets("V55 KEYING")
With ws
ws4.Cells(iRow4, 1).Value = Me.NameBox.Value
ws4.Cells(iRow4, 2).Value = Me.TextBox1.Value
Me.Hide
End With

If Me.V55Full.Value = True Then Set ws = ThisWorkbook.Sheets("V55 FULL")
With ws
ws5.Cells(iRow5, 1).Value = Me.NameBox.Value
ws5.Cells(iRow5, 2).Value = Me.TextBox1.Value
Me.Hide
End With

If Me.V62.Value = True Then Set ws = ThisWorkbook.Sheets("V62")
With ws
ws6.Cells(iRow6, 1).Value = Me.NameBox.Value
ws6.Cells(iRow6, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.CVT.Value = True Then Set ws = ThisWorkbook.Sheets("CVT")
With ws
ws7.Cells(iRow7, 1).Value = Me.NameBox.Value
ws7.Cells(iRow7, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.KFI.Value = True Then Set ws = ThisWorkbook.Sheets("KFI")
With ws
ws12.Cells(iRow12, 1).Value = Me.NameBox.Value
ws12.Cells(iRow12, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.Triage.Value = True Then Set ws = ThisWorkbook.Sheets("TRIAGE")
With ws
ws8.Cells(iRow8, 1).Value = Me.NameBox.Value
ws8.Cells(iRow8, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.DRIVERSPRNREN.Value = True Then Set ws = ThisWorkbook.Sheets("DRIVERS PRNREN")
With ws
ws10.Cells(iRow10, 1).Value = Me.NameBox.Value
ws10.Cells(iRow10, 2).Value = Me.TextBox1.Value
Me.Hide
End With
If Me.VITrainers.Value = True Then Set ws = ThisWorkbook.Sheets("VI TRAINERS")
With ws
ws9.Cells(iRow9, 1).Value = Me.NameBox.Value
ws9.Cells(iRow9, 2).Value = Me.TextBox1.Value
Me.Hide
End With

'Clear input controls.
Me.NameBox.Value = ""
Me.TextBox1.Value = ""
End Sub


Much appreciated
Click to expand...

I've got a couple things for you that maybe you can take, and rework your code.

First: Why in each "If" statement do you set ws to the same worksheet as the other worksheet variable you're using in that if statement? For instance, in your last If statement, you set ws = ...Sheets("VI TRAINERS"), and then reference ws9 which is already set to that same sheet at the beginning of your code. Its redundant, and you can do away with either one or the other. You can either remove your use of "ws", or the individual worksheet variables. I would take out all the individual variables.

Second: You're not using the "With" statement correctly. I would just take all of your "Withs" out.

So, to get you started, try something like this:

Code: 
Private Sub Add_Click()
    'Copy input values to sheet.
    Dim iRow As Long
    Dim ws As Worksheet

If Me.PR.Value = True Then 
    Set ws = ThisWorkbook.Sheets("PR")
    iRow = ws..Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ws.Cells(iRow, 1).Value = Me.NameBox.Value
        ws.Cells(iRow, 2).Value = Me.TextBox1.Value
End If

If Me.TLS.Value = True Then
    Set ws = ThisWorkbook.Sheets("TLS") 
     iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row    
         ws.Cells(iRow, 1).Value = Me.NameBox.Value
         ws.Cells(iRow, 2).Value = Me.TextBox1.Value
End If

Do that for each of you checkboxes, then just put your Me.Hide before the End Sub.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
If textbox is empty, do not copy the value to worksheet
Replies
1
Views
248
NoSparks
N
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
273
ItalianPlatinum
I
hendrikbez
  • Question
Cannot get to edit on my user form with 1 to 5 members on it
Replies
1
Views
74
Anthony47
A
S
  • Question
VBA Help - Need listbox to scroll to top when opened
Replies
1
Views
337
rlv01
R
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
226
amoverton2
A

Forum statistics

Threads
1,215,167
Messages
6,123,401
Members
449,098
Latest member
ArturS75

Share this page

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
Back
Top