PDA

View Full Version : [SOLVED] how to create a excel macro (button) that will show next number from a list?



Rado88
12-30-2017, 04:06 AM
Hi all,

I'm trying to create macro for my job, but I'm not sure how to do this. I googled through many topics on this forum and YT videos, but I'm still not sure how to achieve this.

What I need to do?

Problem 1:

I have one sheet in excel called "sheet2" with list of numbers from 1 to 60. This list is random so not 1,2,3,4,5 but 1,16,52 etc. I need to create a macro button that will draw the next number from this list and populate it to a different sheet in the same excel file (sheet1, field B8). The button will be located in sheet2 and the numbers are situated in fields B2 to B61.

Problem 2:

I need to enable this macro only for certain people, so I have to protect it with a password or what would be the best protect it and hide the whole sheet from other people who can see only sheet 1. Is there any way to block the macro button so that only certain users may be able to use it? Is there any way to also show who and when pressed this button?

Problem 3:

This is the biggest problem. This will be a read only file used by many people so it won’t save. Is there any way to make excel remember which number from problem 1 was drawn, despite the copy did not save when previous user closed original excel?
I already created a macro that saves a copy of excel file with the data that someone created in sheet 1. However I doubt that it’s possible that it’s possible to create a macro to save such information for many newly created copies (to remember which number was lastly used on all of those).

Thank you very much in advance for any help.

Rado

offthelip
12-30-2017, 10:22 AM
cross posted:
https://www.mrexcel.com/forum/excel-questions/1036931-3-big-problems-macro-please-help.html

Please obey the rules about cross posting

Rado88
12-31-2017, 03:24 AM
Hi Offhelp:

I also posted this on two other forums, but I still didn't manage to create a macro that would help me fix my issue. I tried to google for few days to find a solution to this issue, and tried to finish this macro on my own, but with no success. Therefore i decided to ask for help on few forums.

Sorry if I broke any rules, however I'm unable to post links here so I can't provide the links to my other posts (it's possible from 5 messages).


Could you please help me with my problem?

Aussiebear
12-31-2017, 01:45 PM
It is still possible to indicate what other forums this issue has been posted into.

MARK858
01-01-2018, 01:19 PM
Just for info the link to the other cross-post mentioned is to Excel Forum. Put as a text string below as I can't post a link.

excelforum.com/excel-programming-vba-macros/1213759-how-to-create-a-excel-macro-button-that-will-show-next-number-from-a-list.html#post4812221

Rado88
01-01-2018, 04:48 PM
Yes - problem solved, sorry for the confusion and cross posting. I'll make sure not do this again :(

Paul_Hossler
01-01-2018, 04:51 PM
@Rado88 -- you can mark this as [Solved] using [Thread Tools] above your first post