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]

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

That's used for conditional compiling which basically instructs the vb compiler to compile or ignore the code between the #If #Else clause .

You may want to take a look here : https://usefulgyaan.wordpress.com/2013/06/26/vba-trick-of-the-week-conditional-compiling/

 

[JackDanIce]

Thank you Rorya, though I'm sorry, I'm still unclear.

"Uses the code in the relevant section" Do you mean it evaluates and complies the TRUE or FALSE part only? How is this different to standard IF statement without # (and usually under Sub)?
Basing this on IFF evaluating both TRUE and FALSE parts regardless

Also, due to requirement of needing to choose 32 bit or 64 bit (VBA7?), it's compiling the necessary part before the "main" code executes? I'm aware of setting Global and Const and other variables outside of Sub but hadn't seen IF used outside - was wondering if the # was needed due to this.

Finally based on the original error message, what in the code needs actual updating or does the error message mean?

Feel free to post a link to read or I can start a new thread, appreciate I'm asking more technical explanations here than thread originally needs (plus OP question is solved)

 

[RoryA]

Have a read of Jan Karel's article here: https://www.jkp-ads.com/Articles/apideclarations.asp

 

[JackDanIce]

Thank you both @Jaafar Tribak and @RoryA