Hi
I am trying to find a way to use a macro that initiates on opening the workbook that would check to see if a specific cell "G15" has nothing then enter a random generated number along with a date and letters. I want to be able to open the workbook that would act as a template and then populate as needed then save as a new work. then next time I go into the excel template workbook I want another randomly generated number to populate the same cell "G15". The issue I am having is that I keep getting the same randomly generated number each time I open. When I run the code when the sheet is open it gives me a new one but not when I open it. Any assistance would be great. Here is the code:
Sub Autpen()
Call CheckJV
End Sub
Sub CheckJV()
Dim JVCell As Range
Dim A As String
Dim B As Integer
Set JVCell = ThisWorkbook.Worksheets("JV").Range("G15")
ActiveWorkbook.Worksheets("JV").Activate
If isEmpty(JVCell) Then
Sheets("JV").Unprotect
A = Int(Now() * 1)
B = Int((999 * Rnd) + 100)
JVCell.value = "CS" & A & B
Sheets("JV").Protect
Else
MsgBox "JV Number Has already been created "
End If
End Sub
I am trying to find a way to use a macro that initiates on opening the workbook that would check to see if a specific cell "G15" has nothing then enter a random generated number along with a date and letters. I want to be able to open the workbook that would act as a template and then populate as needed then save as a new work. then next time I go into the excel template workbook I want another randomly generated number to populate the same cell "G15". The issue I am having is that I keep getting the same randomly generated number each time I open. When I run the code when the sheet is open it gives me a new one but not when I open it. Any assistance would be great. Here is the code:
Sub Autpen()
Call CheckJV
End Sub
Sub CheckJV()
Dim JVCell As Range
Dim A As String
Dim B As Integer
Set JVCell = ThisWorkbook.Worksheets("JV").Range("G15")
ActiveWorkbook.Worksheets("JV").Activate
If isEmpty(JVCell) Then
Sheets("JV").Unprotect
A = Int(Now() * 1)
B = Int((999 * Rnd) + 100)
JVCell.value = "CS" & A & B
Sheets("JV").Protect
Else
MsgBox "JV Number Has already been created "
End If
End Sub