Insert a formula to next blank cell from changing conditions in userform

  • Not sure what i should have named the title. Sorry in advance

    i have a sheet with 3 columbs. A,B,C. Columb A is a number And so is Columb B. I have a userform that enters the numbers to the sheet automaticlly. Here is what i need. Colum A is a minimun quantity and Columb B is an actual quanity on hand. I need a code that will check the userform Boxs for Min And Actual quanitys and prepare a formula for every entry and than place the formula in columb c for every entry.

    Example 1

    Userform Box Min has 2 in it. And Userform Box Actual has 1 in it. I want Columb C to show "Order" But if the Actual is higher than the Min then show "In Stock". But because the differance in the numbers will depend on the 2 entry boxes the code eludes me.

    I would rather this be done in vba so others cant mess with formulas.
    I have it working without vba but i dont like it.
    Thanks guys

    If this isnt clear please let me know.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Insert a formula to next blank cell from changing conditions in userform


    A sample file would help a lot with these kind of problems. In general it is always better to do as much as possible with formulas rather than VBA. Certainly entering formulas with VBA needs to be considered carefully.

    If your concern is users tampering with the formulas then use cell locking/unlocking and protect the sheets. Alternatively it may even be more approprate to hide some sheets completely.

  • Re: Insert a formula to next blank cell from changing conditions in userform


    Others may make different suggestions, but I would be inclined to set up the sheet with the formulas and then have the UserForm just populate the data. You can then protect the sheet to prevent tampering. If you want to make the sheet look a bit tidier then you can replace your formula with:

    =IF(OR(ISBLANK(A2),ISBLANK(B2)),"",IF(B2<=A2,"Order","In Stock"))

    Having 'unused' formulas really does not matter. Excel is quite efficient about only performing calculations when necessary, so in your case the unused formulas will have little or no impact on performance.

    To protect the sheet but allow the UserForm to modify it you will probably want to use Protect UserInterfaceOnly:=True.

  • Re: Insert a formula to next blank cell from changing conditions in userform


    Thanks Rob, I will give this a shot. I really appreciate all the help. This forum ROCKS!!!

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!