Skip to main content

Conas cealla a chosaint bunaithe ar dháta in Excel?

De ghnáth, is féidir linn an bhileog oibre a chosaint chun cosc ​​a chur ar dhaoine eile luachanna na gcealla a eagarthóireacht nó a mhodhnú, ach, uaireanta, ní mór duit na cealla a chosaint bunaithe ar dháta. Mar shampla, ba mhaith liom cealla eile a chosaint ach gan ach luachanna cille dáta an lae inniu a mhodhnú mar a leanas an pictiúr a thaispeántar, labhróidh an t-alt seo faoi conas cealla a chosaint bunaithe ar dháta.

doc a chosaint faoi dháta 1

Cosain na sraitheanna go léir ach amháin as a chéile dáta an lae inniu le cód VBA

Cosain gach sraitheanna a rith an dáta le cód VBA


mboilgeog cheart gorm saighead Cosain na sraitheanna go léir ach amháin as a chéile dáta an lae inniu le cód VBA

Ná lig ach an tsraith atá cothrom le dáta an lae inniu a mhodhnú, is féidir leis an gcód seo a leanas cabhrú leat, déan é seo le do thoil:

1. Cliceáil ar dheis ar an táb bileog a theastaíonn uait na cealla a chosaint bunaithe ar dháta, agus ansin roghnaigh Féach an cód ón roghchlár comhthéacs, sa popped amach Microsoft Visual Basic d’Fheidhmchláir fuinneog, cóipeáil agus greamaigh an cód seo a leanas sa Mhodúl bán:

Cód VBA: Cosain na sraitheanna go léir ach amháin as a chéile dáta an lae inniu:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
    If Range("E" & Selection.Row).Value <> Date Then
        ActiveSheet.Protect Password:="111111"
        MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
        ActiveSheet.Unprotect Password:="111111"
        ActiveSheet.EnableSelection = xlNoRestrictions
    End If
End Sub

doc a chosaint faoi dháta 2

nótaí: Sa chód thuas, an litir E is é ceanntásc an cholúin ina bhfuil an dáta suite, “111111"Is é an focal faire chun an bhileog seo a chosaint. Is féidir leat iad a athrú de réir do riachtanas.

2. Ansin sábháil agus dún an fhuinneog cód seo.

(1.) Má chliceálann tú cealla eile níos faide ná a chéile dáta an lae inniu, tiocfaidh bosca pras amach le cur i gcuimhne duit nach féidir an cill a chur in eagar, féach an scáileán:

doc a chosaint faoi dháta 3

(2.) Má chliceálann tú agus má dhéanann tú eagarthóireacht ar an tsraith is ionann agus dáta an lae inniu, athrófar é go rathúil, féach an scáileán:

doc a chosaint faoi dháta 4


mboilgeog cheart gorm saighead Cosain gach sraitheanna a rith an dáta le cód VBA

Más gá duit na sraitheanna go léir a rith an dáta a chosaint, ná lig ach sraitheanna an lae inniu agus na todhchaí a mhodhnú, cuir an cód VBA seo a leanas i bhfeidhm:

1. Cliceáil ar dheis ar an táb bileog a theastaíonn uait na cealla a chosaint bunaithe ar dháta, agus ansin roghnaigh Féach an cód ón roghchlár comhthéacs, sa popped amach Microsoft Visual Basic d’Fheidhmchláir fuinneog, cóipeáil agus greamaigh an cód seo a leanas sa Mhodúl bán:

Cód VBA: Cosain gach dáta atá caite:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20161025
  Dim xRow As Long
  xRow = 2
  ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
  ThisWorkbook.ActiveSheet.Cells.Locked = False
  Do Until IsEmpty(Cells(xRow, 5))
    If Cells(xRow, 5) < Date Then
      Rows(xRow).Locked = True
    End If
    xRow = xRow + 1
  Loop
  ThisWorkbook.ActiveSheet.Protect Password:="111111"
End Sub

doc a chosaint faoi dháta 5

nótaí: Sa chód thuas, an uimhir 5 is í uimhir an cholúin ina bhfuil an dáta suite, “111111"Is é an focal faire chun an bhileog seo a chosaint. Is féidir leat iad a athrú de réir do riachtanas.

3. Ansin sábháil agus dún an fhuinneog cód seo.

(1.) Má chliceálann tú go bhfuil dáta na gcealla caite, tiocfaidh bosca pras amach le cur i gcuimhne duit nach féidir an cill a chur in eagar, féach an scáileán:

doc a chosaint faoi dháta 6

(2.) Má chliceálann tú cill na sraitheanna chun iarracht a dhéanamh na luachanna i dáta an lae inniu nó sa dáta amach anseo a athrú, athrófar é go rathúil, féach an pictiúr:

doc a chosaint faoi dháta 7

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, I would like to protect future days and allow editing current and past days. How do I do that?
This comment was minimized by the moderator on the site
Hello, Miguel,To protect the future days, please apply the below code:<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice
Dim xRow As Long
xRow = 2
ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
ThisWorkbook.ActiveSheet.Cells.Locked = False
Do Until IsEmpty(Cells(xRow, 5))
If Cells(xRow, 5) > Date Then
Rows(xRow).Locked = True
End If
xRow = xRow + 1
Loop
ThisWorkbook.ActiveSheet.Protect Password:="111111"
End SubPlease try, hope it can help you!
This comment was minimized by the moderator on the site
hello i did it but when i press cell message shows that it can not be edited but when i press keyborad i still can write in the cell
This comment was minimized by the moderator on the site
Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

Any help please?

Thank you
This comment was minimized by the moderator on the site
Hello,
To apply this code to columns that you need, please use the following VBA code:(Note: In the code, 5 is the row number contains the date, please change it to your need.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Set xRg = Target.Worksheet.Cells(5, Target.Column)
If xRg <> Date Then
ActiveSheet.Protect Password:="111111"
MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
ElseIf xRg.Value = Date Then
ActiveSheet.Unprotect Password:="111111"
ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi Im facing one problem, once i close the file and reopens it again on the same date, code doesnot work. pls help im stuck
This comment was minimized by the moderator on the site
Hi, rohit,
To keep the code, you should save your workbook as Excel Macro-Enabled Workbook format, please try, thank you!
This comment was minimized by the moderator on the site
Thanks for your help, Thanku so much buddy, It is a great help. and solved my tension.
Be safe
RegardsRohit Khanna
This comment was minimized by the moderator on the site
Thank you! Sorry, I did not get your reply until now, it might be a problem in my email.

It worked! Can't say thank you enough! I really appreciate that!
This comment was minimized by the moderator on the site
Hi,
Glad it can help you!
This comment was minimized by the moderator on the site
Hi,

I am trying to expand this to protect columns that do not include this current month or/and this current week. I tried to replace "Date" with "month" or "week", but did not work.

I have attached a sample for weeks sheet where user cannot edit or type in columns other than this week.

Any advice on that? or shall I use actual dates to reflect a day in the month?
This comment was minimized by the moderator on the site
Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

Any help please?

Thank you
This comment was minimized by the moderator on the site
Hi,

while running this code I am getting an error as shown in the figure
Also i am attaching a snapshot my excel where i want to make changes..

Can u guide in my code where should i make changes according to my excel file so that cells that contain only todays & future date can be editied ???
This comment was minimized by the moderator on the site
Hello, Karan,
Because there are merged cells in your table, the above code can not be applied correctly in merged cells table.
If you have any other good ideas, please comment here. Thank you!
This comment was minimized by the moderator on the site
As someone who is relatively new to VBA, would you mind explaining why xRow = 2 in Line 4?



Thanks
This comment was minimized by the moderator on the site
because in row no. 1 you have header
This comment was minimized by the moderator on the site
This works great. but how can I switch it to unlock a column based on dates in row 3?
This comment was minimized by the moderator on the site
Hello.....


I am using the code for Protect all rows except today’s date row........



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
If Range("A" & Selection.Row).Value <> Date Then
ActiveSheet.Protect Password:="111111"
MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
ElseIf Range("E" & Selection.Row).Value = Date Then
ActiveSheet.Unprotect Password:="111111"
ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub


After i save i and click on cell i get message only today's date row can be edited....but if i double click the cell the i can edit...pls help
This comment was minimized by the moderator on the site
hi is it possible to lock certain cells depending on the date that is directly above them in a column?
This comment was minimized by the moderator on the site
Hi, Lusis,
Can you give your problem more specifically? Or you can attach a screenshot as your problem.
Thank you!
This comment was minimized by the moderator on the site
Hi skyyang

Yes here is a screenshot. Hope this helps.
This comment was minimized by the moderator on the site
Here it is.
This comment was minimized by the moderator on the site
Hello, Lucis,
Do you mean to lock the columns which date is past, and only the columns of today and future days can be modified?
This comment was minimized by the moderator on the site
Correct! How would I go about that?
This comment was minimized by the moderator on the site
Hi, Luis,
How about the date cell in A1? Is it entered manually or entered with a formula =now()? If it is a formula cell, the locked columns will be changed with the date changes.
Looking forward to your reply!
This comment was minimized by the moderator on the site
Hi Skyyangg

yes the cell A1 has the =now() formula.

thanks!
This comment was minimized by the moderator on the site
Hi is it possible to lock columns by the date directly above the columns?
This comment was minimized by the moderator on the site
Private Sub worksheet_Change(ByVal Target As Range)

'Subscribe to http://youtube.com/excel10tutorial

'Support the channel by donating on http://patreon.com/excel10tutorial

Dim col As Range

'Set the correct sheet name here:

With ThisWorkbook.Sheets("AGOSTO-22")

.Unprotect "ABCDE"

For Each col In .UsedRange.Columns

col.EntireColumn.Locked = col.Range("A1").Value <> Date

Next col

.Protect "ABCDE"

.EnableSelection = xlNoRestrictions

End With

End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations