Problem: Userform is inserting the same data to all three worksheets.

[Cynthia465]

Hello Members

I am new to VBA Excel Programming. I created a userform to insert data by row count (Last Name, First Name, Date Completed, Discharge Date). I want to use my userform to write data to three different worksheets "Sheet1", "Sheet2", and "Sheet3". I encountered a problem with not knowing how to properly write my code to prevent my userform from copying data the belongs on "Sheet1" from copying to "Sheet2" and "Sheet3". When my userform appear in "Sheet2" the data also writes to "Sheet1" and "Sheet3". I want to insert different data for each worksheet. Below is the code that I used for cmdOK_Click()that's copying to all Worksheets. Members your help will be very much appreciated.

Private Sub cmdOK_Click()
Dim RowCount As Long

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.txtLastName.Value
.Offset(RowCount, 1).Value = Me.txtFirstName.Value
.Offset(RowCount, 2).Value = Me.txtDateCompleted.Value
.Offset(RowCount, 3).Value = Me.txtDischargeDate.Value
End With

RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A1")
.Offset(RowCount, 0).Value = Me.txtLastName.Value
.Offset(RowCount, 1).Value = Me.txtFirstName.Value
.Offset(RowCount, 2).Value = Me.txtDateCompleted.Value
.Offset(RowCount, 3).Value = Me.txtDischargeDate.Value
End With

RowCount = Worksheets("Sheet3").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet3").Range("A1")
.Offset(RowCount, 0).Value = Me.txtLastName.Value
.Offset(RowCount, 1).Value = Me.txtFirstName.Value
.Offset(RowCount, 2).Value = Me.txtDateCompleted.Value
.Offset(RowCount, 3).Value = Me.txtDischargeDate.Value
End With

End Sub

 

[SkipVought]

HI,

I really don't understand. The SAME VARIABLES are being used to assign the data in the same four columns on each sheet.

Your prose does not match your code. What do you expect!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MiggyD]

Well, you have this ONE (1) cmdOK_Click() for all 3 Sheets. And the way you have it coded is what you are getting....

When you click on OK (see your code above) no matter what sheet it is running on; you told it to GOTO SHEET1 and put this info. After that is done, GOTO SHEET2 and put this info (again). After THAT is done, you told it to GOTO SHEET3 and put all the info on that page as well. All of this happens because it is THE SAME BUTTON!

I know you are new to "VBA Excel Programming" but: do you understand what you wrote (or programmed)?

There are many ways to write different information on different sheets. You have to decide how you want it written (like having separate forms for each sheet which might be in YOUR BEST INTEREST as you are learning programming.)

I, myself, would use validations to compare sheetnames and even go so far as using something like cmdOK(indices)_click and change setting Visible and/or Enabled and/or even using Tag if desperate.

GL with your studies
--MiggyD

After pondering the riddle (for many years I might add) I finally got the answer (inadvertently through a movie): "If a tree falls in the forest and no one is around, does it make a sound?"

 

[SkipVought]

Maybe the FIRST thing you ought to do is clearly state what your objective is, that is the task that your boss gave you before you even thought about programming anything.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

How do you decide what sheet the data is "supposed" to go to?

 

[zelgar]

You first indicate that you want the macro to copy information from the form into the three Sheets (e.g., Sheet1, Sheet2 and Sheet3). Then you indicate that you don't want the information to be copies to the other sheets.

Your code, as written will copy the information to all of the three different sheets. It sounds like you need to put in a variable on your form (e.g., checkboxes, button) to indicate what sheet(s) you want the put the data. Then you can use IF statements or other ways to select which part(s) of your current macro to populate the intended sheets.

 

[SkipVought]

I hear echoes guys.

How about waiting for the OP to clarify the things they have already been posed?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cynthia465]

Thank you so much MiggyD I tried what you suggested. I used a separate form for each sheet, actually I used a MultiPage form. Different names and dates will be inserted in my worksheets. I forgot to mention that this project is for my job. We have clients that have completed different groups, so I rename sheet1 (BudgetingGroup)..etc. I finally finished my program today, and it feels great. This has inspired me to go a step further.

Once again thank you so much!!!

 

[MiggyD]

Umm, OK??? Glad to have helped?? Although, I'm not sure how. Anyways...

Sorry if by "studies" you thought I thought you were a student. That was not the case. I am self-taught in several languages. Currently 'studying' php on my own.

Also, I am confused but I thought you might have gone with using 3 command buttons on the same form with separate code for each sheet (essentially c/p from your cmdOK()_Click) far less typing, mostly deleting the excess.

I kept referencing the button issue and though you may do something like:

[pre]
Private sub cmdOK1()_click
'
'--sends info to Sheet 1
'
Dim RowCount As Long

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.txtLastName.Value
.Offset(RowCount, 1).Value = Me.txtFirstName.Value
.Offset(RowCount, 2).Value = Me.txtDateCompleted.Value
.Offset(RowCount, 3).Value = Me.txtDischargeDate.Value
End With
'
' -- once done, CALL another SUB TO clear all textboxes
' -- re-useable code is nice.
end sub


Private sub cmdOK2()_click
'
'-- adds data to Sheet 2
'
Dim RowCount As Long

RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A1")
.Offset(RowCount, 0).Value = Me.txtLastName.Value
.Offset(RowCount, 1).Value = Me.txtFirstName.Value
.Offset(RowCount, 2).Value = Me.txtDateCompleted.Value
.Offset(RowCount, 3).Value = Me.txtDischargeDate.Value
End With
'
' -- once done, CALL another SUB TO clear whatever needs cleaning
' -- re-useable code again.
end sub

etc..
[/pre]

It would have worked on your one Form and it would have been a very very quick fix.

Well, it works for you and you know how to tweak it for future changes then that's all that matters.

In all seriousness, Good For You!! for getting past this hurdle. Just do what's easier for you with what you know. Trust me as you learn more, your Forms and code will start becoming more complex. DON'T FORGET TO PUT CODING COMMENTS! Not only for others but for you too. You'll be glad you did, one day.

Good Luck.
--MiggyD

After pondering the riddle (for many years I might add) I finally got the answer (inadvertently through a movie): "If a tree falls in the forest and no one is around, does it make a sound?"