Trouble finding vba code to create random generated number upon opening

[Ohyaisee]

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

 

[Norie]

Try adding Randomize at the top of the code.

 

[Ohyaisee]

Sorry I am learning the site. Did not tag code. Where would I add Randomize?

Sub Auto_Open()
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

 

[footoo]

If you can use 10 digits instead of 8, this would ensure their are no repeated numbers :

Sub CheckJV()
Dim JVCell As Range
Set JVCell = Worksheets("JV").Range("G15")
If IsEmpty(JVCell) Then
    JVCell.Value = "CS" & Int(Now() * 100000)
Else
    MsgBox "JV Number Has already been created "
End If
End Sub

 

[Ohyaisee]

Hi Footoo. Thanks for suggestion but I need it to be 10 digits including a two-letter prefix. My thought was a two-letter prefix followed by the date generated followed by a random number between 100 and 999.

 

[footoo]

Sub CheckJV()

Dim JVCell As Range, x
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)
    Randomize
    B = WorksheetFunction.RandBetween(100, 999)
    JVCell.Value = "CS" & A & B
    Sheets("JV").Protect
Else
    MsgBox "JV Number Has already been created "

End If

End Sub

 

[Ohyaisee]

Thanks for the insight footoo. I still have the same issue though. When I open the sheet the field does get populated but it is always populating with the same last three digits "804". I am looking for a way to populate with a new three-digit combo each time it is opened. I tried entering in the Excel Object 'ThisWorkbook' but I have inherited this sheet and there is already code there with the headers before the code "Option Explicit' & 'Option Base1'. I am fairly new to vba and that is way over my knowledge level. Do you think it may be the way I have structured:
In module1 I have a code:

Sub Auto_Open()
Call CheckJV

End Sub

Then in another module I have thecode you had previously posted:

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

 

[Ohyaisee]

Thanks for the insight footoo. I still have the same issue though. When I open the sheet the field does get populated but it is always populating with the same last three digits "804". I am looking for a way to populate with a new three-digit combo each time it is opened. I tried entering in the Excel Object 'ThisWorkbook' but I have inherited this sheet and there is already code there with the headers before the code "Option Explicit' & 'Option Base1'. I am fairly new to vba and that is way over my knowledge level. Do you think it may be the way I have structured:
In module1 I have a code:

Sub Auto_Open()
Call CheckJV

End Sub

Then in another module I have thecode you had previously posted:

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

Also forgot to mention that when I run the macro when the workbook is open the correct functionality is present, so this makes me think it is something to do with the structure upon open but I do not know what would cause this. Any assistance is greatly appreciated.

 

[Ohyaisee]

Also forgot to mention that when I run the macro when the workbook is open the correct functionality is present, so this makes me think it is something to do with the structure upon open but I do not know what would cause this. Any assistance is greatly appreciated.

Thank you very much footoo. Your code did work after all. I missed typing a piece of it. Thanks again for your help. I greatly appreciate it!