Custom - Limit cell input or VBA pop-up message

[Gtasios4]

Hi all,

In the below configuration tool I'm trying to find a way to set some numeric restrictions in M column regarding of the extra parts (memory, hd) a user can add based on the pick-up drop-down list (cell B2)

So for instance, if the user chooses a "Model Crystal 570x"(cell B2) then in rows 5, 6 and inserted rows could add only 3 units (cell M5) of memories, therefore once it types 4 QTY a message pops up.

I've searched a bit on google on how to do that, but I couldn't find any relative solutions, even with Custom - Limit cell input; maybe is it possible with VBA?

Any help on that would be much appreciated.

 

[Dan_W]

You can definitely do something like that with VBA, but I'm not sure that you need to. Unless I'm misunderstanding what you're after, it seems like all you would need to do is set some data validation rules (See Data tab - > Data Validation).

Here, it would allow you to select numeric limits on something. If you want those limits to be dynamic, and dependent on the quantity available (stored elsewhere, say in cell AA1 for example), you would just need to enter that information here. The Error Alert tab above would also allow you to customise the error message.

Does that sound like it might help?

 

[Gtasios4]

You can definitely do something like that with VBA, but I'm not sure that you need to. Unless I'm misunderstanding what you're after, it seems like all you would need to do is set some data validation rules (See Data tab - > Data Validation).

View attachment 64579

Here, it would allow you to select numeric limits on something. If you want those limits to be dynamic, and dependent on the quantity available (stored elsewhere, say in cell AA1 for example), you would just need to enter that information here. The Error Alert tab above would also allow you to customise the error message.

Does that sound like it might help?

Hi Dan,

I've think a bit on it, and it seems data validation doesn't look the best option for what I want to do, because the msg box should pop-up based on the value the user picks up from the drop down list in cell B2. So, I need somehow to create pop-up messages for each value in the drop-down list coupled with a change event code; when I pick another value then the attached msg box should pop-up and so on..

Frankly speaking, I don't know how to create such code

best regards

 

[Gtasios4]

You can definitely do something like that with VBA, but I'm not sure that you need to. Unless I'm misunderstanding what you're after, it seems like all you would need to do is set some data validation rules (See Data tab - > Data Validation).

View attachment 64579

Here, it would allow you to select numeric limits on something. If you want those limits to be dynamic, and dependent on the quantity available (stored elsewhere, say in cell AA1 for example), you would just need to enter that information here. The Error Alert tab above would also allow you to customise the error message.

Does that sound like it might help?

The primary value that determines the fill-out of cells (ranges B4:B, M4:M) is cell B2. If I can set somehow some restrictions on the qty column for each value of the drop-down list of cell B2, that could also help me achieve towards to what I want to do. To help you a bit, when the basic brand is chosen in B2 cell then in cells B5:B shows dependent drop down lists and XLOOKUP runs to give item code on A column..

If its helps to create some code scenarios such as "When the user chooses a "Model Crystal 570x"(cell B2) and then in cell A5:A appears the item code 45##### then in M5 set restrictions to add only 3 units, if it adds 4 then pop-up message (you exceeded the maximum number that you can add) etc etc..

 

[Dan_W]

Hi. So your two messages are saying opposite things - in the first response, you've said

"the msg box should pop-up based on the value the user picks up from the drop down list in cell B2."

and

"when I pick another value then the attached msg box should pop-up and so on.."

which indicates that once the user has made a selection from the dropdown list in cell B2, a message box appears, presumably from which a quantity is to be entered. This would certainly need VBA.

But in the second response:

"When the user chooses a "Model Crystal 570x"(cell B2) and then in cell A5:A appears the item code 45##### then in M5 set restrictions to add only 3 units, if it adds 4 then pop-up message (you exceeded the maximum number that you can add) etc etc.."

Specifically, you said "if it adds 4 then pop-up message", which definitely sounds like data-validation would be the best option.

If this workbook is to be distributed to people and used on different computers, one thing you need to bear in mind as a practical matter is that the user has to enable macros and (following a new decision my MS) take an additional step specifically enable VBA to work for that file downloaded from the internet. The VBA option is entirely doable, I just want to make sure I understand what you're after and that the end-solution properly fits your needs.

 

[Gtasios4]

Hi. So your two messages are saying opposite things - in the first response, you've said

and

which indicates that once the user has made a selection from the dropdown list in cell B2, a message box appears, presumably from which a quantity is to be entered. This would certainly need VBA.

But in the second response:

Specifically, you said "if it adds 4 then pop-up message", which definitely sounds like data-validation would be the best option.

If this workbook is to be distributed to people and used on different computers, one thing you need to bear in mind as a practical matter is that the user has to enable macros and (following a new decision my MS) take an additional step specifically enable VBA to work for that file downloaded from the internet. The VBA option is entirely doable, I just want to make sure I understand what you're after and that the end-solution properly fits your needs.

Dear Dan,

Thank you for your advice and your kind information. That excel will be used internally by my team and colleagues, so it won’t be distributed on internet.

Just to set some things right and not confusing you, regarding the below:

“which indicates that once the user has made a selection from the dropdown list in cell B2, a message box appears, presumably from which a quantity is to be entered. This would certainly need VBA.”
That’s correct. What I want to do though is to set some quantity restriction statements. Is there any VBA code for such case scenarios to set in cells M5:M specific quantities for item codes found in A5:A based on the value selected in cell B2? For now lets skip msg box.

Thanks in advance

 

[Gtasios4]

Hi Dan,

I've thought again and I will close that thread and open another more targeted and clear as to want I want to do. Thanks for your help though!

 

[Dan_W]

Hi - sorry - I had a long weekend break and am only just checking back in now. Are you certain? Happy to have a look at it again if you like.

 

[Gtasios4]

Hi - sorry - I had a long weekend break and am only just checking back in now. Are you certain? Happy to have a look at it again if you like.

Dear Dan,

Thank you for reverting. I've created a new thread that maybe you can help me towards what i want to do VBA pop-up message box for each selection of a drop-down list