PDA

View Full Version : appending textbox info from a userform to a worksheet as a comment



jqagsweb
12-27-2015, 12:05 PM
I have a Work Order tracking system that I have been developing for our manufacturing process. I am making great progress but have a couple of sticking points that I need assistance with.

I have one file on the backend but in the end, once everything works as needed will separate the work order master db(WOMaster) to a separate file. When the user opens the workbook they are presented with the WO_Entry worksheet that display unreleased work orders. To add info to WO_Entry, there is a button which calls up the userform(frmWOControl). On the first tab, allinitial info is added for the work order, when they press post, the info is added to the WOMaster which then assigns an id number. WO_Entry is a query table based some of the info in WOMaster

The second tab of the userform contains two text entry boxes, one for issues that develop in direct relation to the work order(txtWONotes) and one for issues directly relating to the part itself(txtPartNotes) that need to be addressed so that it is not an ongoing issue for this part. The info in these two boxes will need to be added to and updated throughout the life of the work order so the info contained can end up being lengthy. At some point I read somewhere in one of the forums that converting it to a comment will keep the cell clean looking on the worksheet and I like the idea of it becoming a drop down for the notes. Not all parts will have notes I imagine so any macro needs to be able to deal with blank rows. Im not sure if dealing with this at entry is better than an onsheet macro or not, kind of lost on applying it directly from the userform. When they open the userform, for editing, the first thing they do is load the data for the work order they are changing (cboWONum) then they switch to the second tab to being adding/updating the notes.

When finished adding the notes they press an update button that then adds the information to WOMaster in columns17 WO Notes, and 18 Parts Notes and the goal is that it becomes a comment at that point. I realize that on pushing the update button, it needs to check those cells on the WOMaster to see if it is appending or adding new and then do it, converting it into a comment in the process but am clueless as to the process.

Private Sub txtpartnotes_Change()

Sheets("WOMaster").Activate
cboWONum= value indicating active record row currently the 3rd entry in the row at col c
ActiveCell.Offset(0, 14).Value = Me.txtWONotes.Text 'set col Q convert to comment append to comment
ActiveCell.Offset(0, 15).Value = Me.txtPartNotes.Text 'set col R convert to comment append to comment

'Clear down the values ready for the next record entry...
Me.txtWONotes.Text = Empty
Me.txtPartNotes.Text = Empty



End Sub

Any assistance is appreciated because Im at a wall on this. Im using excel 2010. It is not a shared workbook, this is the management side utilized by one person.

mikerickson
12-27-2015, 04:35 PM
I wouldn't use active cell, but syntax like this might help.


Sheets("Sheet1").Range("Q1").NoteText TextBox1.Text
' cf.
TextBox2.Text = Sheets("Sheet1").Range("Q1").NoteText