VBA Userform to generate next available number

[NaumanJ]

Hi there,

I am trying to create a userform that will present me with the next available phone number (based on Switch type). Once the phone number is generated, and the user confirms they will be using it, the number's status will go from inactive to active.

Here's a mock of how the raw data looks like:

Switch Type	Phone Number	Activity
1	9050000001	Active
1	9050000002	Inactive
1	9050000003	Inactive
2	9050000004	Active
2	9050000005	Active
2	9050000006	Inactive
3	9050000007	Inactive
3	9050000008	Inactive
3	9050000009	Inactive
4	9050000010	Active
4	9050000011	Active
4	9050000012	Inactive
5	9050000013	Inactive
5	9050000014	Active
5	9050000015	Inactive

Here's a mock of how the userform will look:

This is the flow I'm looking for:

1. User selects the switch type they're looking for (from 1 to 5)
2. User specifies whether they want an Active or Inactive number (The user will always select "inactive" number, but I have this here to show that the phone generator requires two criteria. I can remove from userform if it's possible to account for it in the code)
3. After they've specified what they want, they will click "Generate Phone Number"
4. The phone number will show in the textbox below "Here's your phone number"
5. If the user says "yes" or "no" to "confirm if they will be using this phone number", it will either change the Activity of the phone number to "Active" (if Yes), or leave it the same as "Inactive" (if No)

Any help is greatly appreciated with this. Please let me know if you require any more information - thank you so much !!

 

[mumps]

Because any macro will need to refer to the names of the objects used in the user form, it would be easier to see that information if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[NaumanJ]

Because any macro will need to refer to the names of the objects used in the user form, it would be easier to see that information if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Awesome recommendations - thanks! I've uploaded the file on Dropbox (ready to be edited): Phone Number Generator.xlsm

I have three sheets in the file:
1) The actual userform (you can see that it's designed, it just doesn't have any code to make it function right now)
2) Mock data (listing of phone numbers and their respective activity)
3) Labels (to identify the 5 switch types and 2 activity options)

This is the flow I'm looking for:

1. User selects the switch type they're looking for (from 1 to 5)
2. User specifies whether they want an Active or Inactive number (The user will always select "inactive" number, but I have this here to show that the phone generator requires two criteria. I can remove this from the userform if it's possible to account for it in the code)
3. After they've specified what they want, they will click "Generate Phone Number"
4. The phone number will show in the textbox below "Here's your phone number"
5. If the user says "yes" or "no" to "confirm if they will be using this phone number", it will either change the Activity of the phone number to "Active" (if Yes), or leave it the same as "Inactive" (if No)

Literally any help will be greatly appreciated - thanks a million !!

 

[mumps]

Click here to download your file.

 

[NaumanJ]

You are an absolute gem - thank you so so much!!!

Stay safe and have an amazing day

 

[mumps]

You are very welcome.

 

[NaumanJ]

You are very welcome.

Hey Mumps,

Thanks so much for helping me with this I need to make some changes to add one more component, but I've been really struggling with this for the past few days. If you have some free time, I was wondering if you can help me with this? It'd be greatly appreciated, but you've already done so much, so I understand if you don't want to.

Here's what I'm trying to do:

It's the same userform, except now, I'm trying to add an option for "specify amount of numbers required", as team members can request multiple numbers (sometimes 50+) at one time, so it's tedious for them to keep generating numbers one by one.

I've updated the form to reflect how it should look - I just haven't been able to make it functionally work after a lot of trial-and-error.

This is how the flow would look:

1. User selects the switch type they're looking for (from 1 to 5)
2. User specifies the amount of numbers they need (FYI - all the numbers would be for the same switch. So if they selected Switch 1, it would only be for that switch, and not a combination of switches)
3. After they've specified what they want, they will click "Generate Phone Number"
4. The phone numbers will show in the main "PN Generator" sheet, starting from Cell (A10), pasting downwards for the numbers specified
5. If possible, but not a requirement, it they state they want more numbers than their are available, it will open a message box saying "you have selected too many numbers, please lower your selection" or something along those lines (of course it'd be great if it can tell the number of available numbers for that switch, but that sounds too complex)
5. If the user says "yes" or "no" to "confirm if they will be using this phone numbers", it will either change the Activity of the phone number to "Active" (if Yes), or leave it the same as "Inactive" (if No)

This is the link to the older file: Old Phone Generator.xlsm
This is the link to the newer file: New Phone Generator.xlsm

This has been driving me nuts so I would really really appreciate any help. Thank you so much in advance

 

[mumps]

Click here to download your file. I've had to unmerge two merged ranges and formatted them as "CenterAcrossSelection" to give the same effect as merging. You should avoid using merged cells as they almost always cause problems for macros. Since there was a possibility that a user might choose more phone numbers than were available as "Active" for any given switch type, I have incorporated a warning if this happens that asks the user to select a smaller number.

 

[NaumanJ]

Click here to download your file. I've had to unmerge two merged ranges and formatted them as "CenterAcrossSelection" to give the same effect as merging. You should avoid using merged cells as they almost always cause problems for macros. Since there was a possibility that a user might choose more phone numbers than were available as "Active" for any given switch type, I have incorporated a warning if this happens that asks the user to select a smaller number.

Mumps - you are an angel!!! I can't thank you enough - you are awesome. Sending you good wishes !!

 

[mumps]

You are very welcome.