Data from userform only submitting to 2 columns?

J

jacob1234

New Member
Joined
Aug 16, 2016
Messages
37
Hi All,

Hopefully somebody can help me with this one, I have a userform that is submitting data to the next empty row of columns "T:AB" but the code is only submitting data to columns T and U, could anyone please help me to find out why?


Thanks in advance


Jacob.


Code: 
Private Sub CommandButton7_Click()
Dim LastRow1 As Long, lngIndex1 As Long, ws As Worksheet

    
    
        
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = ComboBox7.Value
        .CC = ""
        .BCC = ""
        .Subject = "CAOOS" & " " & MonthView1.Value & " " & "Unique ID Number:" & " " & Label4.Caption
        .HTMLBody = "Hi" & " " & ComboBox7.Value & "<br>" & "<br>" & "You have a new Customer Acceptance form to fill out." & "<br>" & "<br>" & "This has been submitted by:" & " " & Label1.Caption & "<br>" & "<br>" & "To view new submission" & " " & "<A HREF=""file://" & ActiveWorkbook.FullName & """>Click Here</A>" & "<br>" & "<br>" & "<br>" & "<br>" & "Unique ID Number:" & " " & Label4.Caption
  
        
    .Send   'or use .Display
        
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    
    MsgBox "This row has now been updated", vbOKOnly, "Success"
    
        End With
        
        
        
        
    LastRow1 = Range("S" & Rows.Count).End(xlUp).Row
    lngIndex1 = Me.ListBox3.ListIndex
    Set ws = Sheets("DATABASE")
    
    
    ws.Range("T" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox18.Value
        
    
    UserForm1.Hide
    
    
    
    
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
jacob1234 said:
Hi All,

Hopefully somebody can help me with this one, I have a userform that is submitting data to the next empty row of columns "T:AB" but the code is only submitting data to columns T and U, could anyone please help me to find out why?


Thanks in advance


Jacob.


Code: 
Private Sub CommandButton7_Click()
Dim LastRow1 As Long, lngIndex1 As Long, ws As Worksheet

[COLOR="#0000FF"]<<<<< SNIP >>>>>[/COLOR]    
    
    ws.Range("T" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).[COLOR="#FF0000"]Cells[/COLOR](lngIndex1 + 1) = UserForm1.TextBox18.Value
        
    
    UserForm1.Hide
    
    
    
    
End Sub
Click to expand...
The Cells object takes two arguments (a row number and a column, either a number or letter designation)... your code above is only providing it the row number.
 
Last edited:
Upvote 0
Rick Rothstein said:
The Cells object takes two arguments (a row number and a column, either a number or letter designation)... your code above is only providing it the row number.
Click to expand...

Thanks so much Rick, what would I need to add to my code to make it work? would below be sufficient?
Code: 
    ws.Range("T" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](18,lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](19,lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](20,lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](21,lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](22,lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](23,lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](24,lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](25,lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](26,lngIndex1 + 1) = UserForm1.TextBox18.Value

Also if it the column number wasn't identified - why would column T&U still get data submitted?

Thanks for your help!
 
Upvote 0
jacob1234 said:
Thanks so much Rick, what would I need to add to my code to make it work? would below be sufficient?
Code: 
    ws.Range("T" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](18,lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](19,lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](20,lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](21,lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](22,lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](23,lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](24,lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](25,lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](26,lngIndex1 + 1) = UserForm1.TextBox18.Value

Also if it the column number wasn't identified - why would column T&U still get data submitted?

Thanks for your help!
Click to expand...



Any thoughts?
 
Upvote 0
jacob1234 said:
Thanks so much Rick, what would I need to add to my code to make it work? would below be sufficient?
Code: 
    ws.Range("T" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](18,lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](19,lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](20,lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](21,lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](22,lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](23,lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](24,lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](25,lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).[COLOR=#FF0000]Cells[/COLOR](26,lngIndex1 + 1) = UserForm1.TextBox18.Value

Also if it the column number wasn't identified - why would column T&U still get data submitted?
Click to expand...
If your row numbers are 18, 19, 20, etc. and lngIndex1+1 is, in fact, a column number, then yes, it should work.

As to your second question... I don't know, I guess it makes something up when it is missing that but I do not know what rule it follows. Whenever I use Cells, I don't give it a chance to screw up because I always supply two arguments.
 
Upvote 0
Rick Rothstein said:
If your row numbers are 18, 19, 20, etc. and lngIndex1+1 is, in fact, a column number, then yes, it should work.

As to your second question... I don't know, I guess it makes something up when it is missing that but I do not know what rule it follows. Whenever I use Cells, I don't give it a chance to screw up because I always supply two arguments.
Click to expand...



Thanks Rick

Apologies for previous post -I mucked up the order. Please see the code below. It is selecting the right row but only populating columns T & U?

Code: 
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
      lngIndex = ListBox3.ListIndex
      Set ws = Sheets("DATABASE")
  
     ws.Range("T" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow).Cells(lngIndex + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow).Cells(lngIndex + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow).Cells(lngIndex + 1) = UserForm1.TextBox18.Value
 
Upvote 0
And updated code that still isn't posting to columns
Code: 
     LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
      lngIndex = ListBox3.ListIndex
      Set ws = Sheets("DATABASE")
  
     ws.Range("T" & LastRow).Cells(lngIndex + 1, 19) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow).Cells(lngIndex + 1, 20) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow).Cells(lngIndex + 1, 21) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow).Cells(lngIndex + 1, 22) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow).Cells(lngIndex + 1, 23) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow).Cells(lngIndex + 1, 24) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow).Cells(lngIndex + 1, 25) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow).Cells(lngIndex + 1, 26) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow).Cells(lngIndex + 1, 27) = UserForm1.TextBox18.Value
 
Upvote 0
Rick Rothstein said:
If your row numbers are 18, 19, 20, etc. and lngIndex1+1 is, in fact, a column number, then yes, it should work.

As to your second question... I don't know, I guess it makes something up when it is missing that but I do not know what rule it follows. Whenever I use Cells, I don't give it a chance to screw up because I always supply two arguments.
Click to expand...


Hi Rick - Sorry for the confusion with the other posts. The code I have actually put in is below:
Code: 
     LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
      lngIndex = ListBox3.ListIndex
      Set ws = Sheets("DATABASE")
  
     ws.Range("T" & LastRow).Cells(lngIndex + 1, 19) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow).Cells(lngIndex + 1, 20) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow).Cells(lngIndex + 1, 21) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow).Cells(lngIndex + 1, 22) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow).Cells(lngIndex + 1, 23) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow).Cells(lngIndex + 1, 24) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow).Cells(lngIndex + 1, 25) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow).Cells(lngIndex + 1, 26) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow).Cells(lngIndex + 1, 27) = UserForm1.TextBox18.Value
This is now posting all data but instead of posting in T:AB it has posted in AL and every 2nd column after that (ie. AL, AN, AP and so on) any ideas?

Thanks so much for your advice
 
Upvote 0
jacob1234 said:
And updated code that still isn't posting to columns
Code: 
     LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
      lngIndex = ListBox3.ListIndex
      Set ws = Sheets("DATABASE")
  
     ws.Range("T" & LastRow).Cells(lngIndex + 1, 19) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow).Cells(lngIndex + 1, 20) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow).Cells(lngIndex + 1, 21) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow).Cells(lngIndex + 1, 22) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow).Cells(lngIndex + 1, 23) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow).Cells(lngIndex + 1, 24) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow).Cells(lngIndex + 1, 25) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow).Cells(lngIndex + 1, 26) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow).Cells(lngIndex + 1, 27) = UserForm1.TextBox18.Value
Click to expand...
I am a little confused by your construction... why are you establishing a Range (column letter followed by LastRow) and then specifying those Cell offsets from it... is that really what you want to do? What cells are you actually trying to put values into?
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Question
VBA Copy Data from Sheet to Email depending on Assignee
Replies
2
Views
372
2000km12
2
A
  • Solved
VBA code for adding data from a sheet to 2 different sheets
2
Replies
11
Views
717
mumps
mumps
A
  • Question
Pull a data from excel sheet on vba macro (using excel formula)
Replies
1
Views
362
KOKOSEK
KOKOSEK
K
  • Solved
Add multiple values from userform and table to single cell VBA
Replies
4
Views
1K
KestutisTower
K
P
  • Question
VBA for sending rows of data if column A = current date. (Via email)
Replies
1
Views
423
paun_shotts
P

Forum statistics

Threads
1,215,619
Messages
6,125,875
Members
449,267
Latest member
ajaykosuri

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