Solved

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

Posted on 2016-11-21
12
25 Views
Last Modified: 2016-11-21
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Author Comment

by:david francisco
Comment Utility
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
The data is getting there but there's data in rows 5000+. Delete those rows or move them up and try it again.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Great help and very timely!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now