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

[shawthingz]

Hi Folks,

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 a little bit "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 what they can actually enter). Based on the value of 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

 

[shawthingz]

Hi All,

I got a response from another forum & wanted to share the solution with you for info:

Basically, the simplest thing to do seems to be to actually include the VBA code inside the worksheet itself (rather than trying to do it in a separate module).

In my scenario, I wanted to lock / unlock various cells based on the contents of cell $E$3 on Sheet2 (the value of which can be 1 - 100). Also, the sheet is password protected for security reasons as the company I work for is ISO27001 compliant.

Anyhow, here's the code I dropped into the 'Sheet2' Excel Object for reference (in case anyone else out there is struggling to do something similar):

Hope this helps the Good Cause!




Private Sub Worksheet_Change(ByVal Target As Range)


If (Target.Address = "$E$3") Then

'I didn't need this as no application warnings were generated (but others might), so I've included it for info in case it's needed by someone out there
'Application.EnableEvents = True

'Only needed if your worksheet is password protected
Sheets("Sheet2").Unprotect Password:="the.worksheet.password"

'Unlock the relevant cells based on the value in $E$3
For row_number = 7 To Cells(3, 5).Value + 7 - 1

Cell_Range_To_Unprotect = "D" & row_number & ":F" & row_number

Range(Cell_Range_To_Unprotect).Select

Selection.Locked = False

Next row_number


'Clear other cell contents & lock all other rows (just in case the number in $E$3 is reduced)
For row_number = Cells(3, 5).Value + 7 To 106

Cell_Range_To_Protect = "D" & row_number & ":F" & row_number

Cells(row_number, 4) = ""
Cells(row_number, 5) = ""


'FYI, I initially used Range(Cell_Range_To_Protect).Clear but it wiped out EVERYTHING associated with the cells, (including ALL my conditional formatting & formulae), which 'mucked up' other sheets referencing these cells (hence setting the cell contents to "" instead)


Range(Cell_Range_To_Protect).Select

Selection.Locked = True

Next row_number



'Set the Cursor back to the cell containing the first active cell ($D$7)
Range("d7").Select

'Only needed if you want to re-protect the worksheet when this has completed
Sheets("Sheet2").Protect Password:="the.worksheet.password"

'BTW, don't forget to switch the application events back on again (if you disabled them earlier)!!
'Application.EnableEvents = True


End If

End Sub