EXCEL - SELECTIVELY UNLOCK CELLS

[BobJacksonNCI]

I need to be pointed in the right direction.

If someone has done this, sample VBA code might get me going. (I used VBA several years ago with Access. And have a failing memory. If there are differences between using VBA with Access and using it with Excel, advising of those would also be helpful to me.)

Question: How can I take an initial selection in Excel and use that to trigger unlocking other cells which need to be filled in based on that initial selection?

We are using an Excel-based form to document employee changes for entry into the payroll system.
Change types include hire, transfer, promotion, insurance changes, leave of absence, sick time, vacation and separation. (One form fits all.)
As used, the form is printed and then handwritten.
We want our properties to TYPE on the forms and send them as e-mail attachments. In making this operational change, I would like to use the initial selection, new hire for example, to trigger unlocking cells that require input. If promotion was initially chosed, the cells I would want to unlock would be different than the ones for new hire.
Actually, I'm a database guy and would like a web-based database for this, but time constraints dictate doing the best I can with what we've already got.

Thanks in advance for your assistance!
Bob

 

[macropod]

Hi Bob,

There are various ways of doing what you ask for, each of which has advantages and disadvantages. Here's one way:

Use an event-based macro to determine which cells to lock/unlock based on the value entered in the 'Change Type' cell. For example, if the range you want to protect is B2:E4, the following code, placed in the worksheet change event routine, will prevent the user from entering anything into that range.

Private Sub Worksheet_Change(ByVal Target As Range)
	If Intersect(Target, ActiveSheet.Range("B2:E4")) Is Nothing Then Exit Sub
	Application.EnableEvents = False
	MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
	Application.Undo
	Application.EnableEvents = True
End Sub

Alternatively, you could give access to only the range B2:E4 by changing the line:
If Intersect(Target, ActiveSheet.Range("B2:E4")) Is Nothing Then Exit Sub
to:
If Not Intersect(Target, ActiveSheet.Range("B2:E4")) Is Nothing Then Exit Sub

You can lock/unlock multiple ranges this way. You'd always want the 'Change Type' cell unlocked. So building up a scenario, you could have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
    Select Case UCase(Range("A1").Value)
    Case "HIRE"
        If Not Intersect(Target, ActiveSheet.Range("B2:E4")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("A3")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("G4")) Is Nothing Then Exit Sub
    Case "TRANSFER"
        If Not Intersect(Target, ActiveSheet.Range("B2:B4")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("A3")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("C4")) Is Nothing Then Exit Sub
    Case "PROMOTION"
        If Not Intersect(Target, ActiveSheet.Range("C2:E4")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("A2")) Is Nothing Then Exit Sub
        If Not Intersect(Target, ActiveSheet.Range("C4")) Is Nothing Then Exit Sub
        End Select
    Application.EnableEvents = False
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
    Application.EnableEvents = True
End Sub

A potentially significant issue with the above approach is that the savvy user can simply edit the 'Change Type' cell from one option to another to gain access to whatever cells they want, before setting the the 'Change Type' cell back to the 'correct' value. I should also point out that the above acode doesn't really lock/unlock cells in the sense that these terms are normally used in the Excel environment - it simply allows/denies access.

To get to the Worksheet change event routine in the VBA Project Explorer, right click on the sheet's name tab and select View Code in the pop-up menu. Then select "Worksheet" in the left drop down list and Change in the right box. You can also use range names for the range parameter instead of cell addresses. The advantage of doing this is that range names adjust as rows & columns are inserted/deleted. The only problem with using range names is that the user can defeat the protection by changing the named range. The names can be hidden from code like:
Thisworkbook.Names("_MyRangeA").visible=false

You could enhance the code to actually lock/unlock cells, which you could then couple with code to restrict cursor movement to unprotected cells (which might be useful), but this also requires invoking worksheet protection/unprotection. Beyond restricting cursor movement to unprotected cells, this still wouldn't overcome the issues associated with users changing the 'Change Type' cell from one option to another.

Cheers

 

[BobJacksonNCI]

Thank You!

Once Christmas has passed, I will make GOOD use of one/both of your suggestions.

Bob

 

[SkipVought]

Bob,

One of the great features of Excel is the ability to Name Ranges. Names can help to clarify and document your intent.

Then it's just a matter of

[rngDataEntry].ClearContents

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}