vba intermediate

AI Training: For Busy Decision Makers & Professionals Book now

TrustPilot

starstarstarstarstar Excellent

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

Forum home » Delegate support and help forum » Microsoft Excel Training and help » VBA Intermediate

VBA Intermediate

resolvedResolved · Low Priority · Version 2013

Ajay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

VBA Intermediate

How to lock sheets and allow users to edit ranges and have a dynamic password. How to unlock cells based on cell colours?

RE: VBA Intermediate

Hi Ajay,

Thank you for the forum question and the nice feedback.


The first part of the question.

"How to lock sheets and allow users to edit ranges and have a dynamic password."

The code below will password protect the range D9:K18 with the password typed in B2.





Sub PassWordProtect()


Range("D9:K18").Select
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range( _
"D9:I18"), Password:=Range("B2")
End Sub


"How to unlock cells based on cell colours?"

The code below will unlock all cells with the fill colour RGB(100,100,100)

Sub UnProtectColour()

Dim cl As Range

For Each cl In ActiveSheet.UsedRange

If cl.Interior.Color = RGB(100, 100, 100) Then
cl.Locked = False
End If
Next cl
End Sub


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: VBA Intermediate

Hi Jen,

I am using the code provided by you earlier.

Dim cl As Range

For Each cl In ActiveSheet.UsedRange

If cl.Interior.Color = RGB(100, 100, 100) Then
cl.Locked = False
End If
Next cl
End Sub

The last row in my range is Row 43, however the used range row is 6397



the Range in each of my workbook is different.
How do I change the property Used range to the last cell in the range?

Can I attach a screen snip to explain the problem better.

I have tried using the code to reset the usedrange, but it does not work
Reset UsedRange
Sub UsedRange()
a = ActiveSheet.UsedRange.Rows.Count
End Sub

The value of variable a is still 6397


Can you kindly assist in resetting the used range or provide an alternative solution to the problem?

Regards

Ajay


 

Excel tip:

Missing Field handle

If your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked.

View all Excel hints and tips


Server loaded in 0.12 secs.