Insert random number only if cell empty

[M.L.]

Hello. I'd like my spreadsheet to place a 7 digit random number into a
cell only the cell is empty, otherwise leave the contents as is. I
simply don't want the number to change if one is already present.

I currently keep the number static by pressing F9 when I enter the
RAND formula, but that forces me to manually change the number when I
save the spreadsheet to another name. I'd like the spreadsheet to
automatically calculate a new number in the cell as soon as I erase
the previous number in that cell. Any help on this issue will be
appreciated. Thanks.

 

[Joe User]

I'd like my spreadsheet to place a 7 digit random number into a
cell only the cell is empty, otherwise leave the contents as is.

In the VBAProject "Sheet1" object (or whatever the name is), add or modify
the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("a1")) Is Nothing Then
'range("a1") is included in the changed range
If Range("a1") = "" Then
Range("a1").NumberFormat = "0000000"
Range("a1") = Int(1E8 * Evaluate("rand()"))
End If
End If
Application.EnableEvents = True
End Sub


This does what you ask for ("only [when] the cell is empty"). Note that
this can be defeated if the user puts something else into the cell.


Note: I use Evaluate("rand()") instead of the VBA Rnd function for a number
of reasons. If you prefer to use Rnd, it would be prudent to place a call
to Randomize in a Workbook_Open event macro or to add the following to the
macro above:

Static notFirst As Integer
If notFirst = 0 Then Randomize: notFirst = 1


----- original message -----