Thread: disable allow until tier * Height is correct

I have a workbook that people in my company use as a form to create New Items for the company. I have it set up so I can do as much work as possible to prevent errors. Our Warehouse only has two height allowances for our pallets 48" or 62" so I have it set up that when (Case Height * Tier (how many per level)) > Pallet Height I pop up an error message and I want to force the user to fix the error before going forward. So what I am trying to do is make the ok button disabled until they correct the error. However, no matter what I try with code it is not working. Here is the workbook to test.

In order to test:

1) you need to set a storage zone
2) type a number in Case Height
3) type a number in tier
4) Case Height * tier needs to be larger than Pallet height

plus if anyone has any other suggestions for the workbook they are welcome.

HI djblois1,

Cute problem - Change your code behind New Item sheet to -

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dblTier As Double
    Dim dblblock As Double
    
    Application.ScreenUpdating = False
    
    dblTier = Range("X14").Value
    dblblock = Range("X10").Value
        On Error Resume Next
        frmError.Hide
        On Error GoTo 0
    If dblTier * dblblock > Range("X12").Value Then
        frmError.Show
        Range("X14").Value = Range("X14").Value
    End If
    
    Application.ScreenUpdating = True
    

End Sub

and test like mad....

The intent is to let the frmError plug values back in and then trigger the change event on the sheet again, so it tests again.

I sure hope this works.

That will not allow me to change the values because each time it pops back on screen it uses the old values. Plus I think it would be more user friendly if it disabled the Ok button until the values are correct. if a user keeps hitting the ok button and it just pops right back up they may think that it is not working correctly.

Hi,

I thought it used the new values that were put into the Form. Then it would check the new values and only come back up if the new values didn't work. Check the code again.

You might change the event which triggers the Change Event so it doesn't plug X14 back into X14.

I did it a few times and it did not work.

What do you mean "change the event which triggers the Change Event so it doesn't plug X14 back into X14"?

Hi,

You have essentially two peices of code. You have a Userform called frmError and code behind the New Item Sheet. The code behind sheets is really Event Driven Code. This means the code that goes there works (is fired) when things happen on that sheet. You can trigger the code on a worksheet by stuff like double clicking on the sheet or Activate the sheet or in your case when something changes on the sheet.

This event called Worksheet_Change is triggered each time something on the worksheet changes. You then capture this event and see if you want to deal with it in your code (behind the worksheet).

You use this code to see if two cells multiply together to be bigger than another cell. (I see a forklift driver trying to put a stack of pallets in a low height room - each time I do this problem.)

If these the product is bigger than the room height you TRIGGER the event on the New Item sheet and it throws up the Warning frmError and the guy has to change something. (We're hoping s/he does or the forklift driver is toast!)

So how do you get the form to come up again if the product is still too big? You can't show an open form again because Excel thinks we're stupid to even try. So we hide it and say if it is an error then - never mind - and just resume next step. Just after that we turn Error watching back on witht he On Error goto 0.

NOW - The in the frmError code. When you click ok - I thought it wrote the values back into the cells X14 and X10. These are the new values in the New Item Sheet. Now you need to trigger the New Item sheet to think something has changed, so it saves the forklift driver. You need to do something on the sheet that triggers this worksheet event. I thought that putting X10 back into X10 would trigger this event.

I'm now reading http://www.mvps.org/dmcritchie/excel/event.htm and http://www.ozgrid.com/VBA/run-macros-change.htm to see if it will.
Sometimes http://msdn.microsoft.com/en-us/libr...ge(VS.80).aspx tells more but not here.

I don't know what didn't quite work. When I saw the frmError, I typed in different and smaller values for the two input boxes until the frmError went away.

Gosh - I hope this helps.