Increment Form and Revision Number with Hyphen in Between

USFengBULLS

Board Regular
Joined
May 7, 2018
Messages
66
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking at many examples online on how to auto increment Invoice / Form number but they aren't specific to what I need to do.
The format to number my forms is (Change Order #) - (Revision #). Example, If I were on the fourth revision of change order three, it would be 3-4.
I need to write two codes for two different situations.

The first code needs to increment the change order number, the value to the left side of the hyphen i.e. 1-0, 2-0, 3-0, 4-0

The second code will need to increment the revision number, the value to the right side of the hyphen i.e. 1-1, 1-2, 1-3, 1-4

I had 1-0 in the cell to test and wrote a simple Range("B7") = Range("B7") + 1 and of course it did not work. I already have a place to put these codes that resets the form depending on if its a new change order, or a revision to a change order. I just need some help with the actual code that would increment the number to left only or the number on the right only.
How would I write the code for both of these?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I have an ActiveX button that call multiple codes based off conditions. If it is a new Change Order (1-0, 2-0, 3-0) I was going to throw that code in a reset macro code I call at the end that looks like this:


Public Sub Reset()
'Clears Inputted Content from User to reset the Form sheet for Future Use.
Application.ScreenUpdating = False
Sheets("NEW FORM-RESET").Activate
Range("B6,B8,B9,D3,D8,A15:F28,B41,C41,C42").Select
Selection.ClearContents
ActiveSheet.ComboBox1 = ""


Range("A15") = "0-00"
Range("C15") = "$"
Range("D15") = "-$"
'New Change Order increment code here.
Range("B7").Value = ???
Range("D3").Select
End Sub

If it is a new revision I am going to write a completely new sub procedure and Call it in the Else for Revision on the ActiveX code. The first part of the If statement if for new change orders, the second is for new revisions. I have Highlighted in Red where I plan to put both codes. Here is the code I have for my button just to clear things up:

Private Sub CommandButton1_Click()


If Application.ActiveSheet.Name = "NEW FORM-RESET" Then
If Range("B7").Value = "0-0" Then
MsgBox "Please Update MR Change Order Number"
Exit Sub
ElseIf Range("D3") = "" Then
MsgBox "Please Update Change Order Submittal Status"
Exit Sub
ElseIf Range("D3") <> "INITIATED" Then
MsgBox "Change Order Status can only be INITIATED on the NEW FORM-RESET Sheet. Remember: This is used to Generate New Change Orders. If you need to REVISE, REJECT, OR APPROVE a CO, Find the CO Sheet and Adjust Accordingly."
Exit Sub
Else
Call Project_Info
Call Log_Copy
Call Sheet_Copy_NewForm
Call PDF_Save
Call Reset
End If
Else
Call Project_Info
Call Existing_Sheet_Rename
Call PDF_Save
Call Log_Copy
Call Log_Rejected
Call Log_Approved
'Call New Revision Increment sub here.
End If


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top