Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

userform submit button saving document, but not imputting data into worksheets

Posted on 2016-11-21
12
Medium Priority
?
79 Views
Last Modified: 2017-01-17
So to go into a little more detail. this document IS opening and saving the document, as verified by the documents properties, but it is not storing the values of the userform. I believe the issue to be a part of this portion of the code, but im not sure as to why it is not loading in. could the issue possibly be that the worksheets are established as tables?

Private Sub sbmt_Click()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim irow As Long

    Select Case NHdata.nhNme.Value
        Case "john", "jordy", "stan"
             Set wb = Workbooks.Open("L:\names\helper\harold\harold.xlsm")
             Set ws = Worksheets("Data")
        Case "chris", "dave", "christina"
             Set wb = Workbooks.Open("L:\names\helper\jose\jose.xlsm")
             Set ws = Worksheets("Data")
        Case Else
        MsgBox "There is no Trainer set for this Apprentice Agent. Please contact David Francisco to have the trainer added for this Apprentice Agent."
    End Select
    

 irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    With ws
        .Range("A" & irow) = Me.Date1.Value
        .Range("B" & irow) = Me.typSel.Value
        .Range("C" & irow) = Me.acntNmbr.Value
        .Range("D" & irow) = Me.nhNme.Value
        .Range("E" & irow) = Me.ojtNme.Value
        .Range("F" & irow) = Me.rslvChk.Value
        .Range("G" & irow) = Me.pvntChk.Value
        .Range("H" & irow) = Me.prmtChk.Value
        .Range("I" & irow) = Me.profChk.Value
        .Range("J" & irow) = Me.efctChk.Value
        .Range("K" & irow) = Me.srtText1.Value
        .Range("L" & irow) = Me.stpText1.Value
        .Range("M" & irow) = Me.conText1.Value
        .Range("N" & irow) = Me.srtText2.Value
        .Range("O" & irow) = Me.stpText2.Value
        .Range("P" & irow) = Me.conText2.Value
   End With
    wb.Close True
 End Sub

Open in new window

Tester.xlsm
0
Comment
Question by:david francisco
  • 6
  • 6
12 Comments
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41896141
When I send the data to the "Data" sheet in the workbook, it shows up. Do the workbooks that you refer to in the code exist? Are the names and locations correct?
0
 

Author Comment

by:david francisco
ID: 41896148
all the addresses are correct, but in each workbook used for saving data, the entire workbook is establish as a table called "Table1" that I think is where I am missing the references to the table
0
 

Author Comment

by:david francisco
ID: 41896150
the document is opening, and saving, but the data is not transferring from the user form to the worksheet, and I believe it is because I am not referencing the Table  ("Table1") in the code, but im not sure how to integrate that in
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:david francisco
ID: 41896152
the example document to be saved in is this
jose.xlsm
0
 

Author Comment

by:david francisco
ID: 41896157
rather, after looking at the document, it appears the info is being saved, but only on line 5001 and beyond, not the next open line.
0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41896169
The data is getting there but there's data in rows 5000+. Delete those rows or move them up and try it again.
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41896181
If you want to write to the first empty row and ignore the rows at 5000 then change your iRow calculation to
 irow = ws.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1

Open in new window

0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41896551
Your workbook may be corrupted because when I try to refer the comboboxes I get a" user defined type not defined" error. When I add an "On Error Resume Next" line before trying to refer to it, I get the error on the "On Error Resume Next" line! Let me see if I can rebuild the workbook.
0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41896639
I found the problem!!! It had nothing to do with the comboboxes, but rather the problem was in the Jose.xlsm workbook. It had a Worksheet_TableUpdate sub that was invalid and if the sub is commented out, the problem goes away. Do you know what that sub was supposed to do? I also made a few changes in the main workbook that I've attached. The changes I made are marked with 'new
28984486.xlsm
0
 

Author Comment

by:david francisco
ID: 41896719
the sub is to set color changes for true and false in the target worksheet. but those are not necessary, I think I actually resolved it with your bit of code above. thanks for all the help.
0
 

Author Closing Comment

by:david francisco
ID: 41896720
Great help and very timely!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41896736
If you want the colors then...
1) Uncomment the Worksheet_TableUpdate sub and change it to Private Sub UpdateColors(ByVal Target As Range)
2) In the Data sheet in Jose.xlsm delete the rows 2 to 5010.
3) At the end of the Worksheet_Change event in that workbook, add UpdateColors Target
I've attached the modified workbook.

In any case you're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
jose.xlsm
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question