Solved

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

Posted on 2016-11-21
12
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 47

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 47

Accepted Solution

by:
Martin Liss earned 500 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 47

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 47

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 47

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

735 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