Userform code

C

cruyff73

New Member
Joined
Oct 7, 2016
Messages
32
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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

G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
183
genesix
G
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
183
NdNoviceHlp
NdNoviceHlp
M
  • Solved
Transfer data from userform to spread sheet in excel
2
Replies
17
Views
298
abdelfattah
A
H
  • Question Question
Need to add column header values to this code
Replies
1
Views
72
Osvaldo Palmeiro
O
M
  • Question Question
Re-produce code to copy last row from listbox on userform to sheet
Replies
3
Views
143
MKLAQ
M

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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