Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-04-2017, 02:34 PM
genpromo genpromo is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2010 32bit
Novice
VBA Unprotect Input Date By Offset
 
Join Date: May 2017
Posts: 3
genpromo is on a distinguished road
Default VBA Unprotect Input Date By Offset

Please need help to solve the programming :
unprotect sheet if the cell is selected and protect sheet again after the run vba completed.
The column 2 is for the input "IN" and protect the column 4, but if I insert command "Sheet1.Unprotect" then the Vba won't work.

Quote:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.count = 1 Then
If .column = 2 And .Row > 1 And .Value = "In" Then
.Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
End If
If .column = 3 And .Row > 1 And .Value = "Out" Then
.Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
End If
End If

End With



End Sub
Any help will be very much appreciated.
Reply With Quote
  #2  
Old 05-04-2017, 07:52 PM
Logit Logit is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

See if this helps (I have not tested this macro):

http://stackoverflow.com/questions/5...ated-worksheet
Reply With Quote
  #3  
Old 05-04-2017, 08:40 PM
genpromo genpromo is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2010 32bit
Novice
VBA Unprotect Input Date By Offset
 
Join Date: May 2017
Posts: 3
genpromo is on a distinguished road
Default

Thank you Logit for reply, I have opened your link and try to modify.
But still protected in column 4. Any suggestion ?
Reply With Quote
  #4  
Old 05-04-2017, 09:06 PM
Logit Logit is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

This works down to Row 4, although the code should be good down to Row 100.

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim inputRange As Range


Set ws = Worksheets("Sheet1")
'tell this sub to unprotect only these cells
Set inputRange = Range("B2:B100,C1:C100")


' If the selected cell is not in the range keep the sheet locked
If Intersect(Target, inputRange) Is Nothing Then
'else unprotect the sheet by providing password
'(same as the one that was used to protect this sheet)
Else

    ws.Unprotect Password:=""
    Target.Locked = False
    With Target
    If .Cells.Count = 1 Then
        If .Column = 2 And .Row > 1 And .Value = "In" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
        If .Column = 3 And .Row > 1 And .Value = "Out" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
    End If
    
    End With
    ws.Protect Password:=""

End If

End Sub
It's late here ... need some sleep.

See what you can do with it.

Steps:

Paste the code.
Protect Sheet1 (no password).
Start B1 and go down entering In.

Same for C1
Reply With Quote
  #5  
Old 05-04-2017, 10:53 PM
NoSparks NoSparks is offline VBA Unprotect Input Date By Offset Windows 7 64bit VBA Unprotect Input Date By Offset Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

@ genpromo
How about you attach a sample sheet with your code and the same cell locking and sheet protection as you're wanting us to suggest a solution for. Then we can test things on the same thing you're using, no guessing as to what's what.

To attach a sheet click Go Advanced > Manage Attachments
Reply With Quote
  #6  
Old 05-05-2017, 06:59 AM
Logit Logit is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Believe this is the solution ... Changed the macro slightly :

New Code :

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim inputRange As Range
Set ws = Worksheets("Sheet1")
ws.Unprotect
Application.EnableEvents = False


'tell this sub to unprotect only these cells
Set inputRange = Range("B1:C100")


' If the selected cell is not in the range keep the sheet locked
If Intersect(Target, inputRange) Is Nothing Then
'else unprotect the sheet by providing password
'(same as the one that was used to protect this sheet)
Else

    
    Target.Locked = False
    With Target
    If .Cells.Count = 1 Then
        If .Column = 2 And .Row > 1 And .Value = "In" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
        If .Column = 3 And .Row > 1 And .Value = "Out" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
    End If
    
    End With
    
End If
Application.EnableEvents = True
ws.Protect

End Sub
Before using the above code, highlight Columns B & C. Right click either of the highlighted columns / select FORMAT CELLS ...

Choose the PROTECTION TAB. Make certain LOCKED is UNCHECKED. (Because you highlighted the two Cols B&C, this 'unlocked setting' will only apply to these two columns).

Click OK, etc.

Now, go to the MENU BAR up top / REVIEW / PROTECT SHEET. Protect the sheet without providing a password - leave that blank.

The macro should now work as desired.

If you choose to utilize a password the macro will need to be edited to include that password in the code. The code as presently written applies to rows 1 to 100.
Reply With Quote
  #7  
Old 05-05-2017, 08:02 PM
genpromo genpromo is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2010 32bit
Novice
VBA Unprotect Input Date By Offset
 
Join Date: May 2017
Posts: 3
genpromo is on a distinguished road
Smile

@Logit : Thank you for the code, very much appreciated for your help. Do not know what to do without this forum

@Nosparks : here is the file after the code change from Logit.

This file for attendance employee, so they can absence by themselves but can not change the attendance record.
Attached Files
File Type: xlsm GH - Alun 2017 - Copy.xlsm (22.0 KB, 9 views)
Reply With Quote
  #8  
Old 05-05-2017, 08:14 PM
Logit Logit is offline VBA Unprotect Input Date By Offset Windows 10 VBA Unprotect Input Date By Offset Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Glad to help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unprotect Cells Cardinal2 Excel Programming 2 01-06-2015 05:15 PM
VBA Unprotect Input Date By Offset Set offset properties of a picture charm PowerPoint 1 11-01-2014 05:41 AM
VBA Unprotect Input Date By Offset Formula In Excel To Prompt User To Input Date pclutts Excel Programming 4 08-31-2014 12:45 PM
VBA Unprotect Input Date By Offset Syntax to use OFFSET() in a cell formula? dlowrey Excel 3 07-09-2013 09:15 AM
ADD in That can unprotect the documents. aligahk06 Word 0 07-12-2010 04:55 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft