About Us

Leox5000

Hi,

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

Code:

Private Sub Worksheet_SelectionChange1(ByVal Target As Range) 
    Dim myrow 
    Dim StarLock, EnLock 
    StarLock = 19 
    EnLock = 46 
     
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Application.CutCopyMode = False 
     
     
    If Target.Column = 1 Then 
        myrow = Target.Row 
         
        ActiveSheet.Unprotect 
        If Cells(myrow, 1) = "Vacancy" Then 
             
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 16
             '.Pattern = xlSolid
             'End With
            Selection.Locked = True 
            Cells(myrow, 1).Select 
            ActiveSheet.Protect 
        End If 
         '    Else
        If Cells(myrow, 1) = "Applicant" Then 
             
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 34
             '.Pattern = xlSolid
             ' End With
            Selection.Locked = False 
            Cells(myrow, 1).Select 
            ActiveSheet.Protect 
        Else 
             
             '        Range(Cells(myrow, Starlock), Cells(myrow, Enlock)).Select
             'With Selection.Interior
             '.ColorIndex = 2
             '.Pattern = xlSolid
             'End With
             '        Selection.Locked = False
             '        Cells(myrow, 1).Select
             '        ActiveSheet.Protect
        End If 
    End If 
     
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
    Application.CutCopyMode = True 
     
    ActiveSheet.Protect 
End Sub

I am unable to upload the file to this website due to security restrictions but it can be found he
https://rcpt.yousendit.com/143737095...889d657f64b494

Many Thanks,
Leox5000

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data Validation, Drop Down Lists	Jesse Blanchard[_2_]	Excel Programming	1	September 13th 08 11:31 AM
Locking Data Validation Drop Down	Joe D	Excel Discussion (Misc queries)	0	June 12th 08 02:34 PM
how do i protect a worksheet without disabling drop downs lists?	BME	Excel Programming	1	August 28th 07 10:55 PM
Validation drop-down lists	Lee	Excel Worksheet Functions	1	January 5th 06 06:38 PM
VBA code silently crashing. Problem with Data Validation drop down lists.	Don Wiss	Excel Programming	6	February 27th 05 09:46 AM