Activecell VBA Code

[Pankaj Kataria]

Hi,

I am running the below code:

While in unprotected mode, the code is working fine. The moment protection is applied, it shows an error on the red highlighted text:

The code is to pick the cell value from one sheet and show the corresponding details on another sheet.

Request help.

Regards,
Pankaj

Code is given below:


Sub ViewRecord()


Dim wk_master As Workbook
Dim ws_master As Worksheet
Dim cell_value As String


Set wk_master = ActiveWorkbook


Set ws_master = wk_master.Worksheets(3)


cell_value = ws_master.Cells(ActiveCell.Row(), "B").Value


Range("P1").Value = cell_value


'MsgBox ("The cell value is " & cell_value)
Worksheets("RecordView").Activate


End Sub

 

[Rick Rothstein]

Hi,

I am running the below code:

While in unprotected mode, the code is working fine. The moment protection is applied, it shows an error on the red highlighted text:

The code is to pick the cell value from one sheet and show the corresponding details on another sheet.

Request help.

Regards,
Pankaj

Code is given below:


Sub ViewRecord()


Dim wk_master As Workbook
Dim ws_master As Worksheet
Dim cell_value As String


Set wk_master = ActiveWorkbook


Set ws_master = wk_master.Worksheets(3)


cell_value = ws_master.Cells(ActiveCell.Row(), "B").Value

ActiveSheet.Unprotect
Range("P1").Value = cell_value
ActiveSheet.Protect

'MsgBox ("The cell value is " & cell_value)
Worksheets("RecordView").Activate


End Sub

You need to unprotect the cell before trying to change it and reapply the protection afterwards. What I post in blue above is the minimum you would need. If you have a password, you would place space and then that password on each of those lines. You might also want to look up the protect method in the help files as it has arguments available that allow you to customize the protection in the same way you can do so manually.

NOTE: I used ActiveSheet because that is the worksheet where your red highlighted line of code will look for cell P1 as you did not qualify the sheet for that cell.