Adding Data to diffrent worksheets from userform

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
123
I have a simple userform setup. i have written code to work on all the userform but what im havint trouble getting to work is: i have 8 diffrent worksheets that i can select what worksheet i place data into. My problem is i cant get the data to go into the first empty cell (Like A1) on the sheet i select and move on down the column as i enter new data on the sheet i select. Really what im asking i guess how do i enter data in multiple worksheets as i enter data and select the worksheet i want What i do have with the code i have written is i can get data into the first cell at the bottom of my form i have layed out on the worksheet. I will include the code i have to made that doesnt work.

Private Sub CommandButton1_Click()
TargetSheet = ComboBox1.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Value = TextBox1.Value
MsgBox ("Data is added successfully")
TextBox1.Value = ""
Worksheets("Master Sheet").Activate
Worksheets("Master Sheet").Cells(1, 1).Select
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
How about
Code:
Private Sub CommandButton1_Click()
   TargetSheet = ComboBox1.Value
   If TargetSheet = "" Then Exit Sub
   
   With Worksheets(TargetSheet)
      If .Range("A1") = "" Then
         LastRow = 1
      Else
         LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
      End If
      .Cells(LastRow, 1).Value = TextBox1.Value
      MsgBox ("Data is added successfully")
      TextBox1.Value = ""
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
End Sub
 
Upvote 0
The problem is i wanted data to start in cell A3 on each worksheet i failed to tell you that. the Data shows up in cell A1 like the code is now. Also when i go to add another name to the same sheet i select it wont go on down to the next row with new name. the code replaces the name in Cell A1. so when adding a new name to the same sheet it wont adveance to the next row so i will have 2 names and so on down the sheet.
 
Upvote 0
It would have helped if you said that originally, rather than referring to A1. ;)
How about
Code:
Private Sub CommandButton1_Click()
   TargetSheet = ComboBox1.Value
   If TargetSheet = "" Then Exit Sub
   
   With Worksheets(TargetSheet)
      LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
      If LastRow < 3 Then LastRow = 3
      .Cells(LastRow, 1).Value = TextBox1.Value
      MsgBox ("Data is added successfully")
      TextBox1.Value = ""
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
End Sub
 
Upvote 0
The problem now is when i enter a name into user form it puts it at the bottom of my worksheet (like if i have 40 rows used it put the info in row 41 cell A1. But i still want my first name in cell A3 and as i add names that name will be added to next row. If it would help i could send you the file.

Sorry about not saying cell A3

Dennis
 
Upvote 0
I don't understand what you mean. One moment your are saying "as you add names it should go to the next row", but then you also say that "If 40 rows are used, the name should still go in A3"
To me those conflict with each other.
 
Upvote 0
Sorry about the confusion.What I'm saying is when I use the user form to add data to a worksheet it puts the information from the 1 text box that the userform has in cell A41 instead of cell A3 of the selected sheet that i select from a combix.
If I need to send you the file I can. I think we are close to having this figured out. Thanks
 
Upvote 0
How will the code know where to put the value?
Should it be in A3 downwards as far as (for instance) A20?
If so what happens when all those cells are full?
 
Upvote 0
When I show the user form I have a drop down combo box that has all the possible worksheets in it. I choose the sheet that I want to put the data in . On the worksheet the data can go downwards from cells A3 thru A24. Not sure what I will do when they get full
 
Upvote 0
Ok, if you will always have data in any row below row 23, try
Code:
Private Sub CommandButton1_Click()
   targetsheet = ComboBox1.Value
   If targetsheet = "" Then Exit Sub
   
   With Worksheets(targetsheet)
      On Error GoTo NoBlanks
      .Range("A3:A24").SpecialCells(xlBlanks)(1).Value = TextBox1.Value
      On Error GoTo 0
   End With
   Worksheets("Master Sheet").Activate
   Worksheets("Master Sheet").Cells(1, 1).Select
   Exit Sub
NoBlanks:
   MsgBox "No more blanks in sheet " & targetsheet & " range A3:A24"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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