Auto-Running a VBA Macro Based on the Value Within a Cell

Hi,

Apologies if this has been asked before, I’ve not been able to find a similar thread on this topic myself (but if you know of one, please point me in the direction of it!)

We work with 3rd party companies who can submit new User requests in batches (1 – 100) to us from time to time & we use an Excel workbook for them to send us the User details to auto-generate their UserIDs & login credentials. Not the most elegant solution (I know), but it kind-of works for us

I already have a workable solution (but it’s rather “clunky”), so here’s what I’m ideally trying to do (with a bit of background info):

Cell E3 contains the number of new Users they want to submit (using a formula-based Data Validation to limit values that they can actually enter). Based on the value entered into E3, I’d like to automatically unprotect various other cells on the worksheet so they can enter the relevant number of User details ONLY. Some examples are:

E3 = 0 -> Cells D10:F109 are all locked
E3 = 1 -> Cells D10:F10 are unlocked: Cells D11:F109 remain locked
E3 = 10 -> Cells D10:F19 are unlocked: Cells D11:F109 remain locked
E3 = 100 -> Cells D10:F109 are unlocked

Note that the worksheet is also password-protected (for what it’s worth).

Is what I’m trying to do even possible? If so, the (VBA?) solution would need to work with Excel97-2003, 2007 & 2010.

Thanks for any pointers on this.

shawthingz

3 thoughts on “Auto-Running a VBA Macro Based on the Value Within a Cell

  1. shawthingz

    Many thanks for the reply!

    I’ll play around with this over the coming weeks & hopefully get the result I’m looking for 🙂

  2. shawthingz

    Hi Again! Don’t bother publishing the response above – I tried the info you provided last night & got it working – thanks! 🙂

    FYI, one thing that might be worth adding to the other page is that if the sheet is protected, it will need to be unlocked first.

    Here’s what I did (ish):

    ‘Unprotect the worksheet
    Sheets(“WorkheetName”).Unprotect Password:=”a.password”

    ‘Protect the worksheet again
    Sheets(“WorksheetName”).Protect Password:=”a.password”

    Hope this helps for amazing site! 🙂

    shawthingz

Leave a Reply

Your email address will not be published. Required fields are marked *