Generate Random Number Button

[csimpson24]

Hi all,

I am wanting to have a random number generate button that generates a random number (between 5000 and 20000) in cell J2. I am needing to distribute the file as a XLTX. I would like for the button to only show until it is clicked to generate the random number. The workbook is protected and Cell J2 is locked so it would need to unlock, write the number then lock again and then disappear. Is this all possible?

Thank you,
Chris

 

[louisH]

Hi.

.XLTX files are macro-disabled. You can't execute any vba code in such a file.
In order to make a macro work you'll have to save it as a .XLTM file

To generate a random number between 5000 and 20000 without VBA you can use this function in J2.

=INT(RAND()*15000+5000)

 

[csimpson24]

Thank you. I totally forgot about that. I will distribute a .XLTM then.

My concern is that I only want to generate a number only the one time and have it never change in the .XLSX file.

 

[mikerickson]

Get a Button from the Forms menu and attach this macro to it.

Sub Button1_Click()
    With ActiveSheet
        .Unprotect
        .Range("J2") = WorksheetFunction.RandBetween(5000, 20000)
        .Shapes(Application.Caller).Visible = False
        .Protect
    End With
End Sub

 

[csimpson24]

Thank you! Is it possible to prevent them from clicking, seeing, or executing the macro if they have the XLTM file opened? Or can I prevent them from opening the actual XLTM file without having a password?

 

[mikerickson]

Thank you! Is it possible to prevent them from clicking, seeing, or executing the macro if they have the XLTM file opened?

The code and the button would be in the XLTM file, so it must be opened if the macro is to be run.