I started a new thread because I marked the last one as Resolved, but unfortunately I need help with a few more things. I also sent a message to the forum mods, but received no response on proper procedure.
The accounting department wants a few more features added to this:
1. A quantity input box, which would add sequential numbers according to the quantity entered. (Basically our accounting department sometimes has multiple quantities of orders that need unique serial numbers and/or national board numbers that need to be in sequence, but need to have the same Job and Part #)
a. If a quantity was entered in the “S/N Only” field only serial numbers would be generated.
b. If a quantity was only entered in the “S/N & N/B” field both serial numbers and national board numbers would be generated.
c. The Job # and Part # are still entered by the user and would populate in the excel document the same for S/N or S/N & N/B for any quantity entered.
2. Is there any way to get the last saved field or fields to automatically print to a default printer when Save is activated?
3. Is there any way to have the worksheet protected from manual editing? If not would it be possible to put the visual basic interface in Worksheet 1 and have the actual number sheet in Worksheet 2?
I have attached a screen shot of what I imagine the interface will look like.
Below is all the code I have so far:
Code:
Private Sub CommandButton1_Click()
End Sub
Private Sub checkbox_nb_Click()
If checkbox_nb.Value = True Then
'get next NB
Else
'don't
End If
Set ws = ActiveSheet
lastRow = ws.Range("b" & Rows.Count).End(xlUp).Row
'find the last filled cell in column B
nextNum = CLng(ws.Range("b" & lastRow).Value) + 1
'add 1 to the value in the last filled cell in B
Me.textbox_nb.Text = CStr(nextNum) '*** added this to populate textbox with next number
End Sub
Private Sub CommandButton_generate_Click()
Dim ws As Worksheet
Dim nextNum As Long
Dim lastRow As Long
Set ws = ActiveSheet
lastRow = ws.Range("a" & Rows.Count).End(xlUp).Row
'find the last filled cell in column A
nextNum = CLng(ws.Range("a" & lastRow).Value) + 1
'add 1 to the value in the last filled cell in A
Me.textbox_sn.Text = CStr(nextNum) '*** added this to populate textbox with next number
End Sub
Private Sub CommandButton_generateNB_Click()
Dim ws As Worksheet
Dim nextNum As Long
Dim lastRow As Long
Set ws = ActiveSheet
lastRow = ws.Range("b" & Rows.Count).End(xlUp).Row
'find the last filled cell in column A
nextNum = CLng(ws.Range("b" & lastRow).Value) + 1
'add 1 to the value in the last filled cell in A
Me.textbox_nb.Text = CStr(nextNum) '*** added this to populate textbox with next number
End Sub
Private Sub CommandButton_save_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a Name number
If Trim(Me.textbox_sn.Value) = "" Then
Me.textbox_sn.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.textbox_sn.Value
ws.Cells(iRow, 2).Value = Me.textbox_nb.Value
ws.Cells(iRow, 3).Value = Me.textbox_job.Value
ws.Cells(iRow, 4).Value = Me.textbox_part.Value
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_sn.Value = ""
Me.textbox_nb.Value = ""
Me.textbox_job.Value = ""
Me.textbox_part.Value = ""
Me.textbox_sn.SetFocus
End Sub
Private Sub Label2_Click()
End Sub
Private Sub sn_Click()
End Sub
Private Sub UserForm_Click()
End Sub
Again, I really appreciate any help. And I hope what I am asking makes sense, if not feel free to ask any questions for clarification. This isn’t exactly my field of expertise
With this updated code, I check for a qty in either the SN Only or the "both" textbox, then loop that many times through the number generating piece. I do NOT check to make sure a job number and part number are filled in. You'd have to add that.
Code:
Private Sub btnSave_Click()
Dim foundQty As Boolean
If txt_SN_Only_Qty.Text <> "" Then
If IsNumeric(txt_SN_Only_Qty.Value) Then
foundQty = True
Call writeVals(txt_SN_Only_Qty.Value, "sn", txtJob.Text, txtPart.Text)
End If
ElseIf txt_SN_NB_qty.Text <> "" Then
If IsNumeric(txt_SN_NB_qty.Value) Then
foundQty = True
Call writeVals(txt_SN_NB_qty.Value, "both", txtJob.Text, txtPart.Text)
End If
End If
If foundQty = False Then
MsgBox "No qty found in either field"
End If
End Sub
Private Sub writeVals(numRecords As Integer, snOrBoth As String, job As String, part As String)
Dim ws As Worksheet
Dim writeRow As Long
Dim j As Integer
Dim lastSN As Long
Dim lastNB As Long
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws
For j = 1 To numRecords
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
lastSN = CLng(.Range("a" & writeRow - 1).Value)
.Range("a" & writeRow).Value = lastSN + 1
If snOrBoth = "both" Then
lastNB = CLng(.Range("b" & writeRow - 1).Value)
.Range("b" & writeRow).Value = lastNB + 1
End If
.Range("c" & writeRow).Value = job
.Range("d" & writeRow).Value = part
Next j
End With
End Sub
I'm not clear what you mean by "getting the last saved fields to print..." what exactly would you want printed? Just those X lines? Some headings?
As far as protecting it goes, you could have protection enabled, then have the code turn it off while updating, then turn it back on at the end.
As for getting it to print. They were hoping to get the last rows saved to automatically print when Save is pressed. So if they enter a Qty of 7 for SN then one sheet would print with the 7 serial numbers along with job and part numbers.
put the "stop" where I show it in post #6, then when it goes into break mode, hover your cursor over "txt_SN_Only_Qty.Value" , "txtJob.Text" and "txtPart.Text" to see what their values are that you're trying to feed into "writeVals."
Debug points to:
Call writeVals(txt_SN_Only_Qty.Value, "sn", txtJob.Text, txtPart.Text)
If you have not yet done so, add "Option Explicit" to the top of your code page. I suspect that one or more of your textboxes are not named what you think they are.
Wow I am a complete doofus. I must have looked at this 20x and did not realize I had "textJob" instead of "txtJob". Seriously sorry about that.
Super close to working. The S/N only field is working as intended. However if you enter a quantity in the "S/N & N/B" it starts listing sequential numbers starting at 1 if the last above field is left blank. I am guessing I need something to pull from the last filled cell in the column?
If you go back to my code in post #2, it shows where for "SN Only" it's finding the last filled cell in column A, but for "both" it's finding the last filled cell in column B instead.
It appears to be pulling from the cell directly above, even if it is not filled. So if I have a bunch of S/N's with no N/B's it will start over from 1 in the N/B column, instead of pulling from the last filled cell in column B.
I tried using "clear contents" in the N/B column, but the results are still the same. Is there another way to do it? Or do you know of code that could pull from the last numeric value in the N/B column instead of the last filled cell?
Your issue may be related to the way you assign the cell values. As it now stands, you are assigning string values where you intend integer values. Excel will clean this up for you (i.e. "123" becomes 123 and "" becomes an empty cell). However if the user of your form clears the input textbox by typing spaces over the existing number, the a string of spaces will be assigned to the cell value.
You have two choices: 1) Trim the textbox text before assigning the value or 2) convert to the text to an integer and only assign the value if it is greater than zero.
For the first option, your code would look like this:
Code:
Private Sub writeVals(numRecords As Integer, snOrBoth As String, job As String, part As String)
Dim ws As Worksheet
Dim writeRow As Long
Dim j As Integer
Dim lastSN As Long
Dim lastNB As Long
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws
For j = 1 To numRecords
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
lastSN = CLng(.Range("a" & writeRow - 1).Value)
.Range("a" & writeRow).Value = lastSN + 1
If snOrBoth = "both" Then
lastNB = CLng(.Range("b" & writeRow - 1).Value)
.Range("b" & writeRow).Value = lastNB + 1
End If
.Range("c" & writeRow).Value = Trim$(job)
.Range("d" & writeRow).Value = Trim$(part)
Next j
End With
End Sub
Thank you for the responses. Unfortunately I am getting in over my head on this project. I really have no experience with even basic programming and am trying to do this for my companies accounting department. I am more than willing to pay someone to finish this, as I know I have taken up a lot of everyone's time.
vbfbryce: I tried researching Application.WorksheetFunction.Max - but I have no idea how to code it in.
TnTinMN: I tried your suggestions, but not exactly sure if I placed the code correctly?
Code:
Private Sub writeVals(numRecords As Integer, snOrBoth As String, job As String, part As String)
Dim ws As Worksheet
Dim writeRow As Long
Dim j As Integer
Dim lastSN As Long
Dim lastNB As Long
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws
For j = 1 To numRecords
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
lastSN = CLng(.Range("a" & writeRow - 1).Value)
.Range("a" & writeRow).Value = lastSN + 1
If snOrBoth = "both" Then
Dim lastNBValueRow As Long
lastNBValueRow = .Range("c" & Rows.Count).End(xlUp).Row
lastNB = CLng(.Range("b" & lastNBValueRow).Value)
.Range("b" & writeRow).Value = lastNB + 1
End If
.Range("c" & writeRow).Value = Trim$(job)
.Range("d" & writeRow).Value = Trim$(part)
Next j
End With
End Sub
Here's a standalone example of using the Max function, on column B:
Code:
Sub myMax()
Dim ws As Worksheet
Dim rngCheck As Range
Dim maxNum As Long
Dim lr As Long
Set ws = ActiveSheet
lr = ws.Range("b" & Rows.Count).End(xlUp).Row
Set rngCheck = ws.Range("b2:b" & lr)
maxNum = Application.WorksheetFunction.Max(rngCheck)
MsgBox maxNum
End Sub
Thanks for the encouragement! I am having trouble integrating this into the code we already have. I tried a few different ways, but not sure of placement.
Code:
Private Sub CommandButton_save_Click()
Dim foundQty As Boolean
If txt_SN_Only_Qty.Text <> "" Then
If IsNumeric(txt_SN_Only_Qty.Value) Then
foundQty = True
Call writeVals(txt_SN_Only_Qty.Value, "sn", txtJob.Text, txtPart.Text)
End If
ElseIf txt_SN_NB_qty.Text <> "" Then
If IsNumeric(txt_SN_NB_qty.Value) Then
foundQty = True
Call writeVals(txt_SN_NB_qty.Value, "both", txtJob.Text, txtPart.Text)
End If
End If
If foundQty = False Then
MsgBox "No qty found in either field"
End If
End Sub
Private Sub writeVals(numRecords As Integer, snOrBoth As String, job As String, part As String)
Dim ws As Worksheet
Dim writeRow As Long
Dim j As Integer
Dim lastSN As Long
Dim lastNB As Long
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws
For j = 1 To numRecords
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
lastSN = CLng(.Range("a" & writeRow - 1).Value)
.Range("a" & writeRow).Value = lastSN + 1
If snOrBoth = "both" Then
lastNB = CLng(.Range("b" & writeRow - 1).Value)
.Range("b" & writeRow).Value = lastNB + 1
End If
.Range("c" & writeRow).Value = Trim$(job)
.Range("d" & writeRow).Value = Trim$(part)
Next j
End With
End Sub
Private Sub writeVals(numRecords As Integer, snOrBoth As String, job As String, part As String)
Dim ws As Worksheet
Dim writeRow As Long
Dim j As Integer
Dim lastSN As Long
Dim lastNB As Long
'*********************
Dim maxNum As Long 'added this ****************
'*********************
Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws
For j = 1 To numRecords
writeRow = .Range("c" & Rows.Count).End(xlUp).Row + 1
'lastSN = CLng(.Range("a" & writeRow - 1).Value) 'don't use this line
lastSN = Application.WorksheetFunction.Max(.Range("a" & writeRow - 1).Value)
'***** added the above line *************************************************
.Range("a" & writeRow).Value = lastSN + 1
If snOrBoth = "both" Then
'lastNB = CLng(.Range("b" & writeRow - 1).Value) 'don't use this line
lastNB = Application.WorksheetFunction.Max(.Range("b" & writeRow - 1).Value)
'***** added the above ******************************************************
.Range("b" & writeRow).Value = lastNB + 1
End If
.Range("c" & writeRow).Value = Trim$(job)
.Range("d" & writeRow).Value = Trim$(part)
Next j
End With
End Sub
Yes! TnTinMN that fix to vbfbryce's code worked! Thank you both!
One last thing... Well actually two.
1. Is there any way to get the last saved field or fields to automatically print to a default printer when Save is activated? For example, if they enter a Qty of 7 for SN then one sheet would print with the 7 serial numbers along with job and part numbers.
2. Is there any way to have the worksheet protected from manual editing? Basically the people using this are afraid they could accidentally enter a number manually when the interface box is not showing.
It almost worked. However it only printed the last row and not the full quantity of rows last saved. Also, Is it possible to get row 1 to print along with all of these (so the descriptive heading is shown)?
Unrelated to the printing. Would I be able to place
Code:
ThisWorkbook.Save
after the print code to have the workbook saved as soon as Save is pressed?