Trouble finding vba code to create random generated number upon opening

Ohyaisee

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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 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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
Try adding Randomize at the top of the code.
 

Ohyaisee

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Sorry I am learning the site. Did not tag code. Where would I add Randomize?


VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,249
Office Version
  1. 2016
Platform
  1. Windows
If you can use 10 digits instead of 8, this would ensure their are no repeated numbers :
VBA Code:
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

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,249
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 
Solution

Ohyaisee

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
Sub Auto_Open()
Call CheckJV

End Sub

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

VBA Code:
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

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
Sub Auto_Open()
Call CheckJV

End Sub

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

VBA Code:
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

New Member
Joined
Jan 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,845
Members
418,155
Latest member
demasisi

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
Top