Data Validation Error Message - Stop

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
When data validation is setup to STOP on an error with a custom message, the dialogue box presents the user with three options: 1) Retry, 2) Cancel and 3) Help.
Is there a way to customize what happens when the user selects HELP? The default calls up help for Data Validation, but I thought that could be useful to give the user some help dialogue specific to what they are needing to enter.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Why not just the information in the error message box, or in the Input message?
 
Upvote 0
Yes, that is what I'm doing now but was just thinking that a user may be inclined to seek further help by clicking the help button. A user using this for data entry has no interest in knowing the intricacies of Excel Data Validation but might find it useful to get help on the task at hand. Excel is pretty powerful, so I figured linking this button was possibly doable.

I suppose I could use one of the other Warning or Information options available to eliminate the Help button, or disable the error reporting and code in a custom form that the user can interact with similar to the error default message but with a button linked to help information.

Thanks Fluff
 
Upvote 0
I very much doubt you could "hijack" that button & even if possible, it would not be simple.
 
Upvote 0
This should be feasible albeit not simple as pointed out by Fluff ... I remember doing something similar in the past. If I find the code I'll post it here.
 
Upvote 0
See if this works for you : (Clicking the DV Help button should display your userform instead of the native DV Help window)

Workbook Sample


RemoteUserFormf5806542b837322a.gif





1- API code in a Standard Module:
VBA Code: 
Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As Long) As LongPtr
    #End If
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hhk As LongPtr) As Long
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hWnd As LongPtr, ByVal MSG As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function SetProp Lib "user32" Alias "SetPropA" (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal hData As LongPtr) As Long
    Private Declare PtrSafe Function GetProp Lib "user32" Alias "GetPropA" (ByVal hWnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare PtrSafe Function GetDlgItem Lib "user32" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long) As LongPtr
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hhk As Long) As Long
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal nCode As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal MSG As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long
    Private Declare Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As Long, ByVal lpString As String) As Long
    Private Declare Function GetDlgItem Lib "user32" (ByVal hDlg As Long, ByVal nIDDlgItem As Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
#End If

  

Public Property Let CustomizeDataValidationHelpButton(ByVal Cell As Range, ByVal Customize As Boolean)

    Const WH_CBT = 5
  
    #If VBA7 Then
        Dim lHook As LongPtr
    #Else
        Dim lHook As Long
    #End If
  
    If Customize Then
        UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
        lHook = SetWindowsHookEx(WH_CBT, AddressOf HookProc, 0, GetCurrentThreadId)
        SetProp Application.hWnd, "Hook", lHook
    Else
        UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
    End If

End Property



#If VBA7 Then
    Private Function HookProc(ByVal idHook As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
#Else
    Private Function HookProc(ByVal idHook As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If

    Const HCBT_ACTIVATE = 5
    Dim sBuff As String * 256, lRet As Long
  
    If IsVBEActive Then
        UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
        Exit Function
    End If
  
    If idHook = HCBT_ACTIVATE Then
        lRet = GetClassName(wParam, sBuff, 256)
        If Left(sBuff, lRet) = "#32770" Then
            If GetDlgItem(wParam, &HFA1) Then
                UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
                Call SubClassPrompt(wParam, True)
            End If
        End If
    End If
  
    HookProc = CallNextHookEx(GetProp(Application.hWnd, "Hook"), idHook, ByVal wParam, ByVal lParam)
  
End Function



#If VBA7 Then
    Private Sub SubClassPrompt(ByVal hWnd As LongPtr, ByVal Subclass As Boolean)
#Else
    Private Sub SubClassPrompt(ByVal hWnd As Long, ByVal Subclass As Boolean)
#End If

    Const GWL_WNDPROC = -4
  
    #If VBA7 Then
        Dim lPrevProc As LongPtr
    #Else
        Dim lPrevProc As Long
    #End If
  
    If Subclass Then
        lPrevProc = SetWindowLong(hWnd, GWL_WNDPROC, AddressOf WindowProc)
        SetProp Application.hWnd, "PrevProc", lPrevProc
    Else
        Call SetWindowLong(hWnd, GWL_WNDPROC, GetProp(Application.hWnd, "PrevProc"))
    End If

End Sub



#If VBA7 Then
    Private Function WindowProc(ByVal hWnd As LongPtr, ByVal MSG As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
#Else
    Private Function WindowProc(ByVal hWnd As Long, ByVal MSG As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If

    Const WM_COMMAND = &H111
    Const WM_DESTROY = &H2
  
    Select Case MSG
  
        Case WM_COMMAND
            #If Win64 Then
                Static Low As LongPtr
                Static High As LongPtr
            #Else
                Static Low As Long
                Static High As Long
            #End If
            GetHiLoword wParam, Low, High
            If Low = &H9 Then
                CustomHelpUserForm.Show
                Exit Function
            End If
          
        Case WM_DESTROY
            If Low = &H7 Then
                CustomizeDataValidationHelpButton(ActiveCell) = True
             Else
                 UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
            End If
            Call SubClassPrompt(hWnd, False)
    End Select
  
    WindowProc = CallWindowProc(GetProp(Application.hWnd, "PrevProc"), hWnd, MSG, wParam, ByVal lParam)

End Function



#If Win64 Then
    Private Sub GetHiLoword(lParam As LongPtr, ByRef loword As LongPtr, ByRef hiword As LongPtr)
#Else
    Private Sub GetHiLoword(lParam As Long, ByRef loword As Long, ByRef hiword As Long)
#End If
        loword = lParam And &HFFFF&
        hiword = lParam \ &H10000 And &HFFFF&
End Sub

Private Function IsVBEActive() As Boolean
    IsVBEActive = CBool(GetActiveWindow = FindWindow("wndclass_desked_gsk", vbNullString))
End Function

Private Sub Auto_Close()
    UnhookWindowsHookEx GetProp(Application.hWnd, "Hook")
End Sub



2- Code Usage In the Worksheet Module :
VBA Code: 
Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count <> 1 Then Exit Sub

    If HasValidation(Target) Then
        CustomizeDataValidationHelpButton(Target) = True
    Else
        CustomizeDataValidationHelpButton(Target) = False
    End If

End Sub


Private Function HasValidation(ByVal Cell As Range) As Boolean
    On Error Resume Next
    HasValidation = Cell.Validation.Type
End Function
 
Upvote 0
Wow Jaafar, that is some pretty crazy code (mind you this is from a someone that still writes with full dependency on "IF/THEN" and "FOR/NEXT" to do 75% of my application lol). It's certainly worth a try, I really wasn't expecting the effort considering it was not affecting how the application ran, and there were alternatives of an easier nature. But if not for me, I'm certain others will find this a valuable resource. Thank you!
 
Upvote 0
Ark68 said:
Wow Jaafar, that is some pretty crazy code (mind you this is from a someone that still writes with full dependency on "IF/THEN" and "FOR/NEXT" to do 75% of my application lol). It's certainly worth a try, I really wasn't expecting the effort considering it was not affecting how the application ran, and there were alternatives of an easier nature. But if not for me, I'm certain others will find this a valuable resource. Thank you!
Click to expand...

There certainly are easier alternatives but it was fun to code and a good learning exercise .

Regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Data Validation - Stop/Cancel options
Replies
2
Views
293
EssKayKay
E
A
  • Question
Worksheet Change Event Triggering With Cell Validation "Cancel"
Replies
1
Views
207
rlv01
R
C
  • Solved
What is the difference between [INPUT MESSAGE] and [ERROR ALERT] in data validation?
Replies
1
Views
86
6StringJazzer
6StringJazzer
K
  • Question
Data validation error message
Replies
7
Views
490
Jaafar Tribak
Jaafar Tribak
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
398
AndyPandy27
A

Forum statistics

Threads
1,214,591
Messages
6,120,429
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top