VBA Problem on upgrade to 64-bit system

[Michael Jones]

Following an excel upgrade to excel 2016 640bit I now receive a compile error stating "The code in this project must be updated for use on 64-bit systems..." I am using the following vba which locks cells after an entry is made. I need the code to work on both 32-bit and 64-bit systems but cannot work out where to add the "Ptr Safe" statement (and any other amendments required) to enable this. I would be very grateful if someone could suggest an amendment to my code to enable this vba to run successfully, as I am very much a vba newbie.

Option Explicit
Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Private Declare  CloseClipboard Lib "User64" () As Long
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub ' unprotect only when a single cell is selected
If Sheets("Switch").Range("AutoExpand") Like "Disabled" Then Exit Sub
 
Dim Tbl As ListObject, Off As Integer, ExitCode As Label
Dim TblFirstRow As Long, TblFirstColumn As Integer
Dim FirstRowAllowed As Long
 
On Error GoTo ExitCode
Off = 0: If Target.Row > 1 Then Off = -1
Set Tbl = ActiveSheet.ListObjects(1)
TblFirstRow = Tbl.HeaderRowRange.Row
TblFirstColumn = Tbl.HeaderRowRange.Cells(1, 1).Column
OpenClipboard 0     ' when a macro runs, usually the clipboard is emptied; opening the clipboard will preserve whatever you have in there;
FirstRowAllowed = TblFirstRow  ' the table will be unprotected if the user selects a cell from this row down
 
If Target.Row >= FirstRowAllowed And Target.Row <= Tbl.ListRows.Count + TblFirstRow + 1 And _
    Target.Column <= Tbl.ListColumns.Count + TblFirstColumn And _
    Target.Cells.Offset(Off, 0).Locked = False Then
    Unprotect
    CloseClipboard
Else
    GoTo ExitCode
 End If
Exit Sub
 
ExitCode:
     Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                False, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting:=True, _
                AllowFiltering:=True, AllowUsingPivotTables:=True
     CloseClipboard
 
End Sub

 

[Jaafar Tribak]

Try this :

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

 

[Michael Jones]

Hi Jaafar

Thank you for your swift response

Do I replace the start of my existing code with this, prior to the statement in my existing code which starts "Private Sub Worksheet...?"

 

[Jaafar Tribak]

Hi Jaafar

Thank you for your swift response

Do I replace the start of my existing code with this, prior to the statement in my existing code which starts "Private Sub Worksheet...?"

Yes. At the very top of the worksheet module.

 

[Michael Jones]

Hi Jaafar

Thank you for your much appreciated help

Apologies for a further query. I have the vba set to Worksheet and Change and have input the code as suggested. However, tow lines are turning red, as indicated below. DO you have any suggestion for an amendment?

Option Explicit


#If VBA7 Then
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
#End If


Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A5:A605"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="Register2018"
xRg.Locked = True
Target.Worksheet.Protect Password:="Register2018"
End Sub

 

[RoryA]

It doesn't matter that they are red. The code should still work.

 

[Michael Jones]

Hi Jaafar and RoryA

Thank you both very much for your help. As I said, I am just startin to use VBA. I terefore thuought the colour change was indicating an error. However, I've tested the code, with the amendments recommended by JaaAfar and endorsed by RoryA and it works perfectly.

You have really saved me loads of time. THANK YOU

 

[JackDanIce]

Curious, what does the # mean at the start of the IF statement under Option Explicit before the sub?

 

[Jaafar Tribak]

Hi Jaafar and RoryA

Thank you both very much for your help. As I said, I am just startin to use VBA. I terefore thuought the colour change was indicating an error. However, I've tested the code, with the amendments recommended by JaaAfar and endorsed by RoryA and it works perfectly.

You have really saved me loads of time. THANK YOU

Yes that red colour can be confusing but it won't cause a problem becuse the red section is ignored by the compiler.

Thanks for the feedback and glad we could help.

 

[RoryA]

Curious, what does the # mean at the start of the IF statement under Option Explicit before the sub?

It's conditional compilation. The compiler only uses the code that is in the relevant section based on the VBA7 constant.