Converting Functions from 32 bit to 64 bit

[ACommandLineKindaGuy]

The following code hides the Name Box next to the formula bar and prevents users from accessing a named range and modifying it.

Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function IsWindowEnabled Lib "user32" (ByVal hwnd As Long) As Long
Declare Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long

Sub NameBox(TF)     'True = display it; False = hide it
                    'Called from:
                    '  Workbook_Activate - False
                    '  Workbook_Deactivate - True
                    '  Workbook_BeforeClose - True

Dim hwndXl As Long      'Child window that contains combobox
Dim xlMain As Long      'Xl Window handle
Dim hwndcbo As Long     'Handle of NameBox dropdown
Dim wasenabled As Long  'Status of NameBox
Dim retval As Long      'return value

'Get main window, child, and NameBox handles
xlMain = FindWindowA(strXLClass, vbNullString)
hwndXl = FindWindowEx(xlMain, 0, strXLChildClass, vbNullString)
hwndcbo = FindWindowEx(hwndXl, 0, strDropBtnClass, vbNullString)

'Determine if the NameBox is currently enabled or not.
wasenabled = IsWindowEnabled(hwndcbo)

'If NameBox is hidden then display it
If wasenabled = 0 And TF Then retval = EnableWindow(hwndcbo, 1)

'If NameBox is displayed then hide it
If wasenabled = 1 And Not TF Then retval = EnableWindow(hwndcbo, 0)

End Sub

I would like to convert this so it runs on 64 bit Excel. Please don't suggest workarounds like "Hide the formula bar" or "protect your named ranges"

Can anyone help?

TIA John

 

[DanteAmor]

Try adding "PtrSafe" to each declaration:

Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare PtrSafe Function IsWindowEnabled Lib "user32" (ByVal hwnd As Long) As Long
Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long

 

[ACommandLineKindaGuy]

Thanks! I'll put this on my to-do list since I'll need to test if the user is on a 64bit system.

Meanwhile, I found basically the same solution with the following:

Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
'False hides the names, True displays them
    xName.Visible = False
Next

 

[John_w]

Also change all the function arguments and return values which are window handles from Long to LongPtr:

Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Declare PtrSafe Function IsWindowEnabled Lib "user32" (ByVal hwnd As LongPtr) As Long
Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal fEnable As Long) As Long

Dim hwndXl As LongPtr      'Child window that contains combobox
Dim xlMain As LongPtr      'Xl Window handle
Dim hwndcbo As LongPtr     'Handle of NameBox dropdown
Dim wasenabled As Long     'Status of NameBox
Dim retval As Long         'return value