-1

i'm quite new to Excel VBA and formulas but i need to have a cell with random values on the form that i had created to create like a unique id (I know that there might be chances of getting duplicate numbers but it is slim) to send it to people to fill up a form but the thing is that if im using the RandBetween(00000000,99999999), hiding and unhiding just keep activating the formula which is hard for me to keep track if i were to send this template for others to fill up. So is there any way where when i have the formula and when the user opens the Excel form, it will randomize a 8 digit number and copy and special pasting the value overwriting it to freeze the value. (Other ways are also gladly welcomed, thanks in advance guys)

ZQ7
  • 97
  • 11
  • Yes, there's a way. *Several* ways actually. So many ways that this question is too broad for this site. Try something, lookup how to implement something that does what you want it to do. When you hit a road block - a *specific* programming issue - and can't find anything on this site to help you, *then* you have an answerable SO question. See [ask] for more information. – Mathieu Guindon May 16 '17 at 03:28
  • But that's my road block already, i cant get it to record the first randomized number that is on the form and to copy andpaste it so that when user send it back to me, i can get a freezed 8digit ID @Mat's Mug – ZQ7 May 16 '17 at 03:32
  • 2
    Speaking of *record*, did you try the *macro recorder*? *Record* yourself copying and pasting, see what code gets generated for your every action. Adapt it as needed, and make sure you [read this](http://stackoverflow.com/q/10714251/1188513) to remove all the `.Select` and `.Activate` going on. – Mathieu Guindon May 16 '17 at 03:34
  • 1
    [This](http://stackoverflow.com/q/20119277/1188513) question (and answer) should get you somewhere. – Mathieu Guindon May 16 '17 at 03:36
  • And, of course, if you wanted to almost absolutely, positively, certainly guarantee a unique id, you could use `Left(CreateObject("Scriptlet.TypeLib").GUID, 38)` to return the sort of IDs used by Windows to uniquely identify things. ( [Universally unique identifier](https://en.wikipedia.org/wiki/Universally_unique_identifier) ) – YowE3K May 16 '17 at 04:21

1 Answers1

1

I'm not sure what you're actually trying to do here, but if you wanted to generate a random number and store its value in a cell, you can use this code in 'ThisWorkbook' in VBA:

Option Explicit

Const MySheetName As String = "Sheet1"      ' Change this to your sheet name

Private Sub Workbook_Open()
    ' Called every time you open the excel document

    Dim myRandNumber As Long
    Randomize
    myRandNumber = CLng(Rnd() * 999999999)    ' CLng converts the floating point number to a long integer

    Dim rowNum As Integer
    Dim colNum As Integer
    rowNum = 3
    colNum = 5

    ThisWorkbook.Sheets(MySheetName).Cells(rowNum, colNum).Value = myRandNumber

End Sub

If you wanted to make sure this number was unique, which is probably what you really want to do, you can store these values in a separate hidden sheet and check these values each time you want to generate a new number.

You could also just timestamp the number using this line of code instead of the random number:

ThisWorkbook.Sheets(MySheetName).Cells(rowNum, colNum).Value = CDbl(Now() * 100000)

This will create a new unique number every 1 second, based on the system time.

If I understand correctly, you want to generate this number once. There are a couple ways I can think of to do this:

  1. Generate the number only if the cell is blank
  2. Set a flag in a hidden sheet

In both cases, if you wanted to generate the number again, you just need to clear the cell containing the number (option 1) or clear the flag in the sheet (option 2)

Option 1 can be done by replacing this part of the above code:

ThisWorkbook.Sheets(MySheetName).Cells(rowNum, colNum).Value = myRandNumber

with this:

With ThisWorkbook.Sheets(MySheetName).Cells(rowNum, colNum)
    If (.Value = "") Then
        .Value = myRandNumber
    End If
End With

Hope this helps

Aspekt
  • 78
  • 1
  • 8
  • Hi there Aspekt! i had tried your code and it works like a charm! but the weird thing is when i clear the cell and reopen the workbook, it gave the same digit which is 705547511 for some reason, any idea why it do that? Thanks! – ZQ7 May 16 '17 at 04:06
  • Ah, yes, you need to call Randomize before using Rnd, else it will give you the same set of values each time. I've updated the answer to include this – Aspekt May 16 '17 at 04:13
  • And is it able to do it only once when opened? Because let's say i save this unique number onto a database and a few days later i were to check on the record with the original file, but since the workbook is open, it will generate another set of code rewriting it over the original unique number, resulting in me not able to match the code from the database which had took the unique id from days back compared to now since it rewrote it over So sorry for the trouble – ZQ7 May 16 '17 at 04:15
  • I think i understand what you're trying to do. I've updated the answer with a check to see if the field is already populated. This will ensure the number is only generated once and won't be overridden by opening the document. – Aspekt May 16 '17 at 04:23
  • Omg it works! Thanks so much Aspekt, didnt know Macros were that powerful, really need to learn more about it! Thanks once again! P.S: The code: With ThisWorkbook.Sheets(MySheetName).Cells(rowNum, colNum) If (.Value = "") Then .Value = myRandNumber End If Is lacking of a 'End With' at the last line Just incase people stumbled across this thread and has the same question as mine, hehe thanks! – ZQ7 May 16 '17 at 05:18
  • Glad I could help. Yep, the thing about coding in a text window is it doesn't throw errors at you :P fixed! – Aspekt May 16 '17 at 05:22