Userform sends to two worksheets - Positioning issues

J

joeforton

New Member
Joined
Sep 8, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I have created a VBA Userform to send data to the main database worksheet (Database) as well as a table on a new worksheet (New Entries) that is used to temporarily store information until after the Userform is closed. The data from the Userform correctly adds to the database worksheet (Database) in the correct position, but on the second worksheet (New Entries) the Userform mimics the row position of sheet1 (Database) instead of starting at the top row of the table. How can I get the Userform (once opened) to start at the top row of the sheet2 (New Entries) table? I may have up to 19 data rows in this table range (A3:M21).

Below is the code that works perfectly well, except for the positioning of the "New Entries" worksheet table:




Sub Reset()

Dim iRow As Long

iRow = [Counta(Database!A:A)] 'Identifying the last row


With frmForm

.txtLegalName.Value = ""
.txtTransitionDate.Value = ""
.txtAccountID.Value = ""
.optCAM.Value = False
.optAM.Value = False
.txtCurrentManager.Value = ""
.txtTransitioningTo.Value = ""

.cmbDivision.Clear

.cmbDivision.AddItem "Brevard"
.cmbDivision.AddItem "Gainesville"
.cmbDivision.AddItem "Jacksonville"
.cmbDivision.AddItem "Ocala"
.cmbDivision.AddItem "Orlando"
.cmbDivision.AddItem "Sarasota"
.cmbDivision.AddItem "Tampa"
.cmbDivision.AddItem "Volusia"


.cmbAssociationType.Clear

.cmbAssociationType.AddItem "SFH"
.cmbAssociationType.AddItem "TownHomes"
.cmbAssociationType.AddItem "Mixed"
.cmbAssociationType.AddItem "Condo"
.cmbAssociationType.AddItem "Commercial"
.cmbAssociationType.AddItem "POA"
.cmbAssociationType.AddItem "Villas"
.cmbAssociationType.AddItem "Other"



.txtUnitCount.Value = ""
.optFull.Value = False
.optActOnly.Value = False
.txtLastManagerChange.Value = ""
.txtCAMSenior.Value = ""
.txtAMSenior.Value = ""


.lstDatabase.ColumnCount = 13
.lstDatabase.ColumnHeads = True


.lstDatabase.ColumnWidths = "125,60,55,25,40,50,50,60,25,45,40"



If iRow > 1 Then

.lstDatabase.RowSource = "Database!A2:M" & iRow
Else

.lstDatabase.RowSource = "Database!A2:M2"




End If




End With


End Sub


Sub Submit()

Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Database")

iRow = [Counta(Database!A:A)] + 1


With sh


.Cells(iRow, 1) = iRow + 1

.Cells(iRow, 1) = frmForm.txtLegalName.Value

.Cells(iRow, 2) = frmForm.txtTransitionDate.Value

.Cells(iRow, 3) = frmForm.txtAccountID.Value

.Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")

.Cells(iRow, 5) = frmForm.txtCurrentManager.Value

.Cells(iRow, 6) = frmForm.txtTransitioningTo.Value

.Cells(iRow, 7) = frmForm.cmbDivision.Value

.Cells(iRow, 8) = frmForm.cmbAssociationType.Value

.Cells(iRow, 9) = frmForm.txtUnitCount.Value

.Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")

.Cells(iRow, 11) = frmForm.txtLastManagerChange.Value

.Cells(iRow, 12) = frmForm.txtCAMSenior.Value

.Cells(iRow, 13) = frmForm.txtAMSenior.Value

End With






Set sh = ThisWorkbook.Sheets("New Entries")

'Will enter on top row but will then be overwritten
iRow = [Counta(New Entries!A)] + 2


'Will enter all data but in middle of table (mimics database positioning)
'Range("A" & Rows.Count).End(xlUp).Offset(1).Select


With sh


.Cells(iRow, 1) = iRow - 1

.Cells(iRow, 1) = frmForm.txtLegalName.Value

.Cells(iRow, 2) = frmForm.txtTransitionDate.Value

.Cells(iRow, 3) = frmForm.txtAccountID.Value

.Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")

.Cells(iRow, 5) = frmForm.txtCurrentManager.Value

.Cells(iRow, 6) = frmForm.txtTransitioningTo.Value

.Cells(iRow, 7) = frmForm.cmbDivision.Value

.Cells(iRow, 8) = frmForm.cmbAssociationType.Value

.Cells(iRow, 9) = frmForm.txtUnitCount.Value

.Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")

.Cells(iRow, 11) = frmForm.txtLastManagerChange.Value

.Cells(iRow, 12) = frmForm.txtCAMSenior.Value

.Cells(iRow, 13) = frmForm.txtAMSenior.Value


End With




End Sub





Sub Show_Form()

frmForm.Show

End Sub
 

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
does calculating iRow this way make any difference ?

VBA Code: 
Set sh = ThisWorkbook.Sheets("New Entries")

With sh
    
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 2
    On Error GoTo 0         're-enable error notification
    
    If iRow = 0 Then iRow = 2
    
    ' the rest of your code
    
End With
 
Upvote 0
NoSparks said:
does calculating iRow this way make any difference ?

VBA Code: 
Set sh = ThisWorkbook.Sheets("New Entries")

With sh
   
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 2
    On Error GoTo 0         're-enable error notification
   
    If iRow = 0 Then iRow = 2
   
    ' the rest of your code
   
End With
Click to expand...
Unfortunately, the userform just overwrites the first entry. In other words, I open the userform add data, hit submit, go back to the same userform, add new data, hit submit and only the second dataset is entered on the table. The data is entered on the correct first row, but then it is overwitten by each subsequent userform submission.
 
Upvote 0
Each time you hit the submit button the last row will be recalculated so you must have something else going on
any chance you can share your workbook (without sensitive data)?

Mind if I ask why you write iRow +1 to .cells(iRow, 1)
and then immediately overwrite that with frmForm.txtLegalName.Value ?
 
Upvote 0
NoSparks said:
Each time you hit the submit button the last row will be recalculated so you must have something else going on
any chance you can share your workbook (without sensitive data)?

Mind if I ask why you write iRow +1 to .cells(iRow, 1)
and then immediately overwrite that with frmForm.txtLegalName.Value ?
Click to expand...
Thanks so much for your assistance. I can upload my workbook (without anything sensitive) but I'm not sure how to do that in this forum. My userform actually has two buttons; one to Save the data and go on to the next record on the userform and one button to Email the temporary table data on Sheet2 (New Entries). Everything works great, except for that table where data is overwritten instead of being entered on to the next available empty row. To answer your question I used the same code for Sheet1 (Database) as Sheet2 (New Entries). I figured if it worked on one sheet, why not the next? I was wrong apparently. I am missing a line of code that tells the userform to go to the next row on the Sheet2 (New Entries). I can't figure that part out.
 
Upvote 0
you can't upload files to this forum
There are free file sharing sites such as box.com and Dropbox where you can upload your file then post the share link back here on the forum.
I use box.com with no issues, it's free for individual use.
 
Upvote 0
see if my re-write of your submit button works for you
VBA Code: 
Sub Submit()
    
    Dim iRow As Long

' For writing to Database sheet
With ThisWorkbook.Sheets("Database")
    ' find the last used row then add 1, this will write to the next line
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
    On Error GoTo 0         're-enable error notification
    If iRow = 0 Then iRow = 1
    
    '.Cells(iRow, 1) = iRow + 1
    .Cells(iRow, 1) = frmForm.txtLegalName.Value
    .Cells(iRow, 2) = frmForm.txtTransitionDate.Value
    .Cells(iRow, 3) = frmForm.txtAccountID.Value
    .Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")
    .Cells(iRow, 5) = frmForm.txtCurrentManager.Value
    .Cells(iRow, 6) = frmForm.txtTransitioningTo.Value
    .Cells(iRow, 7) = frmForm.cmbDivision.Value
    .Cells(iRow, 8) = frmForm.cmbAssociationType.Value
    .Cells(iRow, 9) = frmForm.txtUnitCount.Value
    .Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")
    .Cells(iRow, 11) = frmForm.txtLastManagerChange.Value
    .Cells(iRow, 12) = frmForm.txtCAMSenior.Value
    .Cells(iRow, 13) = frmForm.txtAMSenior.Value
End With
    
iRow = 0    'reset iRow

' For writing to New Entries sheet
With ThisWorkbook.Sheets("New Entries")
    ' find the last used row then add 2, this will (skip a line) then write to the sheet
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 2
    On Error GoTo 0         're-enable error notification
    If iRow = 0 Then iRow = 2

    '.Cells(iRow, 1) = iRow - 1
    .Cells(iRow, 1) = frmForm.txtLegalName.Value
    .Cells(iRow, 2) = frmForm.txtTransitionDate.Value
    .Cells(iRow, 3) = frmForm.txtAccountID.Value
    .Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")
    .Cells(iRow, 5) = frmForm.txtCurrentManager.Value
    .Cells(iRow, 6) = frmForm.txtTransitioningTo.Value
    .Cells(iRow, 7) = frmForm.cmbDivision.Value
    .Cells(iRow, 8) = frmForm.cmbAssociationType.Value
    .Cells(iRow, 9) = frmForm.txtUnitCount.Value
    .Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")
    .Cells(iRow, 11) = frmForm.txtLastManagerChange.Value
    .Cells(iRow, 12) = frmForm.txtCAMSenior.Value
    .Cells(iRow, 13) = frmForm.txtAMSenior.Value
End With
End Sub
 
Upvote 0
NoSparks said:
see if my re-write of your submit button works for you
VBA Code: 
Sub Submit()
   
    Dim iRow As Long

' For writing to Database sheet
With ThisWorkbook.Sheets("Database")
    ' find the last used row then add 1, this will write to the next line
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
    On Error GoTo 0         're-enable error notification
    If iRow = 0 Then iRow = 1
   
    '.Cells(iRow, 1) = iRow + 1
    .Cells(iRow, 1) = frmForm.txtLegalName.Value
    .Cells(iRow, 2) = frmForm.txtTransitionDate.Value
    .Cells(iRow, 3) = frmForm.txtAccountID.Value
    .Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")
    .Cells(iRow, 5) = frmForm.txtCurrentManager.Value
    .Cells(iRow, 6) = frmForm.txtTransitioningTo.Value
    .Cells(iRow, 7) = frmForm.cmbDivision.Value
    .Cells(iRow, 8) = frmForm.cmbAssociationType.Value
    .Cells(iRow, 9) = frmForm.txtUnitCount.Value
    .Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")
    .Cells(iRow, 11) = frmForm.txtLastManagerChange.Value
    .Cells(iRow, 12) = frmForm.txtCAMSenior.Value
    .Cells(iRow, 13) = frmForm.txtAMSenior.Value
End With
   
iRow = 0    'reset iRow

' For writing to New Entries sheet
With ThisWorkbook.Sheets("New Entries")
    ' find the last used row then add 2, this will (skip a line) then write to the sheet
    On Error Resume Next    'in case sheet is blank
    iRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 2
    On Error GoTo 0         're-enable error notification
    If iRow = 0 Then iRow = 2

    '.Cells(iRow, 1) = iRow - 1
    .Cells(iRow, 1) = frmForm.txtLegalName.Value
    .Cells(iRow, 2) = frmForm.txtTransitionDate.Value
    .Cells(iRow, 3) = frmForm.txtAccountID.Value
    .Cells(iRow, 4) = IIf(frmForm.optCAM.Value = True, "CAM", "AM")
    .Cells(iRow, 5) = frmForm.txtCurrentManager.Value
    .Cells(iRow, 6) = frmForm.txtTransitioningTo.Value
    .Cells(iRow, 7) = frmForm.cmbDivision.Value
    .Cells(iRow, 8) = frmForm.cmbAssociationType.Value
    .Cells(iRow, 9) = frmForm.txtUnitCount.Value
    .Cells(iRow, 10) = IIf(frmForm.optFull.Value = True, "CAM", "AM")
    .Cells(iRow, 11) = frmForm.txtLastManagerChange.Value
    .Cells(iRow, 12) = frmForm.txtCAMSenior.Value
    .Cells(iRow, 13) = frmForm.txtAMSenior.Value
End With
End Sub
Click to expand...
Thanks very much for your help, but unfortunately the New Entries sheet only shows the last record from the userform. For some reason the form can find the next available row on the table.
 
Upvote 0
Afraid then I'm going to need to see the workbook in order to help you out.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Using If Statement VBA to Submit to Additional Worksheet
Replies
4
Views
514
Micron
M
P
  • Question Question
Userform - "Add row below" button in VBA
Replies
1
Views
912
sijpie
sijpie
R
  • Question Question
Equal number and date being treated as different when comparing them
Replies
1
Views
638
mole999
mole999
D
  • Question Question
Userform Database call Function & Concatenating issues.
Replies
3
Views
522
GWteB
GWteB
B
  • Question Question
Find 1st blank/empty cell in Columm A and then Copy /Paste that same entire row
Replies
2
Views
641
NoSparks
N

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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