VB code help please

Get_Involved

Active Member
Joined
Jan 26, 2007
Messages
383
How do I restructure this part of the code, to put todays year, month, and date, before this part of the code?

Code:
Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then

Example: 2018121773 , year/month/date,2018/12/17+ 27 the updated number on the end.
This will create a Authorization number.

Code:
With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1

In the complete code I have to manually input the numbers the first time and it increases the end of it.

Code:
Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1

Thanks for any help.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I'm not sure what your wanting here.

Tell me in words what your wanting to do.

I see three pieces of code but do not know what you want the end result to look like.
And where do you want the end result entered.
 
Upvote 0
you mean someting like this ??
Code:
Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = Left(Range("H5"), 4) & Mid(Range("H5"), 6, 2) & Mid(Range("H5"), 9, 2) + 27 'change locations to suit
    With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 End With
 End If
 End Sub
 
Upvote 0
I am trying to get the current year, month, date, as the first part of the number. +
starting with 1, as the last part of the whole number.

EXample:
20181217+ 1 will read whole number 20181217-1

The code I am using I have to manually change every year.
Thanks for the reply.
 
Upvote 0
another option

Code:
Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd") + 27 'change locations to suit
    With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 End With
 End If
 End Sub
 
Last edited:
Upvote 0
Michael M:
What does this part do ? + 27 'change locations to suit

the front part is right 20181246, the 46 is not changing to 47 when I update.
and can we get it to read this number 20181218 then the updated number, could there be a - the date and the number?
Thank You
 
Upvote 0
Maybe this way then

Code:
Private Sub Workbook_Open()
 Dim Ans As String
Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd") + 27 'change locations to suit
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
With Sheets("Expense Report").Range("H4")
    .Value = .Value + 1
 End With
 End If
 End Sub

I don't understand this comment though... maybe a before and after sample might help !!
could there be a - the date and the number?
 
Upvote 0
.
With this version you will need to use I4 or some other cell as a helper cell.

Code:
Sub InvNum()
Dim Ans As String
Range("I4").Value = Range("I4").Value + 1
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd")
    With Sheets("Expense Report").Range("H4")
 .Value = .Value & "-" & Range("I4").Value
 End With
 End If
End Sub
 
Upvote 0
this is your Answer, with before. 20181246, the 46 is not changing to 47.
This how I would like it to read after, 20181218-47.
After updating.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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