Insert value after matching with existing cell

[fibonaccistu]

Hello! I'm very new to VBA so I apologize for my ignorance. I've been working on a Userform for logging ordered pulled by the warehouse workers at my job. The warehouse worker selects their own name from a list of option buttions and then enters in values for Sales Order Number, Customer Number, Order Time, # of Line Items, and then the Start and Finish times. All of these values get written to the worksheet at the same time when the OK button is clicked in the next empty row. A second warehouse worker checks the first for errors, but this doesn't happen until later in the day, so I need to be able to enter in values for a specific row retroactively. I'm trying to create a second userform that will let me select the "checking" warehouse worker (obWorker) and then insert that value in column G for a row with the existing Sales Order number in column A. So the second userform just has a list of option buttons for the workers and a text field to look up the SO number. I really have no idea where to begin. Any and all help is much appreciated.

[SamT]

Welcome to VBA Express, Fibonaccistu, Any relation to Leonardo?

If you already have the Sales Order in electronic format somewhere it is usually best to transfer all the data from that storage to the UserForm.

If this is not the case, I suggest that your first design the UserForm as it would be used in the future by someone who is dedicated to this task. Then only add the functionality that you and your warehouse-men need. This way, you can be certain that all the bugs have been worked out and when you reduce the functionality of the Warehouse version both versions will still be compatible .

Since Data is the precursor of Function, first design the Spreadsheet Table(s) where all the information will be stored. Consider events that occur Quarterly, semi-annually and annually.

Note that you should consider a separate Sheet for the invoice lines of the Sales. It merely needs the first column to be the Sales Order Number. Each Invoice line will be on a separate Row. Be sure to include a column for Complete? (Y/N), a Column for Number Short,and a column for Make-Up Order(X). All other Unique information of all sales can be one one sheet, also with the Sales Order Number in the First Column. These two sheets should be kept sorted on Column A

I suggest that you keep all Unique Customer information on a different Sheet, with the Customer number in the first column, then on yet another sheet, all Sales Order Numbers in Column B with Customer Numbers in Column A. These two sheets should be kept sorted on column A

This schema allows you to rapidly determine the full status of any order even with minimal information supplied


I imagine three uses for the UserForm(s): The Data Entry worker, who only enters new information, The Warehouse-Men who may enter new information (especially right now) and use existing Information; and the obWorker you mentioned who checks and edits existing information.

The final sheet in the Workbook, suggested Name is VBA_Lists, is several lists:

• Those authorized to enter new and edit existing Customer information
• Those authorized to edit existing Sales Order information
• Those authorized to click the Start Time and End Time buttons.

There will be names duplicated in two or three lists, but these lists will be used in the UserForm(s) code to determine who can use each form and be used in Drop Down lists so the User can select their name.



For further reading:
http://www.vbaexpress.com/forum/showthread.php?29717
http://www.vbaexpress.com/forum/showthread.php?19820
http://www.vbaexpress.com/forum/showthread.php?42266
http://www.vbaexpress.com/forum/showthread.php?29395

[fibonaccistu]

Thank you for such a detailed reply! I should probably clarify the use for this spreadsheet. The company I work for uses Sage 100 to manage their orders/invoices/customers/vendors. This spreadsheet's main end function is to calculate the Average time per line item for each of warehouse workers and to check for pulling errors. All other information regarding the sales orders is maintained in Sage 100. I'm not trying to migrate the whole business over to Excel, lol.

[SamT]

IF you can use the information here to pull the information from Sage 100, then we can code the UserForm so that the warehouse men only have to select their name and click Start and Finish.

It really is important to keep manual data entry to a minimum.

Attachment 15455

This is a quick example of what you might use on the Floor, The obChecker would use a different UserForm.

The list on Names would be linked to your list on the aforementioned VBA _Lists Sheet, and the list of Active Invoice numbers hopefully from Sage 100. The worker would only have to select his/her name, then select an Invoice Number and click Start. When done without discrepancy, select the Invoice number again, which is linked to his name, and press Finished, The Finished button is now linked to his/her name and if the Start button is clicked before the finish button is clicked again, the newly selected invoice is then linked to his name.

If there should be a discrepancy, clicking Notes would open a new UserForm, also linked to the Order Number, so Notes can be entered.

The UserForm State are

1. No name and Order Number Selected, Selection enabled
2. Name And Order Number Selected, Start Button Enabled
3. Start button pressed, Name and Order linked, (Cannot be unlinked,) New State # 1 Enabled
4. Linked Name and Order selected, Finish Button enabled
5. Finish Button Pressed, Return to State # 1. Existing linkage still present until new selection made
6. Special: Name and Order linked. Notes button enabled

Note I used "Invoice" and "Order" interchangeably. My bad

[fibonaccistu]

I've tried multiple times to get data from Sage into Excel. I went to Data > Get External Data From Other Source From Microsoft Query and then selected SOTAMAS90* from the list of databases. I located the database with the information I needed, but it only ever shows the headers, no data. I tried the same thing in Access with the same results. I tried probably 10 different databases and only 2 of them had any information that was brought over correctly but unfortunately none of it was what I needed for this task. I'm scheduled to talk to Sage again later today so hopefully they can help me figure out why that's not working...

I should also clarify, when I said obWorker I was referring to the name of the option buttons in VBA. I have them set up as obCarlos, obMike, etc.

I've attached a screen shot of the current userform I've created to the data to the spreadsheet and another of the "Checked by" userform I'm still trying to code.userform.jpgcheckedby.jpg

[SamT]

IT is very easy if you can see the spreadsheet and the userform side by side

Set the UserForm.ShowModal = False and in the WareHouseData code module

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim UFrm As Object

For Each UFrm In UserForms
  If UFrm.Name = "CheckedBy" Then
    If UFrm.Visible Then
    UFrm.OrderNumber = Target.EntireRow.Columns(1)
    UFrm.RowNumber = Target.Row
    Exit Sub
    End If
  'ElseIf 'Other Form Name
  End If
Next
End Sub

In the UserForm Code

Option Explicit

Dim mRowNumber As Long
Dim mOrderNumber As Variant

Property Let OrderNumber(OrderNum As Variant)
  mOrderNumber = ONum
  Me.Controls("TextBoxOrderNumber").Text = ONum
End Property
Property Get OrderNumber() As Variant
  OrderNumber = mOrderNumber
End Property

Property Let RowNumber(RowNum As Long)
  mRowNumber = RowNum
End Property
Property Get RowNumber() As Long
  RowNumber = mRowNumber
End Property

Property Subs Create Properties in Objects. Property Let Sub create Writable Properties and Get Subs create Readable Poperties.

Inside the Form, you can use the Properties or the Property Storage Variables (prefixed with "m" or "m_")

Examples:

Sheets("Warehouse Cata").Cells(mRowNumber, "E") = Me.Controls("TextBoxErrors").Text
Sheets("Warehouse Cata").Cells(RowNumber, "E") = Me.Controls("TextBoxErrors").Text

My personal preference is to use the Variable, reserving the Properties for interfacing with outside objects and code. It helps keep things straight in my head. (but that's another story )

[fibonaccistu]

SamT, thank you again for your detailed reply. I must admit though, I'm terribly confused ha. Please forgive my ignorance. What did you mean by "IT is very easy if you can see the spreadsheet and the userform side by side"? What is easy?

And what is the purpose of these two bits of code? What exactly do they do? I'm trying to follow along with it as best as I can but I'm very new to everything Excel/VBA. I appreciate your help.

[SamT]

From the Screenshot in your post, I can tell that your computer monitor is large enough to display a significant portion of the Worksheet and the UserForm side by side. That makes it possible for the User to use the Worksheet to interact with the UserForm.

Setting the UserForm's ShowModal Property to False allows the Worksheet to be used while the UserForm is displayed.

Windows Programs, or Applications, use the Users action to operate, actions such as Clicking on something or pressing a key or mouse button. These actions are called Events. Note that some Events can be Raised by Code. For instance if there is code that Selects Ranges on a Worksheet.

The first sub above is an Event Sub, (technically, an Event Procedure.) It is a Worksheet Event and the Event is Selection Change, both as indicated in the Sub Name "Worksheet_SelectionChange." The Sub will run every time a different cell on the Worksheet is Selected, either by the User or by Code.

A UserForm has no real "value" Properties. A Cell has Value, Text, and Value2 Properties, all which return or set something you can work with. The two pairs of Property Subs above add the Read/Write "value" Properties Rownumber and OrderNumber to the UserForm.

The Event Sub, Worksheet_SelectionChange, will run anytime the Selection (Range) changes, whether or not a UserForm is Loaded or even Visible (depending the the visible UserForm's ShowModal Property.)

When the Event Sub runs, it first checks to see IF frmCheckedBy is Loaded, then it checks IF it is Visible. If either is not True, then it just Exits. Otherwise, it Sets the two new (User Defined) UserForm "value" Properties. Only Loaded UserForms are in the UserForms Collection.

Note that the Procedure Property Let OrderNumber is also tasked with setting the Text of the TextBox "tbxOrderNumber" on the UserForm.

I don't know the names you used for the Controls so I just use typical Control Names in my explanations above.