Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel vba

ResolvedVersion 365

Andrew has attended:
Access Introduction course

Excel VBA

Hi,

I posted the VBA at the bottom of the screen... it works for a while then stops... now it only works on row one and none of the rest..

I followed this website https://www.extendoffice.com/documents/excel/3778-excel-lock-cell-after-data-entry-input.html

regards

Andrew

Dim mRg As Range
Dim mStr As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:I1000"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A1:I1000"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="HICA"
If xRg.Value <> mStr Then xRg.Locked = True
Target.Worksheet.Protect Password:="HICA"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:I1000"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub

RE: Excel VBA

Hi Andrew

I've tried to recreate your scenario so the below is based on best guess.

When the macro is ran the formatting PROTECTION LOCKED is changed for all the cells back to ON.
When resetting the macro and sheet I manually switched the PROTECTION off for the first row only.
I then filled in the first row successfully after which I got the standard error message - locked cells - and then the first row was LOCKED.

So I'm guessing you need to check that you have removed the LOCKED option off all the cells you want to populate before attempting to rerun the macro.

step 1 from the link you sent.

I hope this helps.

Regards

Dennis

Wed 18 Nov 2020: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

The Easiest Way to See a Sum or Average in Excel 2010

Did you know the quickest and most simple way of working out the average or sum of a set of numbers?

Highlight numbers in cells or type some numbers in cells and then look at the status bar at the bottom of the window. Here you will see a display of the average of the numbers, as well as a count of the cells and the sum of these cells.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.18 secs.